Mysql中的一些坑总结

1. 自增id在服务器重启后有可能会出现的问题

假设我们在数据库中使用自增id连续插入两条数据，生成的id分别是1和2，这个时候我把id为2的删除掉，这个时候我再插入一条数据，生成的id为3

同样的场景，插入了两条数据，生成ID为1和2， 如果此时把id为2的删掉，这个时候我先重启mysql，在插入一条数据，生成的id为2，如果使用了历史表，如果使用id处理逻辑，可能会导致错误

因为InnoDB的自增值是记录在内存的，不是记录在数据文件的。重启后，会把 当前最大ID + 1 作为起始值。 mysql8 修复了此问题

2. 关于group by报错的坑

报错： Error : Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'xxx' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

原因： mysql 5.7是默认开启only_full_group_by模式的，在这种模式下，SELECT语句后的列，要么是GROUP BY语句后面出现的列，要么是使用了聚合函数。

解决：规范sql书写，或者关闭only_full_group_by。 关闭的话，修改mysql配置文件my.cnf，在sql_mode中去掉改选项后重启

ps: 查询sql_mode: SELECT @@sql_mode;

3. mysql tinyint使用boolean类型查询时，无法命中索引，导致全表扫描

MySQL中，Boolean只是 tinyint(1) 的别名，也就是说，MySQL中并没有真正的bool类型。而SQLAlchemy生成SQL的时候并没有检测到 这一点，这就导致一个问题，当使用 bool 类型作为查询条件时，用不上索引，从而导致扫表的行为

SELECT COUNT(*) FROM message WHERE message.is_national = 1 AND message.updated_at > '2020-01-01 00:00:00' AND message.deleted_at IS NULL; --- 可命中

SELECT COUNT(*) FROM message WHERE message.is_national is true AND message.updated_at > '2020-01-01 00:00:00' AND message.deleted_at IS NULL; --- 不可命中

4. 自增值用完后怎么办

无符号 int 的最大值为 4294967295，自增值达到此值后，就不变了，新插入记录时就会报错 Duplicate entry '4294967295' for key 'PRIMARY'

5. mysql中的编码的坑

mysql中的utf8编码不是标准的utf8编码，有时候可能会出现 Incorrect string value: ‘ð <…’ for column ‘summary’ at row 1 的问题。 mysql并没有从根本上解决了这个问题， 只是后来推出了一个 utf8mb64的编码，相当于标准utf8,并且可以存储表情类数据，推荐使用utf8mb64编码类型

6. mysql5.7 timestamp默认值‘0000-00-00 00:00:00’报错

这个问题和sql_mode有关 sql_mode='STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' #sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'