测试脚本
## 删除测试表
DROP TABLE IF EXISTS tb2001;
DROP TABLE IF EXISTS tb2002;
DROP TABLE IF EXISTS tb2003;
## 创建测试表
CREATE TABLE tb2001(
id INT AUTO_INCREMENT PRIMARY KEY,
c1 VARCHAR(100) COLLATE utf8mb4_unicode_ci,
c2 VARCHAR(100) COLLATE utf8mb4_bin
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4 ;
CREATE TABLE tb2002(
id INT AUTO_INCREMENT PRIMARY KEY,
c1 VARCHAR(100) COLLATE utf8mb4_general_ci,
c2 VARCHAR(100) COLLATE utf8mb4_bin
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4;
CREATE TABLE tb2003(
id INT AUTO_INCREMENT PRIMARY KEY,
c1 VARCHAR(100) COLLATE utf8mb4_0900_ai_ci,
c2 VARCHAR(100) COLLATE utf8mb4_bin
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4;
## 插入测试数据
INSERT INTO tb2001(c1,c2)VALUES(0xF09F8D83,0xF09F8D83),(0xF09FA68A,0xF09FA68A),(0xF09F8CA0,0xF09F8CA0);
INSERT INTO tb2002(c1,c2)VALUES(0xF09F8D83,0xF09F8D83),(0xF09FA68A,0xF09FA68A),(0xF09F8CA0,0xF09F8CA0);
INSERT INTO tb2003(c1,c2)VALUES(0xF09F8D83,0xF09F8D83),(0xF09FA68A,0xF09FA68A),(0xF09F8CA0,0xF09F8CA0);
## 等值查询测试
SELECT * FROM tb2001 WHERE c1=0xF09F8D83;
SELECT * FROM tb2002 WHERE c1=0xF09F8D83;
SELECT * FROM tb2003 WHERE c1=0xF09F8D83;
SELECT * FROM tb2001 WHERE c2=0xF09F8D83;
SELECT * FROM tb2002 WHERE c2=0xF09F8D83;
SELECT * FROM tb2003 WHERE c2=0xF09F8D83;
测试结果
mysql> SELECT * FROM tb2001 WHERE c1=0xF09F8D83;
+----+------+------+
| id | c1 | c2 |
+----+------+------+
| 1 | 🍃 | 🍃 |
| 2 | 🦊 | 🦊 |
| 3 | 🌠 | 🌠 |
+----+------+------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM tb2002 WHERE c1=0xF09F8D83;
+----+------+------+
| id | c1 | c2 |
+----+------+------+
| 1 | 🍃 | 🍃 |
| 2 | 🦊 | 🦊 |
| 3 | 🌠 | 🌠 |
+----+------+------+
3 rows in set (0.01 sec)
mysql> SELECT * FROM tb2003 WHERE c1=0xF09F8D83;
+----+------+------+
| id | c1 | c2 |
+----+------+------+
| 1 | 🍃 | 🍃 |
+----+------+------+
1 row in set (0.00 sec)
mysql>
mysql> SELECT * FROM tb2001 WHERE c2=0xF09F8D83;
+----+------+------+
| id | c1 | c2 |
+----+------+------+
| 1 | 🍃 | 🍃 |
+----+------+------+
1 row in set (0.00 sec)
mysql> SELECT * FROM tb2002 WHERE c2=0xF09F8D83;
+----+------+------+
| id | c1 | c2 |
+----+------+------+
| 1 | 🍃 | 🍃 |
+----+------+------+
1 row in set (0.00 sec)
mysql> SELECT * FROM tb2003 WHERE c2=0xF09F8D83;
+----+------+------+
| id | c1 | c2 |
+----+------+------+
| 1 | 🍃 | 🍃 |
+----+------+------+
1 row in set (0.00 sec)
测试总结
- 使用utf8mb4字符集能存储特殊字符如表情符号。
- utf8mb4_unicode_ci和utf8mb4_general_ci排序规则无法"正确匹配"特殊字符。
- utf8mb4_0900_ai_ci排序规则能"正确匹配"特殊字符,但仅适用MySQL 8.0版本。
- utf8mb4_bin排序规则基于二级制方式匹配特殊字符,能"正确匹配"特殊字符,适用于MySQL各个版本。