mysql隐式转换

mysql隐式转换 （版本 5.7）

表结构如下：

 Field    | Type             | Null | Key | Default | Extra          |
+----------+------------------+------+-----+---------+----------------+
| id       | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name     | varchar(50)      | NO   | MUL |         |                |
| pwd      | varchar(50)      | NO   |     |         |                |
| group_id | int(11)          | NO   | MUL | 0       |                |

添加的几条数据

mysql> select * from account;
+----+---------------+---------+----------+
| id | name          | pwd     | group_id |
+----+---------------+---------+----------+
|  1 | chujiu        | c123456 |        1 |
|  2 | xiaodongbanji | x78910  |        0 |
|  3 | 1             | 123456  |        2 |
|  4 | cuiyang       | 123456  |        3 |
+----+---------------+---------+----------+
4 rows in set (0.00 sec)

字段类型varchar, 查询条件为int和string 比较

mysql> explain select * from account where name = '1';
+----+-------------+---------+------------+------+---------------+------------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | account | NULL       | ref  | name_index    | name_index | 202     | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from account where name = 1;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | account | NULL       | ALL  | name_index    | NULL | NULL    | NULL |    4 |    25.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 3 warnings (0.00 sec)

再看一个：

mysql> select 'a'+1;
+-------+
| 'a'+1 |
+-------+
|     1 |
+-------+
1 row in set, 1 warning (0.00 sec)

mysql> select 'a'+0;
+-------+
| 'a'+0 |
+-------+
|     0 |
+-------+
1 row in set, 1 warning (0.00 sec)

mysql> select 'a'+'b';
+---------+
| 'a'+'b' |
+---------+
|       0 |
+---------+
1 row in set, 2 warnings (0.00 sec)

mysql> select * from account where name='chujiu'+'a' and pwd='ccc'+0;
+----+---------------+---------+----------+
| id | name          | pwd     | group_id |
+----+---------------+---------+----------+
|  1 | chujiu        | c123456 |        1 |
|  2 | xiaodongbanji | x78910  |        0 |
+----+---------------+---------+----------+
2 rows in set, 8 warnings (0.00 sec)

mysql> show warnings;
+---------+------+---------------------------------------------------+
| Level   | Code | Message                                           |
+---------+------+---------------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'chujiu'        |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'chujiu'        |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'a'             |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'c123456'       |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'ccc'           |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'xiaodongbanji' |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'x78910'        |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'cuiyang'       |
+---------+------+---------------------------------------------------+
8 rows in set (0.00 sec)

可以这样看结果：

mysql> select * from account where name=0 and pwd=0;
+----+---------------+---------+----------+
| id | name          | pwd     | group_id |
+----+---------------+---------+----------+
|  1 | chujiu        | c123456 |        1 |
|  2 | xiaodongbanji | x78910  |        0 |
+----+---------------+---------+----------+
2 rows in set, 5 warnings (0.00 sec)

mysql> select * from account where name=0;
+----+---------------+---------+----------+
| id | name          | pwd     | group_id |
+----+---------------+---------+----------+
|  1 | chujiu        | c123456 |        1 |
|  2 | xiaodongbanji | x78910  |        0 |
|  4 | cuiyang       | 123456  |        3 |
+----+---------------+---------+----------

这样会把name字段转换为整型在和0去比较，那么字符开头的都转为0了，那么当然返回的就是所有行，如果name字段有开头为整型的字符，那么查出来的就不包括整型开头的行

mysql> select * from account where name ='' or name <> '';
+----+---------------+---------+----------+
| id | name          | pwd     | group_id |
+----+---------------+---------+----------+
|  1 | chujiu        | c123456 |        1 |
|  2 | xiaodongbanji | x78910  |        0 |
|  3 | 1             | 123456  |        2 |
|  4 | cuiyang       | 123456  |        3 |
+----+---------------+---------+----------+
4 rows in set (0.01 sec)

添加几条数据

mysql> insert into account (name, pwd, group_id) values('123c', '123aa', 4),('123d', '123bb', 5),('12e', '456bb', 6);
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select *from account where 1;
+----+---------------+---------+----------+
| id | name          | pwd     | group_id |
+----+---------------+---------+----------+
|  1 | chujiu        | c123456 |        1 |
|  2 | xiaodongbanji | x78910  |        0 |
|  3 | 1             | 123456  |        2 |
|  4 | cuiyang       | 123456  |        3 |
|  5 | 123c          | 123aa   |        4 |
|  6 | 123d          | 123bb   |        5 |
|  7 | 12e           | 456bb   |        6 |
+----+---------------+---------+----------+
7 rows in set (0.00 sec)

