【MySQL系列】字符集设置

一.基本信息

1.问题背景

(pymysql.err.DataError) (1366, ‘Incorrect string value: ‘\xF0\x9F\x98\x9D",…’ for column ‘html’ at row 1’)”

字段不支持表情

2.原因分析

报错 1366，主要是处理 emoji 时，编码问题造成的，在 mysql8 里面，字符集默认就是 utf8mb4，已经支持 emoji，连接 mysql 的时候使用的是 utf8，utf8mb4 时 utf8 的超集，mb4 是 most bytes 4 的意思，专门用来兼容四字节的 unicode，处理 emoji 时需要字符集支持 unicode，utf8mb4 是没问题的，但是 uft8 不可以。

3.连接设置

在连接时设置charset=utf8mb4

mysql+pymysql://root:123456@localhost:3306/test?charset=utf8mb4

二.设置方式

1.默认设置,支持表情,不区分大小写

ALTER DATABASE test_cloud CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 查询出的ALTER TABLE语句全部需要执行
SELECT CONCAT('ALTER TABLE `', TABLE_NAME, '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;')
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'test_cloud';


-- 查询出的ALTER TABLE语句全部需要执行
SELECT
    CONCAT('ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` MODIFY COLUMN `', COLUMN_NAME, '` ', COLUMN_TYPE,
           ' CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;') AS alter_statement
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE
    TABLE_SCHEMA = 'test_cloud' AND
    (DATA_TYPE = 'varchar' OR DATA_TYPE = 'text' OR DATA_TYPE = 'char' OR DATA_TYPE = 'tinytext' OR DATA_TYPE = 'mediumtext' OR DATA_TYPE = 'longtext');

2.区分大小写且不支持表情

-- 需要执行查询出来的语句
ALTER DATABASE test_cloud CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

-- 需要执行查询出来的语句
SELECT CONCAT('ALTER TABLE `', TABLE_NAME, '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;')
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'test_cloud';

-- 需要执行查询出来的语句
SELECT
    CONCAT('ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` MODIFY COLUMN `', COLUMN_NAME, '` ', COLUMN_TYPE,
          ' CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;') AS alter_statement
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE
    TABLE_SCHEMA = 'test_cloud' AND
    (DATA_TYPE = 'varchar' OR DATA_TYPE = 'text' OR DATA_TYPE = 'char' OR DATA_TYPE = 'tinytext' OR DATA_TYPE = 'mediumtext' OR DATA_TYPE = 'longtext');

3.支持表情且区分大小写

-- 需要执行查询出来的语句
ALTER DATABASE test_cloud CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_cs;

-- 需要执行查询出来的语句
SELECT CONCAT('ALTER TABLE `', TABLE_NAME, '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_cs;')
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'test_cloud';

-- 需要执行查询出来的语句
SELECT
    CONCAT('ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` MODIFY COLUMN `', COLUMN_NAME, '` ', COLUMN_TYPE,
          ' CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_cs;') AS alter_statement
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE
    TABLE_SCHEMA = 'test_cloud' AND
    (DATA_TYPE = 'varchar' OR DATA_TYPE = 'text' OR DATA_TYPE = 'char' OR DATA_TYPE = 'tinytext' OR DATA_TYPE = 'mediumtext' OR DATA_TYPE = 'longtext');