mysql数据库避坑笔记

        我们在开发过程中，总是会遇到各种各样的坑，开发的过程就是一边挖坑一边填坑的过程，下面总结了笔者在mysql数据库使用过程中曾踩过的一些坑，希望能给大家提供些经验参考，避免踩坑。

1、字段的隐式转换，除了会造成索引失效外，还会造成不可预料的结果，如：select * from tbl_order where order_no= 170325171202362928；当order_no为varchar类型时，会导致order_no为 170325171202362928 和 170325171202362930 的记录都出现在结果中。

2、数据库字符串比较问题：oracle不要把空字符和空字符串和字符串做比较，空字符串oracle也会当空字符处理，比如null<>'123'、''<>'123'、null='123'、''='123'、null=null、''=''返回的都是null，但mysql空字符串可以和字符串做比较，如‘’<>'123'返回的是true。比较运算符会自动过滤掉为null的列，select * from test where a=null 是查不到记录的，要用is null。所以在实际开发过程中尽量保证数据库字段要有默认值，在做字符串字段的比较时，先进行空值处理，oracle用nvl()，mysql用ifnull()转换成数字再做比较。

3、mysql的utf8编码只支持最长3个字节的字符（一个中文占3个字节），Emoji表情占4个字节，因此保存Emoji表情时会报错，需要采用utf8mb4编码代替，utf8mb4是utf8的超集，占用4个字节。在实际项目中，建议直接采用utf8mb4编码。

4、在建库建表时尽量显式指出使用的字符集，在数据库间迁移表时要确保两个库的表和字段的字符集和排序规则一致，否则会导致表关联查询报错。

只修改表的默认字符集：

ALTER TABLE tbl_name DEFAULT CHARACTER SET character_name [COLLATE...];

修改字段的字符集：

ALTER TABLE tbl_name CHANGE c_name c_name CHARACTER SET character_name [COLLATE ...];

修改表和所有字符列的字符集：

ALTER TABLE tbl_name CONVERT TO CHARACTER SET character_name [COLLATE ...];

5、当字段使用float 、real和double这类浮点型数据类型时，使用round()函数不一定能得到期望的结果，需要加一个足够小的数字来参与运算，如round(i+0.000000009)，建议使用 numeric或decimal替代。

6、mysql的int类型在定义为无符号类型时可以节省一半的存储空间，作为标识字段使用时没问题，但如果涉及字段相减运算建议定义为有符号类型，否则可能会出错，如表test有两个无符号整型字段a和b，当存在a小于b的记录时，select a-b from test 会报错：[Err] 1690 - BIGINT UNSIGNED value is out of range in '(`test_db`.`test`.`a` - `test_db`.`test`.`b`)'。

7、当id定义为自增主键时，就不要程序指定id的值，由数据库自行生成，插入数据时只要不插入id字段即可，如果指定了id值，当插入null或0时，永远不会报错，数据库会自动生成自增id替代null和0。

8、在多个表使用子查询进行联合查询操作的时候，尽量在每个字段前加上表的别名，以免造成不可挽回的后果。例如，表testa和testb分别有以下数据：

当我们想删除testa表中a1字段在testb表a2字段已存在的记录，我们可能会这样写：delete from testa where a1 in (select a2 from testb) ，但假如我们不小心把子查询 select a2 from testb 写成 select a1 from testb 时，语句是不会报错的，执行时会把testa表的所有记录删除。所以，在平时就要养成在字段前加别名的习惯。

9、group by的使用要小心。从oracle转到mysql的人基本上都遇到过这个坑，oracle和mysql的group by是有很大区别的，oracle要求：select子句后的任一非聚合函数字段都应来源于group by分组语句，比如 select a,b,c,max(d) from test，group by语句至少要包含a,b,c三个字段，少了一个都执行不了，但mysql的group by可以使用表中的任何字段，不管这些字段在select子句中是否存在，因此 select a,max(d) from test group by b 在mysql中都是可执行的。

10、mysql的字符串截取函数left、right、substr对中文字符的处理都是一个中文算一个字符长度，left('测试test',2)的结果是：测试，但length函数是一个中文算三个字符，length('测试test')的结果是10，要与left、right、substr配合使用应用char_length函数。

11、left join过滤条件在on是先对表过滤再join，过滤条件在where是对join后的结果过滤。如 select a.id,b.name from a left join b on a.id=b.id and b.name='test' 和 select a.id,b.name from a left join b on a.id=b.id where b.name='test' 执行的结果可能是不一样的，前者可以包含a表所有记录，后者可能只包含a表部分记录。

12、对大表特别是处于生产环境的大表操作慎用 insert into table_a select * from table_b，会造成长时间锁表，应先查询出来后再通过程序插入。

13、mysql数据库存储过程执行的时候突然报主键冲突错误，但单独执行sql语句正常，重新编辑保存存储过程后恢复（不知道为啥，遇到这种情况不妨尝试下）。

14、Druid+Mysql默认使用的是ping的方式去判断连接是否有效。我们给druid配置的validationQuery根本就不会执行。在在进程的启动参数中设置-Ddruid.mysql.usePingMethod=false即可。