Mysql的隐式转换

Mysql的隐式转换：

来我们先看看官方文档：
        When an operator is used with operands of different types, type conversion occurs to make the operands compatible. Some conversions occur implicitly. 
        For example, MySQL automatically converts strings to numbers as necessary, and vice versa.

我们常见的different types operands 有：

1.strings and numbers        

当值类型和字符串类型进行比较的时候，Mysql做的操作是，把字符串类型转换成值类型

我们实际测试一下看看：

mysql> select '2.34'=2.34,'2.34abc'=2.34,'abc2.34'=2.34,'2 .34abc'=2.34,'2 .34abc'=2;
+-------------+----------------+----------------+-----------------+--------------+
| '2.34'=2.34 | '2.34abc'=2.34 | 'abc2.34'=2.34 | '2 .34abc'=2.34 | '2 .34abc'=2 |
+-------------+----------------+----------------+-----------------+--------------+
|           1 |              1 |              0 |               0 |            1 |
+-------------+----------------+----------------+-----------------+--------------+
1 row in set, 4 warnings (0.00 sec)

mysql> show warnings;
+---------+------+----------------------------------------------+
| Level   | Code | Message                                      |
+---------+------+----------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: '2.34abc'  |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'abc2.34'  |
| Warning | 1292 | Truncated incorrect DOUBLE value: '2 .34abc' |
| Warning | 1292 | Truncated incorrect DOUBLE value: '2 .34abc' |
+---------+------+----------------------------------------------+
4 rows in set (0.00 sec)

mysql> select '2.34'+1,'2.34abc'+1,'abc2.34'+1;
+----------+-------------+-------------+
| '2.34'+1 | '2.34abc'+1 | 'abc2.34'+1 |
+----------+-------------+-------------+
|     3.34 |        3.34 |           1 |
+----------+-------------+-------------+
1 row in set, 2 warnings (0.02 sec)

        所以在写sql的时候，我们尽量要避免类型间的隐式转换，如果隐式转换的是入参的话，查询结果可能不准；如果隐式转换的是字段的话，那么这样转换之后，即使在该列上有索引，也无法使用索引的快速扫描了。
        举例说明：

mysql> show create table test1017\G
*************************** 1. row ***************************
       Table: test1017
Create Table: CREATE TABLE `test1017` (
  `id` int(10) NOT NULL,
  `m1` varchar(5) DEFAULT NULL,
  `m2` int(4) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_1017_m1` (`m1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> insert into test1017 values(1,'12',1),(2,'12a',2),(3,'a12',3),(4,'12.a',4);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> explain select id,m1 from test1017 where  m1=12;
+----+-------------+----------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
| id | select_type | table    | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+----------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | test1017 | NULL       | index | idx_1017_m1   | idx_1017_m1 | 18      | NULL |    4 |    25.00 | Using where; Using index |
+----+-------------+----------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
1 row in set, 3 warnings (0.00 sec)

mysql> explain select id,m1 from test1017 where  m1='12';
+----+-------------+----------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | test1017 | NULL       | ref  | idx_1017_m1   | idx_1017_m1 | 18      | const |    1 |   100.00 | Using index |
+----+-------------+----------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> select id,m1 from test1017 where  m1=12;
+----+------+
| id | m1   |
+----+------+
|  1 | 12   |
|  4 | 12.a |
|  2 | 12a  |
+----+------+
3 rows in set, 3 warnings (0.00 sec)

mysql> select id,m1 from test1017 where  m1='12';
+----+------+
| id | m1   |
+----+------+
|  1 | 12   |
+----+------+
1 row in set (0.00 sec)

        我们可以看到在入参是值类型，导致m1需要做隐式转换之后，我们发现sql语句不走idx_1017_m1的快速扫描了，转而走了索引全扫描。
        而且我们发现，入参是12和‘12’时，查询结果集不一样！！

总结：
        1.当值类型和字符串类型进行运算的时候，Mysql把字符串类型转换成值类型
        2.如果字符串不是以值类型开头的，那么Mysql在比较时直接将字符串类型转化成数值0
        3.如果字符串是以值类型开头的，那么Mysql在运算时会从字符串的首位开始，"尽可能的截取值"，然后返回