mysql字符集相关
MySQL - Collation
来源于：https://zhuanlan.zhihu.com/p/103448212
本文对MySQL中的collation进行学习，参考文章Collate， Tutorial，MySQL。
背景：项目中遇到字符串检索时不区分大小写的问题，后来发现是MySQL数据库中的collation设置的问题。
MySQL collation是一系列的rules，用来在特定character set中比较字符。MySQL中支持很多字符集，而每种字符集会存在多种collation，并默认选择一种。字符串比较作用于字符串类型的列，如VARCHAR,CHAR,TEXT. Collation会影响到ORDER BY, Where, distinct, group by, having以及字符串索引。
在MySQL中执行"show character set;"命令可显示其支持的所有字符集及每个字符集使用的默认collation。也可以执行命令"show collation;"来获取所有collation的信息。
MySQL [metastore_apimanager_sophon12]> show character set;
+----------+-----------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
| hp8 | HP West European | hp8_english_ci | 1 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
| swe7 | 7bit Swedish | swe7_swedish_ci | 1 |
| ascii | US ASCII | ascii_general_ci | 1 |
| ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
| sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
| hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |
| tis620 | TIS620 Thai | tis620_thai_ci | 1 |
| euckr | EUC-KR Korean | euckr_korean_ci | 2 |
| koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |
| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
| greek | ISO 8859-7 Greek | greek_general_ci | 1 |
| cp1250 | Windows Central European | cp1250_general_ci | 1 |
| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
| latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |
| armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
| cp866 | DOS Russian | cp866_general_ci | 1 |
| keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |
| macce | Mac Central European | macce_general_ci | 1 |
| macroman | Mac West European | macroman_general_ci | 1 |
| cp852 | DOS Central European | cp852_general_ci | 1 |
| latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 |
| cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |
| utf16 | UTF-16 Unicode | utf16_general_ci | 4 |
| utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 |
| cp1256 | Windows Arabic | cp1256_general_ci | 1 |
| cp1257 | Windows Baltic | cp1257_general_ci | 1 |
| utf32 | UTF-32 Unicode | utf32_general_ci | 4 |
| binary | Binary pseudo charset | binary | 1 |
| geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 |
| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
+----------+-----------------------------+---------------------+--------+collation中一般以"_ci", "_cs","_bin"结尾，其中，ci表示case insensitive， cs表示case sensitive，bin表示binary。项目中使用charset utf8并使用其collation utf8_unicode_ci，所以，字符串比较是大小写不敏感。
Table使用的character set和collation使用命令"show create table TABLE_NAME"如下获取。不同的命令获取table不同的信息。
# show create table TABLE_NAME 获取create table的sql
| cluster | CREATE TABLE `cluster` (
`id` char(36) NOT NULL DEFAULT '',
`name` varchar(200) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `unique_cluster_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin |
# desc TABLE_NAME 获取table中column的信息
desc cluster;
+------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+---------------+------+-----+---------+-------+
| id | char(36) | NO | PRI | | |
| name | varchar(200) | NO | UNI | NULL | |
# show table status like 'cluster'; 查看table底层的信息
MySQL [metastore_apimanager_sophon28]> show table status like 'cluster';
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| cluster | InnoDB | 10 | Compact | 2 | 8192 | 16384 | 0 | 16384 | 0 | NULL | 2019-12-02 01:08:53 | NULL | NULL | utf8_general_ci | NULL | | |
+---------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.00 sec)设置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;-column
可以对CHAR, VARCHAR, TEXT等数据类型的column设置字符集属性。
column_name [CHAR | VARCHAR | TEXT] (length) CHARACTER SET character_set_name COLLATE collation_name
ALTER TABLE TABLENAME MODIFY COLUMN COLUMNNAME VARCHAR(50) BINARY CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL;上面讲述在不同层次设置字符集和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');utf8mb4
mysql中的utf8最多只能支持3bytes长度的字符编码，对于一些需要占据4bytes的文字，mysql的utf8就不支持了，要使用utf8mb4才行。建议使用utf8mb4。
utf8mb4中存在utf8mb4_bin， utf8mb4_unicode_ci， utf8mb4_general_ci，其中bin将字符看做二进制串进行比较，因此，大小写敏感，另外两个大小写不敏感。unicode比general更新， 符合更新的标准。
Liquibase层次设置
由于项目中使用Liquibase来自动创建table，因此需要使用Liquibase xml文件来创建table。
推荐的方式：创建table时设置table的字符集和collation。
<changeSet id="5" author="rain">
<createTable tableName="recall_info">
</createTable>
<modifySql dbms="mysql">
<append value="ENGINE=INNODB DEFAULT CHARSET utf8 COLLATE utf8_bin"/>
</modifySql>
</changeSet>这种方式通过在Liquibase生成的create table statement后append指定的字符串来设置字符集和collation。
注意：在该changeSet中只放置createTable和modifySql，因为modifySql会自动append到changeSet中所有的statement之后。
尝试使用下面的方式修改table层次的字符集和collation，但是由于Liquibase识别不了sql语法而失败。
<changeSet id="8" author="rain">
<sql>alter table service_desc CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;</sql>
</changeSet>
