最近在用开源的MySQL 8.0开发本公司的产品，在客户现场建表时默认使用的是CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 字符集导致与oracle的结果不一致，最后将建表时的字符集改为utf8mb3就可以了。
正常建表如下，默认使用的是CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 字符集
[test]> CREATE TABLE t3(id int primary key, name varchar(50))engine = xxx;
Query OK, 0 rows affected (0.07 sec)
[test]> insert into t3 values(1, '123AA123'), (2, '123aa123');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
[test]> show create table t3;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t3 | CREATE TABLE "t3" (
"id" int NOT NULL,
"name" varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
PRIMARY KEY ("id")
) ENGINE=xxx DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
[test]> select * from t3 where name = "123AA123";
+----+----------+
| id | name |
+----+----------+
| 1 | 123AA123 |
| 2 | 123aa123 |
+----+----------+
2 rows in set (0.00 sec)
上面的结果导致查询有2条记录，也就是大小写不敏感，跟oracle结果不一致，oracle就一条结果。
后面经过排查要与oracle结果一致，将建表时的字符集改为utf8mb3就可以了。因为字符集utf8mb3对大小写是敏感的。
[test]> CREATE TABLE t4(id int primary key, name varchar(50))engine = xxx collate utf8_bin;
Query OK, 0 rows affected, 1 warning (0.08 sec)
[test]> show warnings;
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Error | 1064 | You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'warning' at line 1 |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
[test]> select * from t4 where name = '123aa123';
+----+----------+
| id | name |
+----+----------+
| 2 | 123aa123 |
+----+----------+
1 row in set (0.01 sec)
[test]> select * from t4 where name = '123AA123';
+----+----------+
| id | name |
+----+----------+
| 1 | 123AA123 |
+----+----------+
1 row in set (0.00 sec)
但是这样导致建表时会有警告，字符集这块相当于从utf8mb4 退化到了 utf8mb3了。
还有一种方法就是建表时，默认使用utf8mb4字符集，但是建表之后立即执行：
alter table test3 modify name varchar(50) collate utf8_bin;将name列的字符集改为utf8_bin。
总结：建表时指定的字符集导致大小写不敏感导致的问题。
