Mysql中的一些坑总结

怼烎@ 2022-10-29 01:41 63阅读 0赞

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'