charset和COLLATE

在mysql8.0以下版本中，默认的CHARSET是Latin1，默认的COLLATE是latin1_swedish_ci。
从mysql8.0开始，默认的CHARSET改为了utf8mb4，默认的COLLATE改为了utf8mb4_0900_ai_ci。

一、CHARSET是什么

字符集参数
utf8mb3 ：又称为utf8,非标准的 utf8 字符集，只使用1～3个字节表示字符。
utf8mb4 ：标准的 utf8 字符集，使用1～4个字节表示字符。

对于一些需要占据4bytes的文字，mysql的utf8就不支持了，要使用utf8mb4才行。如：特殊中文、特殊表情包

mysql中有utf8和utf8mb4两种编码，在mysql中请大家忘记utf8，永远使用utf8mb4。

二、COLLATE是什么

CREATE TABLE `table0` (
    `id` bigint(10) unsigned NOT NULL AUTO_INCREMENT,
    `field1` text COLLATE utf8_unicode_ci NOT NULL COMMENT '字段1',
    `field2` varchar(128) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '字段2',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8_unicode_ci;

collate:mysql的排序规则
但凡是mysql中涉及到字符类型(VARCHAR，CHAR，TEXT)的排序、比较(ORDER BY语句的顺序,WHERE条件中大于小于号筛选出来的结果)，都和collate有关。如果索引是字符类型，也会影响索引创建

COLLATE区别

后缀_ci（Case Insensitive），即大小写无关，也就是说” B”和”b”在排序和比较的时候是一视同仁的。selection * from table1 where fieldn=”b”同样可以把fieldn为”B”的值选出来。
后缀_cs的COLLATE（Case Sensitive），即大小写敏感的。

三、查看字符集编码和排序规则：

//查看mysql所有支持的字符集
SHOW CHARSET;
//查看mysql所有支持的COLLATE
show collation;
// 查看当前数据库的比较规则
SHOW VARIABLES LIKE 'collation_database';

// 查询当前服务器和数据库的字符集
SHOW VARIABLES LIKE 'character_set_database';

select SCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME,DEFAULT_COLLATION_NAME 
from information_schema.SCHEMATA s 
where SCHEMA_NAME ='数据库名';

//查询当前表的字符集
select TABLE_NAME,TABLE_COLLATION 
from information_schema.TABLES t 
where TABLE_SCHEMA ='数据库名' and TABLE_NAME ='表名';

//查询表中所有列的字符集和排序规则
select c.TABLE_NAME,c.COLUMN_NAME,c.CHARACTER_SET_NAME,c.COLLATION_NAME 
from information_schema.`COLUMNS` c 
where c.TABLE_SCHEMA ='数据库名' and c.TABLE_NAME ='表名'

四、 字符集和比较规则的应用

4.1 MySQL 字符集和比较规则
MySQL 有4个级别的字符集和比较规则，分别是：

服务器级别
数据库级别
表级别
列级别
我们接下来仔细看一下怎么设置和查看这几个级别的字符集和比较规则。

服务器级别
MySQL 提供了两个系统变量来表示服务器级别的字符集和比较规则：
服务器级别字符集 character_set_server
服务器级别比较规则 collation_server

我们看一下这两个系统变量的值：

@localhost:[(none)]>SHOW VARIABLES LIKE 'character_set_server';
+----------------------+--------+
| Variable_name        | Value  |
+----------------------+--------+
| character_set_server | latin1 |
+----------------------+--------+
1 row in set (0.00 sec)
@localhost:[(none)]>SHOW VARIABLES LIKE 'collation_server';
+------------------+-------------------+
| Variable_name    | Value             |
+------------------+-------------------+
| collation_server | latin1_swedish_ci |
+------------------+-------------------+
1 row in set (0.00 sec)

可以看到在我的计算机中服务器级别默认的字符集是latin1 ，默认的比较规则是 latin1_swedish_ci 。

我们可以在启动服务器程序时通过启动选项或者在服务器程序运行过程中使用 SET 语句修改这两个变量的值。比如我们可以在配置文件中这样写：

[server]
character_set_server=gbk
collation_server=gbk_chinese_ci

当服务器启动的时候读取这个配置文件后这两个系统变量的值便修改了。

数据库级别

-- 创建数据库时指定字符集编码和排序规则
CREATE DATABASE  数据库名
 	CHARACTER SET utf8mb4
 	COLLATE utf8mb4_general_ci;
-- 数据库级别修改
ALTER DATABASE 数据库名 CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci

表级别
我们也可以在创建和修改表的时候指定表的字符集和比较规则，语法如下：

-- 创建表时指定字符集编码和排序规则
create table tt2(
	addr varchar(50)
) character set utf8mb4 collate utf8mb4_general_ci;
-- 表级别修改
ALTER TABLE 表名CONVERT TO CHARACTER SET utf8mb4 collate utf8mb4_general_ci;

列级别
需要注意的是，对于存储字符串的列，同一个表中的不同的列也可以有不同的字符集和比较规则。我们在创建和修改列定义的时候可以指定该列的字符集和比较规则，语法如下：

-- 创建表时指定某个列的字符集编码和排序规则
create table tt4(
	name varchar(50) character set utf16 collate utf16_general_ci,
	addr varchar(50)
)character set utf8mb4 collate utf8mb4_general_ci;


-- 列级别修改
ALTER TABLE 表名MODIFY `colname` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

在转换列的字符集时需要注意，如果转换前列中存储的数据不能用转换后的字符集进行表示会发生错误。比方说原先列使用的字符集是utf8，列中存储了一些汉字，现在把列的字符集转换为ascii的话就会出错，因为ascii字符集并不能表示汉字字符。