5.7升级到8.0后问题小记
原创
从5.7升级到8.0.18后，碰到下面两个问题：
- 时间类型的空值比较不再可行
- 升级后修改字符集，导致查询结果有差异
case 1： 时间类型的空值比较不再可行
5.7中，下面的SQL请求不会直接报错返回，而只是提示warning信息：
但到了8.0，这样的查询，会直接报错，且没有workaround：
对这个变化，社区也是吵的很厉害，有认为合理的：
When comparing DATE values with constant strings, MySQL first tries to convert the string to a DATE and then to perform the comparison. When the conversion failed, MySQL executed the comparison treating the DATE as a string. Now in such cases, if the conversion of the string to a DATE fails, the comparison fails with ER_WRONG_VALUE.
也有认为不合理的：
MySQL in data conversion always was "warnings", please reconsider removing this strange DATE conversion error. Or at least move it to configurable sql_mode.
具体见：Unexpected behaviour for date comparison with constant strings
至目前最新的8.0.27，这个「问题」依然存在。
case 2: 升级后修改字符集，导致查询结果有差异
原5.7的字符集是utf8，升级后，8.0使用utf8mb4，但有部分表创建有误，仍是utf8，因此进行了字符集的修改操作：
ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8mb4;但该操作完成后，发现有些查询操作有差异:
-- 修改字符集前
MySQL [test]> select * from tbl_name where name ='abc';
+----+------+
| id | name |
+----+------+
| 1 | abc |
+----+------+
1 row in set (0.004 sec)
-- 修改字符集后
MySQL [test]> select * from tbl_name where name ='abc';
+----+------+
| id | name |
+----+------+
| 1 | abc |
| 2 | Abc |
| 3 | ABC |
+----+------+
3 rows in set (0.026 sec)也即，修复字符集前，字符的比较是大小写不敏感，但修改后，变成了大小写敏感。
原因是 utf8 默认的字符集序是 utf8_bin，使用 alter table convert to 的操作进行字符集修改，但没有指定字符序，会导致使用该字符集集默认字符序，在这个案例里，utf8mb4的默认字符序是 utf8mb4_general_ci，这也就为什么修改字符集后，字符的比较大小写不敏感了。
可以通过如下方式进行修复：
alter table tbl_name convert to character set utf8mb4 collate utf8mb4_bin;问题复现方式如下：
MySQL [test]> create table utf8mb_collate(id int primary key, name varchar(10)) COLLATE=utf8mb4_general_ci;
Query OK, 0 rows affected (0.030 sec)
MySQL [test]>
MySQL [test]> insert into utf8mb_collate values(1, 'abc');
Query OK, 1 row affected (0.002 sec)
MySQL [test]> insert into utf8mb_collate values(2, 'Abc');
Query OK, 1 row affected (0.001 sec)
MySQL [test]> insert into utf8mb_collate values(3, 'ABC');
Query OK, 1 row affected (0.001 sec)
MySQL [test]> select * from utf8mb_collate where name ='abc';
+----+------+
| id | name |
+----+------+
| 1 | abc |
| 2 | Abc |
| 3 | ABC |
+----+------+
3 rows in set (0.026 sec)
MySQL [test]> alter table utf8mb_collate COLLATE=utf8mb4_general_ci;
Query OK, 0 rows affected (0.013 sec)
Records: 0 Duplicates: 0 Warnings: 0
MySQL [test]> select * from utf8mb_collate where name ='abc';
+----+------+
| id | name |
+----+------+
| 1 | abc |
| 2 | Abc |
| 3 | ABC |
+----+------+
3 rows in set (0.001 sec)
MySQL [test]> alter table utf8mb_collate convert to character set utf8mb4 collate utf8mb4_bin;
Query OK, 0 rows affected (0.023 sec)
Records: 0 Duplicates: 0 Warnings: 0
MySQL [test]> select * from utf8mb_collate where name ='abc';
+----+------+
| id | name |
+----+------+
| 1 | abc |
+----+------+
1 row in set (0.001 sec)
MySQL [test]> 原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
腾讯云开发者
