| MySQL字符集、排序规则和大小写敏感相关问题总结 | 您所在的位置：网站首页 › 编码规则设置错误 › MySQL字符集、排序规则和大小写敏感相关问题总结 |
|
文章目录
相关概念查看字符集和排序规则查看当前实例支持的字符集utf8mb4字符串长度计算注意
查看当前实例支持的排序规则查看当前实例下的某个字符集支持的所有排序规则查看当前实例使用的字符集和排序规则查看数据库编码(包括字符集和排序规则)查看表编码(包括字符集和排序规则)查看表中所有字段的编码(包括排序规则)
设置字符集和排序规则设置serverdatabasetablecolumn
查询时显式指定collation大小写敏感问题lower_case_file_systemlower_case_table_names
参考
相关概念
几个重要的术语和概念： 编码：某个字符集成员的内部表示 字符集： 字母符号的集合 排序规则：规定字符如何比较和排序的指令(也叫 校验规则 或者 校对规则) 不指定时(默认情况)：库会继承实例、表会继承库、字段会继承表的字符集和排序规则 查看字符集和排序规则 查看当前实例支持的字符集同时显示每个字符集使用的默认collation show charset; 或者 show character set; 或者 ## 在MySQL中，全部的字符集与排列字符集的信息都存放在information_schema库中，所以可以通过下面的方式 select * from INFORMATION_SCHEMA.CHARACTER_SETS;可以过滤： show charset like '%utf8%'; show charset where charset like '%utf8%'; show character set like '%utf8%'; utf8mb4mysql中的utf8最多只能支持3bytes长度的字符编码，对于一些需要占据4bytes的文字，mysql的utf8就不支持了，要使用utf8mb4才行。建议使用utf8mb4。 utf8mb4中存在utf8mb4_bin， utf8mb4_unicode_ci， utf8mb4_general_ci，其中bin将字符看做二进制串进行比较，因此，大小写敏感，另外两个大小写不敏感。unicode比general更新， 符合更新的标准。 字符串长度计算注意由于多字节编码的缘故，字符个数不一定等于字节数，所以在查询时，注意区分，length是字节数，char_length是字符数 select length('你'); /*结果是3，3个字节，数据库采用的utf8编码*/ select char_length('你'); /*结果是1，1个字符*/ 查看当前实例支持的排序规则 show collation; 或者 select * from INFORMATION_SCHEMA.COLLATIONS;collation中一般以"_ci"，“_cs”，"_bin"结尾，其中，ci表示case insensitive， cs表示case sensitive，bin表示binary(将字符看做二进制串进行比较，因此，大小写敏感)。 例如，项目中使用charset utf8并使用其collation utf8_unicode_ci，所以，字符串比较是大小写不敏感。 可以过滤： SHOW COLLATION WHERE Charset = 'utf8'; 查看当前实例下的某个字符集支持的所有排序规则 show collation where charset = 'utf8' 查看当前实例使用的字符集和排序规则备注：创建库不指定时，使用此默认值 SHOW VARIABLES LIKE 'character%'; SHOW VARIABLES LIKE 'collation%';注意，这个参数 collation_server很重要：该参数定义服务器使用的字符集和排序规则。 对应在MySQL配置文件中找到或添加以下行，例如： [mysqld] collation_server=utf8_general_ci 查看数据库编码(包括字符集和排序规则) USE database_name; SHOW CREATE DATABASE db_name; 或者 SHOW VARIABLES LIKE 'character_set_database'; SHOW VARIABLES LIKE 'collation_database'; 查看表编码(包括字符集和排序规则) SHOW CREATE TABLE tbl_name; 查看表中所有字段的编码(包括排序规则)在指定的库中，使用SHOW FULL COLUMNS FROM table_name语句即可查询表中所有列的信息。 USE database_name; SHOW FULL COLUMNS FROM table_name;指定列 为了查询指定列的信息，可以在上述语句中添加限制Field字段的条件。 SHOW FULL COLUMNS FROM table_name WHERE Field = column_name; 设置字符集和排序规则 设置设置character set和collation： MySQL允许在server, database, table, column等4个级别来设置字符集和collation。越小层次设置的字符集属性，优先级越大，即column级别的字符集属性会覆盖其他层次设置的字符集属性。 server < database < table < column server字符集默认使用utf8mb4，若设定字符集但不设定collation，使用该字符集的默认collation。 下面命令在server层次设置字符集为utf8，collation为utf8_unicode_ci。这样，server下的所有database便使用server设置的字符集信息。 mysqld --character-set-server=utf8 --collation-server=utf8_unicode_ci database下面SQL在DB层次设置字符集和collation。这样，DB中所有创建的table会使用db设置的字符集属性。 CREATE DATABASE database_name CHARACTER SET utf8 COLLATE utf8_bin; ALTER DATABASE database_name CHARACTER SET utf8 COLLATE utf8_bin;注意：新设置的字符集属性不影响已经创建的table，只会对新创建未指定字符集的表赋予默认字符集 table CREATE TABLE TABLE_NAME ( ... ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin ALTER TABLE TABLE_NAME CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin; ALTER TABLE table_name CHARACTER SET charset_name COLLATE collation_name; column可以对CHAR, VARCHAR, TEXT等数据类型的column设置字符集属性。 格式如下： column_name [CHAR | VARCHAR | TEXT] (length) CHARACTER SET character_set_name COLLATE collation_name -- 案例： ALTER TABLE table_name MODIFY column_name column_type CHARACTER SET charset_name COLLATE collation_name; 查询时显式指定collation上面讲述在不同层次设置字符集和collation。我们可以在查询数据时显式指定collation来覆盖表列上指定的collation，或者使用binary。 格式如下： SELECT DISTINCT field1 COLLATE utf8mb4_general_ci FROM table1; SELECT field1, field2 FROM table1 ORDER BY field1 COLLATE utf8mb4_unicode_ci; -- 在每一个条件前加上binary关键字 select * from user where binary username = 'admin' and binary password = 'admin'; -- 将参数以binary('')包围 select * from user where username like binary('admin') and password like binary('admin'); 大小写敏感问题查看当前mysql的大小写敏感配置 show global variables like '%case%'; lower_case_file_system该参数是用于描述data所在的操作系统的文件目录是否区分大小写，该参数为bool类型，只读参数，无法修改。 此变量是只读的，因为它反映了文件系统的属性，设置它对文件系统没有影响。 0 – 大小写敏感，OFF 1 – 大小写不敏感，ON 比如跑在linux上的都是OFF或者0。 lower_case_table_names表示表名是否大小写敏感，可以修改，该参数为静态，可设置为0、1、2。 0 – 大小写敏感。（Unix，Linux默认） 创建的库表将原样保存在磁盘上。如create database TeSt;将会创建一个TeSt的目录，create table AbCCC …将会原样生成AbCCC.frm。 SQL语句也会原样解析。 1 – 大小写不敏感。（Windows默认） 创建的库表时，MySQL将所有的库表名转换成小写存储在磁盘上。 SQL语句同样会将库表名转换成小写。 如需要查询以前创建的Test_table（生成Test_table.frm文件），即便执行select * from Test_table，也会被转换成select * from test_table，致使报错表不存在。 2 – 大小写不敏感（OS X默认） 创建的库表将原样保存在磁盘上。 但SQL语句将库表名转换成小写。 修改lower_case_table_names导致的常见不良隐患： 如果在lower_case_table_names=0时，创建了含有大写字母的库表，改为lower_case_table_names=1后，则会无法被查到。 注意事项： 将默认的lower_case_tables_name为0设置成1，需先将已经存在的库表名转换为小写： 1）针对仅表名存在大写字母的情况： ①、lower_case_tables_name=0时，执行rename table成小写。 ②、设置lower_case_tables_name=1，重启生效。 2）针对库名存在大写字母的情况： ①、lower_case_tables_name=0时，使用mysqldump导出，并删除老的数据库。 ②、设置lower_case_tables_name=1，重启生效。 ③、导入数据至实例，此时包含大写字母的库名已转换为小写。 转换操作需要自行测试，不同操作系统，不同MySQL版本可能有不同的情况。 参考MySQL官网 server-system-variables MySQL数据库配置信息查看和修改 MySQL show语句常用用法汇总 |
| CopyRight 2018-2019 实验室设备网 版权所有 |