6. 不要让程序员去写SQL #11
Description
最近服务器开始莫名其妙的报错：
com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Truncated incorrect DOUBLE value: '1-1'
表的结构类似于下面这样：
CREATE TABLE `test` (
`id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '主键',
`lv` integer(11) NOT NULL COMMENT '等级',
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '名字',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ;id是字符串，不是数字，格式类似于1-1，2-1这样。
表中的数据如下：
报错的语句是一个update语句：
update `test` set `lv`=1 where `id` = 2-2
说实话，刚看到错误的时候觉得莫名其妙，我要更新2-2的行，为什么报错1-1呢？我开始在网上搜索报错代码。
先搜到的是参考资料1的那篇文章。
文章中作者提到了，如果在where语句中使用一个字符串类型的列，而条件忘记增加单引号，会出现以下两个问题：
- 全表查询，即使这个列已经是索引列了，但是还是会触发全表查询
- 莫名其妙的会出现类似于like的效果
下图是作者文章的大概介绍：
表结构以及提到的全文检索见下图：
可以看到，如果where语句中不加引号，则会触发全表检索；而加了引号就会使用索引。
再来看看检索结果：
可以看到，当where语句中42加引号时，什么都没有查询出来，但是把引号去掉了，却查询出了42开头数据的对应行。
作者接下来查看MySQL的warnings，得到如下结果：
show warnings;
| Warning | 1292 | Truncated incorrect DOUBLE value: '80f52706c2f9de40472ec29a7f70c992' |和下面的：
show warnings;
| Warning | 1739 | Cannot use ref access on index 'data' due to type or collation conversion on field 'data'
| Warning | 1739 | Cannot use range access on index 'data' due to type or collation conversion on field 'data'读到这里，我模糊的意识到一件事情：因为没有加引号，MySQL做了全表检索，对于每一行的data数据来说，MySQL尝试去对它进行强制转换（成double），但是失败了，所以报出了Truncated incorrect DOUBLE value的错误；同时MySQL还会把字符串从第一个字符开始，尽可能的截出一个数字来，一直截到第一个不为数字的字符为止。这也是为什么42不加引号时，效果类似于like。
第一篇参考资料解释了我的报错中为什么报出的是"1-1"，而不是在update中的"2-4"。那我的疑问随之而来，为什么我的update语句中的"2-4"没有报错呢？继续查询。
然后我找到了参考资料2的bug
这是MySQL上有人给报出的bug，他的情况和我的类似，id是字符串，表结构如下：
create table t (id char(36), id2 varchar(36) null)数据如下：
作者先是做了如下操作，成功了：
然后再次执行的时候，报出了和我类似的错误：
然后作者不死心，加上了cast，成功了：
很有意思的一个作者，遇到的情况也与我类似。但是最让我注意的是下面回答问题的第一个人，他写道：
You are probably running 'strict mode'? In non-strict mode I get warnings but the UPDATEs actually do perform.
...
So it is the comparison in the WHERE clause that causes this. An integer is being compared with a string and both are converted to double for the comparison. I think this is expected...
他这里提到了两个两个信息：
- MySQL有个strict mode
- where语句中，如果不加引号则是数字，如果要比较的数据列是字符串，双方就都会被强转成double类型来比较，这就是报错中double的来源。
但是还是没有解决我上面的问题，为什么我的"2-4"没有报错。不过这里提供了一个新的名词：strict mode，于是我找到了MySQL官网对于strict mode的解释：
Strict mode controls how MySQL handles invalid or missing values in data-change statements such as INSERT or UPDATE. A value can be invalid for several reasons. For example, it might have the wrong data type for the column, or it might be out of range. A value is missing when a new row to be inserted does not contain a value for a non-NULL column that has no explicit DEFAULT clause in its definition. (For a NULL column, NULL is inserted if the value is missing.) Strict mode also affects DDL statements such as CREATE TABLE.
If strict mode is not in effect, MySQL inserts adjusted values for invalid or missing values and produces warnings. In strict mode, you can produce this behavior by using INSERT IGNORE or UPDATE IGNORE.
For statements such as SELECT that do not change data, invalid values generate a warning in strict mode, not an error.
Strict mode produces an error for attempts to create a key that exceeds the maximum key length. When strict mode is not enabled, this results in a warning and truncation of the key to the maximum key length.
大意就是说strict mode会对让表有修改的语句进行检查，可以关掉或者用ignore关键字忽略掉。有兴趣的可以去参考资料3去围观，这里就不赘述了。
看到这里对于我来说还是云里雾里的。大概知道报错是怎么来的了，但是疑问依然一大堆：
- 为什么我的语句中"2-4"没报错？
- strict mode到底为什么存在，用来干什么？
直到我找到了参考资料4和5，两位日本大哥的分享
在其中一位叫寺田 健的日本大哥的分享中，最重要的是东西是从下面这个例子开始的：
这个表很简单，大哥对这个表执行了以下的命令：
mysql> SELECT * FROM product WHERE name='tomato'+'';注意大哥执行的where语句中'tomato'+''，他把tomato后面用加号，加上了也给空字符。大哥得到了下面的结果：
说实话，看到这里我也挺诧异的，所有数据都出来了。为啥？
大哥开始解释，核心意思是：对于+这个运算符来说，在MySQL中就是一个加法运算，没有我们认为的字符串连接的功能。对于'tomato'+''这个来说，MySQL会尝试把+两侧的都当作数字来处理。诚然，tomato和空字符不可能转换成有效数字，那么MySQL就把它们转换成了0，所以，'tomato'+''这个东西就相当于是0+0，即：
'tomato+'' => 0+0 => 0
最后语句就相当于：
SELECT * FROM product WHERE name=0;其实可以使用纯select语句来验证：
好，现在SQL语句里name是一个数字了，因为表里的name是字符串，所以类型不同，根据上面参考文章的分析，需要将两方转换成double，而表中所有的name字段都是纯字符，因此转换成有效数据，也是0，所以就会得到全部的数据。
在这种情况下，如果去执行update语句，就会发生可怕的后果，下面是大哥执行的情况：
这种情况下，所有的price都被修改成了999。
另外一个日本大哥的slide里分享的，就是关于在SQL注入情况下，如果被人加入了+''的时候，会导致整个库出现异常。
在slide中，另外的日本大哥总结了，如果想进行字符串连接，不同的数据库应该使用什么：
可以看到，MySQL需要使用空格或者CONCAT函数来连接字符串，而不能是用加号。
鉴于此，MySQL增加了strict mode，来防止对表进行修改的操作出现上述的这些问题。
另外，这里也就解释了我上面的疑问，"2-4"为什么不报错：因为不加引号的情况下，"2-4"就是存粹的数学运算，结果是-2。实际上，我做了实验，我们能想到的任意运算符，除了加减乘除，还有^(异或)，&(与)，|(或)，!(非)，%(取模)，&&，||等，都可以看做是数学运算。
至于数字和表中字符串的比较，MySQL会向double来转换，自然就产生了本文中一上来碰到的错误。
为了验证日本大哥说的，我拿上面我的表做了一些实验。
我现在表中的数据：
大家可以仔细看以下，name字段中，只有第二行是1carot，前面我专门增加了一个数字，用来之后验证MySQL强制转换double时的行为。
先看第一个执行结果：
和日本大哥说的一样，但是我们可以看到，没有1carot所在的行，所以我们推断MySQL会从第一个字符进行转换，直到第一个不能转换的数字为止。
我们可以看第二个执行结果：
只要我们尝试转换字符1，就会得到1carot所在的行。
最后我们看一下id是1221sadflkjweoiurkljsdf的行，这次我们用id来查询，看一下结果：
可以看到，没有1221sadflkjweoiurkljsdf，即使它也是1开头的。
当我们使用1221的时候，结果出现了，证明了我们的猜测。
最后再执行一个：
这个很明显了，减号就是存粹的数学运算。
下面是执行后的warnings：
总结
- 不用引号的运算符会当作数学运算来使用
- 字符串在数学运算中会被强制转换，原则就是从第一个字符开始，能转多少转多少，到第一个不能转换的字符为止，一个都没有就是0
- strict mode的引入，帮助我们有效的避免了类似于SQL注入时带来的风险
参考资料：