MySQL字段值大小写敏感的解决方案

最近在用开源的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。

总结：建表时指定的字符集导致大小写不敏感导致的问题。