MySQL中的那些坑

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 |
+------+------+---------------------+---------------------+
 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 |
+------+------+---------------------+---------------------+
 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 |
+-------------+
 row in set (0.00 sec)

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

-- 使用同样的日期数据，插入到C3,C4中
mysql> insert into mysql_pitfalls(c1,c2,c3,c4)
values(5,'5','2015-1-1 12:00:00.3213','2015-1-1 12:00:00.3213');
Query OK, 1 row affected (0.05 sec)

-- 执行查询，测试microsecond，全部精度丢失了
mysql> select c1,c2,microsecond(c3),microsecond(c4) from mysql_pitfalls;
+------+------+-----------------+-----------------+
| c1 | c2 | microsecond(c3) | microsecond(c4) |
+------+------+-----------------+-----------------+
| 1 | 1 | 0 | 0 |
| 2 | 2 | 0 | 0 |
| 3 | 3 | 0 | 0 |
| 4 | 4 | 0 | 0 |
| 5 | 5 | 0 | 0 |
+------+------+-----------------+-----------------+
 rows in set (0.00 sec)

无论是DATETIME和TIMESTAMP，不管是NOW()函数还是带毫秒的时间字面量，MySQL的最小存储精度都是秒，毫秒精度都丢失了．
官方文档对这个问题分别有描述， 5.1的文档中，有这么一段

A DATETIME or TIMESTAMP value can include a trailing fractional seconds part in up to microseconds (6 digits) precision. Although this fractional part is recognized, it is discarded from values stored into DATETIME or TIMESTAMP columns. For information about fractional seconds support in MySQL, see Section 11.3.6, “Fractional Seconds in Time Values” .

而在5.6.4的文档，已经明确表示这个问题已经修复了．

IFNULL与日期类型问题

MySQL普通的日期操作还算方便，Date，String与Double类型之都有隐式转换．但转换的程度有点过了，导致在操作日期时总感觉像是写弱类型的脚本语言让人不放心，而实事上这三者之前的隐式的转换确实会引起很多的问题都需要注意的．不过这里不说转换的问题，说一个IFNULL的返回类型小问题．

接着之前的表和数据，插入一条明显大于当前日期的记录

mysql> insert into mysql_pitfalls(c1,c2,c3,c4) values(7,'7',date_add(now(),interval 10 day),date_add(now(),interval 10 day));
Query OK, 1 row affected (0.07 sec)

mysql> select c1,c3 from mysql_pitfalls;
+------+---------------------+
| c1 | c3 |
+------+---------------------+
| 1　 | 2015-06-06 19:00:05 |
| 2　 | 2015-06-06 19:00:08 |
| 3　 | 2015-06-06 19:00:11 |
| 5 　| 2015-01-01 12:00:00 |
| 41　| 2015-06-06 20:07:22 |
| 4 　| 2015-06-06 20:08:05 |
| 7 　| 2015-06-30 16:15:50 |
+------+---------------------+
 rows in set (0.00 sec)

再分别执行下面两条测试脚本

mysql> select c1,c3 from mysql_pitfalls where c3 >= now() + 5;
+------+---------------------+
| c1 | c3 |
+------+---------------------+
| 7 | 2015-06-30 16:15:50 |
+------+---------------------+
 row in set (0.00 sec)

#　这一句的ifnull逻辑上没有作用，与每一条相同相同，但结果集却不一致:查不出任何数据

mysql> select c1,c3 from mysql_pitfalls where ifnull(c3,c3) >= now() + 5;
Empty set (0.00 sec)

两条SQL的逻辑是一致的，但结果却不一致，网上说是因为IFNULL函数返回了字符型，类型发生了变化，通过查看官方文档，确实有说明IFNULL后类型会发生变化．
不过似乎这个解释也不能很好的解释，可以继续测试一下到底是什么类型

mysql> create table tmp as select now() + 5 as dateplus, c3, ifnull(c3,c3) trans_c3 from mysql_pitfalls;
Query OK, 7 rows affected (0.19 sec)
Records: 7 Duplicates: 0 Warnings: 0

mysql> desc tmp;
+----------+--------------+------+-----+----------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+----------+-------+
| dateplus　| double(23,6)  | NO  | | 0.000000 | |
| c3　      | datetime 　　　| YES | | NULL | |
| trans_c3　| datetime 　　　| YES | | NULL | |
+----------+--------------+------+-----+----------+-------+
 rows in set (0.02 sec)

mysql>

不确认在新建表的过程中是否发生了新的隐式转换，从结果上来看，IFNULL返回了DATE类型，与原始类型一致．总之在处理日期，特别是在各种类型比较，使用多种操作符，函数做操作时要很小心验证结果．如开头所说，可以将MySQL中的日期类型处理视为弱类型语言编程，需要多留意．而且MySQL这种类型的问题还不少．

