一.基本信息 | KWAN的解忧杂货铺

# 一.基本信息

# 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

# 4.字符集

MySQL 支持多种字符集（Character Set）用于存储和处理不同语言和字符编码的数据。以下是一些常见的 MySQL 字符集：

utf8mb4：UTF-8 编码，支持存储任意 Unicode 字符，包括表情符号。
utf8：UTF-8 编码，支持存储大部分 Unicode 字符，但不支持存储一些较新的 Unicode 字符。
latin1：Latin-1 字符集，也称为 ISO 8859-1。支持大部分西欧语言的字符。
utf16：UTF-16 编码，支持存储任意 Unicode 字符。
utf32：UTF-32 编码，支持存储任意 Unicode 字符。

这只是一些常见的字符集示例，MySQL 还支持其他字符集。您可以使用以下命令查看 MySQL 服务器支持的所有字符集：

SHOW CHARACTER SET;

此命令将返回 MySQL 支持的所有字符集的列表，包括其名称和默认排序规则。

# 二.设置方式

# 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');

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

# 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');

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

# 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');

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

# 三.字符集

# 1. 理解排序规则

排序规则（Collation）是数据库中用于确定字符数据如何比较和排序的规则。在 MySQL 中，排序规则与字符集（Character Set）紧密相关。字符集定义了数据库可以存储的字符集合，而排序规则则定义了这些字符如何被比较和排序。

# 2.查看字符集

SHOW COLLATION WHERE Charset = 'utf8mb4';

# 3. utf8mb4 字符集

utf8mb4 是一种广泛使用的字符集，它是 utf8 的超集，能够存储任何 Unicode 字符，包括表情符号。utf8mb4 使用四个字节来存储每个字符，这使得它能够覆盖所有的 Unicode 字符，而不仅仅是基本的多语言平面（BMP）。

# 4.utf8mb4_bin

utf8mb4_bin 是一个二进制排序规则，它完全区分大小写，并且可以处理所有 utf8mb4 字符。这种排序规则适用于需要精确二进制比较的场景，例如在某些搜索和比较操作中，需要区分大小写和特殊字符。

ALTER TABLE `test` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

这条语句将 test_table 表的字符集转换为 utf8mb4，并设置排序规则为 utf8mb4_bin。这样，表中的数据将按照二进制方式进行排序，区分大小写和特殊字符。

# 5.utf8mb4_0900_as_cs

utf8mb4_0900_as_cs 是基于 Unicode 9.0 的排序规则，它支持区分大小写和表情符号。这种排序规则可能比 utf8mb4_bin 更复杂，因为它涉及到更多的语言和文化规则。适用于需要遵循特定语言或地区排序习惯的场景。

ALTER TABLE `test_table` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_cs;

这条语句将 test_table 表的字符集转换为 utf8mb4，并设置排序规则为 utf8mb4_0900_as_cs。这样，表中的数据将按照 Unicode 9.0 的规则进行排序，区分大小写和特殊字符。

# 6.分区大小写

# 不区分大小
`tenantId` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,

# 区分大小写
SELECT * FROM table_name WHERE tenantId COLLATE utf8mb4_unicode_cs = 'SomeTenantId';

1
2
3
4
5

ci 后缀表示不区分大小写，case-insensitive
cs 表示区分大小写，case-sensitive

← 03-MySQL语句 05-MySQL变量 →