文章目录
相关概念
几个重要的术语和概念：
编码：某个字符集成员的内部表示
字符集： 字母符号的集合
排序规则：规定字符如何比较和排序的指令(也叫 校验规则 或者 校对规则)
不指定时(默认情况)：库会继承实例、表会继承库、字段会继承表的字符集和排序规则
查看字符集和排序规则
查看当前实例支持的字符集
同时显示每个字符集使用的默认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%';
utf8mb4
mysql中的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语句常用用法汇总
扫一扫
9971
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
