MySQL使用中遇到了一些小”坑”和一些不便,无聊来总结一下.

IN子句逻辑问题

这个是在给同事调BUG时发现的,展示之前先初始化一些数据.

create table mysql_pitfalls(
	c1 int,
	c2 varchar(128),
	c3 datetime,
	c4 timestamp
);
-- 插入测试数据
insert into mysql_pitfalls(c1,c2,c3,c4) values(1,'1',now(),now());
insert into mysql_pitfalls(c1,c2,c3,c4) values(2,'2',now(),now());
insert into mysql_pitfalls(c1,c2,c3,c4) values(3,'3',now(),now());
insert into mysql_pitfalls(c1,c2,c3,c4) values(4,'4',now(),now());

下面我们分别执行以下两条SQL

mysql> select * from mysql_pitfalls where c1 in (1,2,3);
+------+------+---------------------+---------------------+
| c1   | c2   | c3                  | c4                  |
+------+------+---------------------+---------------------+
|    1 | 1    | 2015-06-06 19:00:05 | 2015-06-06 19:00:05 |
|    2 | 2    | 2015-06-06 19:00:08 | 2015-06-06 19:00:08 |
|    3 | 3    | 2015-06-06 19:00:11 | 2015-06-06 19:00:11 |
+------+------+---------------------+---------------------+
3 rows in set (0.00 sec)

这条SQL很简单,C1列是数值型的,IN逻辑正确.接下面再看一句有逻辑问题的查询,去IN一个字符串

-- 瞬间就被玩坏了
mysql> select * from mysql_pitfalls where c1 in ('1,2,3');
+------+------+---------------------+---------------------+
| c1   | c2   | c3                  | c4                  |
+------+------+---------------------+---------------------+
|    1 | 1    | 2015-06-06 19:00:05 | 2015-06-06 19:00:05 |
+------+------+---------------------+---------------------+
1 row in set, 1 warning (0.00 sec)

同样是数值型C1列,查询如果IN的条件是一个带逗号的字符串,IN条件会错误命中字符串中第一个逗号之前的数字.虽然这条SQL写错了,但这本身算是一个逻辑错误,明明不相等,IN去处怎么能匹配成功呢.再者,由于错误返回了每一条数据,有时候会麻痹开发和测试,误认为功能没有问题.

PS: 可以试试执行IN (‘1,2,3′,’2,3,4′) , 会发现MySQL会求每一个带逗号字符串的第一个值.

 

时间精度丢失(5.6解决)

MySQL在5.6之前,无论是DATETIME类型或是TIMESTAMP类型都无无法存储毫秒,以至于在对时间有毫秒精度要求的场景下,我直接选用了INT型作为存储时间的类型.

首先展示一下,MySQL提供的时间函数,是能够支持毫秒的

mysql> select microsecond('2015-1-1 12:00:00.3213') microsecond;
+-------------+
| microsecond |
+-------------+
| 321300 |
+-------------+
1 row in set (0.00 sec)

之前在测试表中,分别创建了DATETIME和TIMESTAMP的两个字段,C1和C2,我们使用microsecond函数测试一下
阅读全文