Mysql的奇淫怪技

在项目开发的过程中，包括测试人员提出的一些bug，慢慢总结出一些Mysql的使用经验。

1. decimal类型的字段总长度包括了精度长度。

比如decimal(10, 2)，则表示小数点左边为8位，右边为2位。decimal类型字段常用于存储精度要求高的数字。

 

2. 字段默认不区分大小写。

测试跟我说，产品名判断是否重名时有bug，不能区分大小写。存在名为test的产品，再取TEST时竟然提示重名。

解决方法有几种：

（1）添加binary关键字

SELECT * FROM table WHERE BINARY name = 'test';

（2）设置字符集为utf8_bin 

utf8_bin：区分大小写；    utf8_general_ci：不区分大小写

 

3. 当有GROUP BY时，不能直接用COUNT(*)计算行数。

比如：

SELECT * FROM table WHERE conditions GROUP BY column_1, column_2 LIMIT pagestart, pagesize;

无法用以下sql语句来计算行数：

SELECT COUNT(*) FROM table WHERE conditions GROUP BY column_1, column_2;

需将GROUP BY的部分放入COUNT里，即改成：

SELECT COUNT(DISTINCT column_1, column_2) FROM table WHERE conditions;

 

4.用LIMIT来实现分页，需要两条sql语句。

（1）用LIMIT offset, pagesize来实现分页；

（2）同时要获取同等sql（去除分页条件）结果的总数量，来给前端进行分页计算。

这里有个优化sql的地方：LIMIT的offset越大，效率越低

原本：

SELECT id FROM table LIMIT 100000, 10;

改进：

SELECT id FROM table WHERE id > 100000 LIMIT 10;

 

5. datetime类型字段自动更新时间的问题。

项目发现一个bug，修改表中数据会记录修改时间updated_at，结果创建时间created_at也跟着更新，导致某个定时任务的逻辑判断出了问题。

当表中有datetime类型的字段a和b，其中b字段设置了ON UPDATE CURRENT_TIMESTAMP。当修改了字段a时，b会自动更新为最新时间。

所以尽量把datetime类型字段的ON UPDATE CURRENT_TIMESTAMP自动设置更新时间这个特性去掉，由业务层去设置时间。

 

6.存储json字符串可以用json类型字段。