更新时，表无法做为条件嵌套引用

我们尝试从表中删除一行时间最大的列，其实可选的方法不少．但如果采用下面的语句，会抛出异常

mysql> delete from mysql_pitfalls where c4 = ( select max(c4) d from mysql_pitfalls);
ERROR 1093 (HY000): You can't specify target table 'mysql_pitfalls' for update in FROM clause

如果只是想删除最大一行的话，绕开的方法不少，也不算上让人郁闷．但最让人郁闷的是，其它这条SQL稍微改造，再嵌套一层使子查询彻底变成内存表就可以使用了，而MySQL自身并没有做这样的优化

--　再嵌套一层就可以正常使用了
mysql> delete from mysql_pitfalls where c4 =
(select max(d) from ( select max(c4) d from mysql_pitfalls) a );
Query OK, 1 row affected (0.04 sec)

当然，这只能算＂体验＂问题．

字符串前后空格问题

如果varchar列的值前后存在空格，那将引起一起逻辑问题，以5.5为例，先看对查询条件的影响，

--插入一条带前后空格的字符串
mysql> insert into mysql_pitfalls(c1,c2,c3,c4) values(41,'4 ',now(),now());
Query OK, 1 row affected (0.03 sec)

mysql> select * from mysql_pitfalls where c2 = '4';
+------+------+---------------------+---------------------+
| c1   | c2   | c3                  | c4                  |
+------+------+---------------------+---------------------+
|   41 | 4_   | 2015-06-06 20:07:22 | 2015-06-06 20:07:22 | --错误命中
|    4 | 4    | 2015-06-06 20:08:05 | 2015-06-06 20:08:05 |
+------+------+---------------------+---------------------+
 rows in set (0.00 sec)

MySQL 5.0.3之后，VARCHAR的存储时，前后空格能够正确存储，但在作为查询条件时，前后空格将会被忽略，引起了查询和插入时的逻辑不一致．而定长的CHAR逻辑又不相同，还与MySQL模式相关， 具体的说可以看看这里 ，讲的很清楚．

类似于这样的问题，无论怎么处理只要逻辑一致，我们都可以视为MySQL的特性或规则（Feature&Rule）．但问题就在于，这些个＂特性＂的表现并不完全符合规则，不同场景的下表现缺乏一致性，由此造成了一些误解．

下面就有一个例子，证明这些规则缺乏相互推导，接着刚才，如果这时我们尝试在建一个唯一索引，会抛出异常

mysql> ALTER TABLE mysql_pitfalls ADD UNIQUE INDEX `c2_UNIQUE` (`c2` ASC);

ERROR 1062 (23000): Duplicate entry '4 ' for key 'c2_UNIQUE'

在存储时被保留的空格，在创建索引时又被忽略了，’4’和’4 ‘被认为是相同的值．

忽略或者不忽略，多种场景下，并不具备普遍的适用性．

Group By: 选取非分组列

这个可能是我遇到第一个让人奇怪的功能，算起来，这可能不算做＂坑＂，而更像是MySQL的特色．

在其它关系型数据库中，在拥Group By子句的情况下，只能select出被分组的列，对于非分组的列，除非使用聚合函数，这列将无法选择，原理也很简单，画一个二维表就能明白．但是在MySQL中，即使在Select中选择了非分组，MySQL也不会抛出语法错误，而是会默认选择这一组中这列的最后一个值．

从一方面来讲，这个功能提供了很多的灵活性．但另一方面，由于这个过程是静默，不会提示．会对开发和测试造成一定程度的麻痹（这种SQL不符合严谨的逻辑），而且造成了程序的不确定性． 这个功能可以通过显式修改MySQL运行模式，变得和普通RDBMS一致: ONLY_FULL_GROUP_BY

JDBC与TINYINT(1)

无伤大雅的小毛病，更多的应该算是JDBC驱动的问题．

如果数据库中声明字段类型是TINYINT(1)，那通过JDBC拿到的值，默认将会是Boolean型，其中，如果等于０，则为false，否则为true，注意的是，即使小于0，如-1，值也为True．

可以通过resultSet.getInt()方法获取到正确的值，如果使用框架也可以显式指定类型，但如果想采用相对弱类型的编程方式，如使用map或者json之类的，这那将会变得有点烦，无论是框架配置或者是代码，总得去转换一下．

除此之外，也可以在连接字符串上加下tinyInt1isBit=false参数，这样从TinyInt(1)拿到值将会是数值型．另外有趣的是，如果将字段类型声明为TinyInt(2)，只要其Length大于1，那这个值又将默认是数值型了．