SQL 写的秒，涨薪呱呱叫!就在前不久公司新来的实习生因为写错了一条SQL把数据库搞炸了。
图片来自 Pexels
新来的实习生小杨写了一条 SQL 语句：
- SELECT wx_id from `user` WHERE wx_id = 2
当小杨迫不及待准备下班回家的时候，隔壁的王经理一把抓住了小杨，并用 EXPLAIN 命令教育了小杨，小杨流下了没有文化的泪水。
这条 SQL 语句中，wx_id 是具有索引的，但是王经理查出来的结果却是这样的：
王经理的教育
小杨仔细一瞅 key 字段显示为 Null，很明显这条 SQL 语句没有走索引。
小杨心想“糟糕，又写错 SQL 语句了，这下又要面临运维和经理的混合双打了， 不行我得立马改下这条 SQL 语句，让我想想哪里出错了”!
小杨脑袋瓜疯狂乱撞，仔细回想表结构，忽然想到，wx_id 字段是 varchar 类型，自己查询的时候竟然没有加引号。
小杨一把抢过经理手里的键盘，往 wx_id 的查询条件上加了引号，结果：
果然这条 SQL 语句开始走了索引。小杨沾沾自喜以为解决了个天大的 Bug。
经理微微一笑问道“你知道为什么为什么加了引号就走了索引吗?如果字段是 int 类型，那么查询的时候需不需要加引号呢?又是为什么呢?”
正餐来了
小杨被问的呆在原地，无法回答。
经过小杨研究发现，如果字段是 varchar类型，等号右侧必须加引号才走索引;如果字段是 int 类型，那么等号右侧加不加引号都是会走索引的。
什么?你不相信小杨说的话，有图有真相。(bonus 字段类型为int)
真相图
但是结论出来，还是无法回答经理的夺命三连问。
小杨搬来了答案
在 MySQL 查询中，当查询条件左右两侧类型不匹配的时候会发生隐式转换：
- 也就是说
- SELECT wx_id from `user` WHERE wx_id = 2
- 等价于
- SELECT wx_id from `user` WHERE CAST(wx_id AS signed int) = 2
一旦对索引字段做函数操作，MySQL 会放弃使用索引。
所以如果字段是 varchar 类型，等号右侧必须加引号才走索引，否则由于隐式转换，MySQL 会放弃使用索引。那么凭什么 int 加不加引号都可以使用索引呢?
那是因为 int 类型的数字只有 2 能转化为'2'，是唯一确定的。所以虽然需要隐式转换，但不影响使用索引
小杨追问：“你还能在告诉我一些隐式转换的知识吗?”
我反手就是一个英文文档：
- If one or both arguments are NULL, the result of the comparison is NULL, except for the NULL-safe <=> equality comparison operator. For NULL <=> NULL, the result is true. No conversion is needed.
- If both arguments in a comparison operation are strings, they are compared as strings.
- If both arguments are integers, they are compared as integers.
- Hexadecimal values are treated as binary strings if not compared to a number.
- If one of the arguments is a TIMESTAMP or DATETIME column and the other argument is a constant, the constant is converted to a timestamp before the comparison is performed. This is done to be more ODBC-friendly. Note that this is not done for the arguments to IN()! To be safe, always use complete datetime, date, or time strings when doing comparisons. For example, to achieve best results when using BETWEEN with date or time values, use CAST() to explicitly convert the values to the desired data type.
- A single-row subquery from a table or tables is not considered a constant. For example, if a subquery returns an integer to be compared to a DATETIME value, the comparison is done as two integers. The integer is not converted to a temporal value. To compare the operands as DATETIME values, use CAST() to explicitly convert the subquery value to DATETIME.
- If one of the arguments is a decimal value, comparison depends on the other argument. The arguments are compared as decimal values if the other argument is a decimal or integer value, or as floating-point values if the other argument is a floating-point value.
- In all other cases, the arguments are compared as floating-point (real) numbers.
贴心的我帮你们翻译成了中文：
- 1, 两个参数至少有一个是 NULL 时，比较的结果也是 NULL，例外是使用 <=>
- 对两个 NULL 做比较时会返回 1，这两种情况都不需要做类型转换
- 2, 两个参数都是字符串，会按照字符串来比较，不做类型转换
- 3, 两个参数都是整数，按照整数来比较，不做类型转换
- 4, 十六进制的值和非数字做比较时，会被当做二进制串
- 5, 有一个参数是 TIMESTAMP 或 DATETIME，并且另外一个参数是常量，常量会被转换为 timestamp
- 6, 有一个参数是 decimal 类型，如果另外一个参数是 decimal 或者整数会将整数转换为 decimal 后进行比较，
- 如果另外一个参数是浮点数，则会把 decimal 转换为浮点数进行比较
- 7, 所有其他情况下，两个参数都会被转换为浮点数再进行比较
再分享一个隐式转换的坑：你是否偶尔删除了一些不知道的数据?
- mysql> select * from test;
- +----+-------+-----------+
- | id | name | password |
- +----+-------+-----------+
- | 1 | test1 | password1 |
- | 2 | test2 | password2 |
- | 3 | aaa | aaaa |
- | 4 | 55aaa | 55aaaa |
- | 5 | 1212 | aaa |
- | 6 | 1212a | aaa |
- +----+-------+-----------+
- 6 rows in set (0.00 sec)
- mysql> select * from test where name = 1212;
- +----+-------+----------+
- | id | name | password |
- +----+-------+----------+
- | 5 | 1212 | aaa |
- | 6 | 1212a | aaa |
- +----+-------+----------+
- 2 rows in set, 5 warnings (0.00 sec)
- mysql> select * from test where name = '1212';
- +----+------+----------+
- | id | name | password |
- +----+------+----------+
- | 5 | 1212 | aaa |
- +----+------+----------+
- 1 row in set (0.00 sec)
上面的例子本意是查询 id 为 5 的那一条记录，结果把 id 为 6 的那一条也查询出来了。我想说明什么情况呢?
有时候我们的数据库表中的一些列是 varchar 类型，但是存储的值为‘1123’这种的纯数字的字符串值，一些同学写 SQL 的时候又不习惯加引号。
这样当进行 Select，Update或者 Delete 的时候就可能会多操作一些数据。所以应该加引号的地方别忘记了。
总而言之
隐式类型转换有无法命中索引的风险，在高并发、大数据量的情况下，命不中索引带来的后果可不止被运维和经理混合双打哦!且写 SQL 且 EXPLAIN!
作者：isysc1
编辑：陶家龙
出处：转载自微信公众号码儿嘟嘟骑(ID：maer_duduqi)