mysql> select *from account where name = 123;
+----+------+-------+----------+
| id | name | pwd   | group_id |
+----+------+-------+----------+
|  5 | 123c | 123aa |        4 |
|  6 | 123d | 123bb |        5 |
+----+------+-------+----------+
2 rows in set, 6 warnings (0.00 sec)

mysql> select *from account where name = 12;
+----+------+-------+----------+
| id | name | pwd   | group_id |
+----+------+-------+----------+
|  7 | 12e  | 456bb |        6 |
+----+------+-------+----------+
1 row in set, 6 warnings (0.00 sec)

mysql> delete from  account where name = 123;
ERROR 1292 (22007): Truncated incorrect DOUBLE value: 'chujiu'

mysql> update account set pwd = '111' where name = 123;
ERROR 1292 (22007): Truncated incorrect DOUBLE value: 'chujiu'

sql执行成功了，为啥数据没变呢？？？

mysql> update account set pwd = '111' where name = '123';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> select * from account where 1;
+----+---------------+---------+----------+
| id | name          | pwd     | group_id |
+----+---------------+---------+----------+
|  1 | chujiu        | c123456 |        1 |
|  2 | xiaodongbanji | x78910  |        0 |
|  3 | 1             | 123456  |        2 |
|  4 | cuiyang       | 123456  |        3 |
|  5 | 123c          | 123aa   |        4 |
|  6 | 123d          | 123bb   |        5 |
|  7 | 12e           | 456bb   |        6 |
+----+---------------+---------+----------+
7 rows in set (0.00 sec)

隐式转化规则

官方文档中关于隐式转化的规则是如下描述的：

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.

翻译过来就是：

两个参数至少有一个是 NULL 时，比较的结果也是 NULL，例外是使用 <=> 对两个 NULL 做比较时会返回 1，这两种情况都不需要做类型转换
两个参数都是字符串，会按照字符串来比较，不做类型转换
两个参数都是整数，按照整数来比较，不做类型转换
十六进制的值和非数字做比较时，会被当做二进制串
有一个参数是 TIMESTAMP 或 DATETIME，并且另外一个参数是常量，常量会被转换为 timestamp
有一个参数是 decimal 类型，如果另外一个参数是 decimal 或者整数，会将整数转换为 decimal 后进行比较，如果另外一个参数是浮点数，则会把 decimal 转换为浮点数进行比较
所有其他情况下，两个参数都会被转换为浮点数再进行比较

再继续

mysql> select null+'1';
+----------+
| null+'1' |
+----------+
|     NULL |
+----------+
1 row in set (0.00 sec)

mysql> select 1.34+'a';
+----------+
| 1.34+'a' |
+----------+
|     1.34 |
+----------+
1 row in set, 1 warning (0.00 sec)

mysql> select 1.34+'a123';
+-------------+
| 1.34+'a123' |
+-------------+
|        1.34 |
+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> select 1.34+'123a';
+-------------+
| 1.34+'123a' |
+-------------+
|      124.34 |
+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> select 'a'+'b'='c';
+-------------+
| 'a'+'b'='c' |
+-------------+
|           1 |
+-------------+
1 row in set, 3 warnings (0.00 sec)

a+b=c结果为1，1在MySQL中可以理解为TRUE，因为'a'+'b'的结果为0，c也会隐式转化为0，因此比较其实是：0=0也就是true，也就是1.

mysql> select * from account where (name=' ' -0)||(' ' AND pwd='11');
+----+---------------+---------+----------+
| id | name          | pwd     | group_id |
+----+---------------+---------+----------+
|  1 | chujiu        | c123456 |        1 |
|  2 | xiaodongbanji | x78910  |        0 |
|  4 | cuiyang       | 123456  |        3 |
+----+---------------+---------+----------+
3 rows in set, 7 warnings (0.00 sec)

那这个呢？ ''-0 很显然 ''转为0 那么结果就是0  -的运算符优先级高 所以先算 结果name=0，至于后边的很显然就是 0 and pwd='11' 那么sql:
mysql> select * from account where (name=0) || (0 AND pwd='11');
+----+---------------+---------+----------+
| id | name          | pwd     | group_id |
+----+---------------+---------+----------+
|  1 | chujiu        | c123456 |        1 |
|  2 | xiaodongbanji | x78910  |        0 |
|  4 | cuiyang       | 123456  |        3 |
+----+---------------+---------+----------+
3 rows in set, 6 warnings (0.00 sec)

隐式转换大家都知道用不到索引，那么为什么呢

从数据结构上来讲，主要是因为每查找一个节点都要计算一次，按照官方所说，每次查找都会把字段类型转换为浮点型来比对，故而会走全表扫描
而组合索引比如有字段a,b index为（a_index,b_index）那么表现在数据结构中，关键字存储就是 aa , ab, bb, ba 这样，所以b,ba用不到索引