MySQL字符集带来的问题

    下面说的 这些问题都是随意导致的。真的不是所有开发都可以写SQL，不是所有开发都能建表。这给后续的开发和运维工作带来很大的问题。

     上周意外发现一个SQL，大致 SELECT  *   FROM a   LEFT JOIN b  ON s.xx = d.xx  COLLATE utf8mb4_general_ci WHERE   ..........     .   对于这个关联字段后面带着COLLATE utf8mb4_general_ci的写法有点好奇，为什么要这样呢？于是去掉这个再次执行这个SQL。得到了

ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '=' 这样的报错。看来开发人员应该是最早发现这样写不行，所以去用上述写法来解决报错问题。

    通过show create table去掉了无关信息。看到了这两个表的两个字段

......
`xx` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL 
) ENGINE=InnoDB   .....
1 row in set (0.04 sec)

....
`xx` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL

) ENGINE=MyISAM .....

    可以看到两个表在这个字段上的字符集有一点差别，另外有差别的还有存储引擎都不是同一个。这种问题通常在于开发人员建表和导出建表语句都是用工具的。所以经常会有同一个系统中的不同的表会有不同的字符集，甚至一个表上不同的字段也有不同字符集(因为是多人协作的)。

utf8mb4_0900_ai_ci  和utf8mb4_general_ci 这两种字符集区别在于带前者是带重音（一般不需要设置这种），后者是对大小写不敏感，如果需要大小写敏感。那么需要设置为utf8mb4_bin .下面用一个实例来说明。

mysql> show create table testA\G;
*************************** 1. row ***************************
Table: testA
Create Table: CREATE TABLE `testA` (
`id` int DEFAULT NULL,
`a` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
`b` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

a字段对大小写不敏感  a A都可以查得出来

b字段对大小写敏感   只能查出大写或者小写

mysql> insert into testA values (1,'abc','abc');
Query OK, 1 row affected (0.01 sec)

mysql> insert into testA values (1,'Abc','Abc');
Query OK, 1 row affected (0.00 sec)

mysql> insert into testA values (1,'ABC','ABC');
Query OK, 1 row affected (0.00 sec)

mysql> select * from testA;
+------+------+------+
| id | a | b |
+------+------+------+
| 1 | abc | abc |
| 1 | Abc | Abc |
| 1 | ABC | ABC |
+------+------+------+
3 rows in set (0.00 sec)

mysql> select * from testA where a='abc';
+------+------+------+
| id | a | b |
+------+------+------+
| 1 | abc | abc |
| 1 | Abc | Abc |
| 1 | ABC | ABC |
+------+------+------+
3 rows in set (0.00 sec)

mysql> select * from testA where b='abc';
+------+------+------+
| id | a | b |
+------+------+------+
| 1 | abc | abc |
+------+------+------+
1 row in set (0.00 sec)
 

   以上现象可以结合实际调整，很多人觉得自由度大可以随便调整，其实我个人觉得自由度太大也有很多不好的地方，这些设置规定好不要太过自由的发挥。太过自由导致后续管理成本太高。