MySQL中隐式转换要领【MySQL教程】,MySQL,方法,隐式

隐式转化划定规矩

官方文档中关于隐式转化的划定规矩是以下形貌的：

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 转换为浮点数举行比较
一切其他状况下，两个参数都会被转换为浮点数再举行比较

题目形貌

where 前提语句里，字段属性和赋给的前提，当数据范例不一样，这时候是没法直接比较的，须要举行一致转换
默许转换划定规矩是：

差别范例全都转换为浮点型（下文都说成整型了，一个意义）
假如字段是字符，前提是整型，那末会把表中字段全都转换为整型（也就是上面图中的题目，下面有细致诠释）

转换总结

字符转整型

字符开首的一概为0
数字开首的，直接截取到第一个不是字符的位置

时候范例转换

根据字符串举行截取
23:12:13 -> 2023-12-13（这个后文有议论）
关于不符合的时候值，如10:12:32等，会变成 0000-00-00 或为空

cast函数只能转datetime，不能转timestamp
假如根据timestamp来明白，因为timestamp是有局限的('1970-01-01 00:00:01.000000' to'2038-01-19 03:14:07.999999')，所以只能是2023年，而不能是1923年

直接截取time字段

直接截取date字段

无意义，直接为 00:00:00

追加 00:00:00

date 转 datetime 或许 timestamp
date 转 time
datetime 或许 timestamp 转 date
datetime 或许 timestamp 转 time
time 转 datetime 或许 timestamp
time和datetime转换为数字时，会变成双精度，加上ms（版本差别不一样）

案例剖析

表构造，name字段有索引

-- 注重name字段是有索引的CREATE TABLE `t3` (  `id` int(11) NOT NULL,  `c1` int(11) NOT NULL,  `name` varchar(100) NOT NULL DEFAULT 'fajlfjalfka',  KEY `name` (`name`),  KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.00 sec)

-- 模仿线上一个隐式转换带来的全表扫面慢查询-- 发作隐式转换
xxxx.test> select * from t3 where name = 0;
+----+----+-------------+
| id | c1 | name        |
+----+----+-------------+
|  1 |  2 | fajlfjalfka |
|  2 |  0 | fajlfjalfka |
|  1 |  2 | fajlfjalfka |
|  2 |  0 | fajlfjalfka |
+----+----+-------------+
4 rows in set, 4 warnings (0.00 sec)-- 上述SQL执行计划是全表扫描，扫描后，字符转整型，都是0，婚配上了前提，悉数返回
xxxx.test> desc select * from t3 where name = 0;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t3    | ALL  | name          | NULL | NULL    | NULL |    4 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)-- 加上单引号后，是走name索引的，非全表扫描
xxxx.test> desc select * from t3 where name = '0';
+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | t3    | ref  | name          | name | 102     | const |    1 | Using index condition |
+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)-- 走索引，没返回
xxxx.test>  select * from t3 where name = '1';
Empty set (0.00 sec)

诠释

假如前提写0或许1，会举行全表扫面，须要把一切的name字段由字符全都转换为整型，再和0或许1去比较。因为都是字母开首的字符，会全都转为为0，返回的效果就是一切行。
那有人问了，为何不把前提里的 0 自动改成 '0' ？见下文。

转换举例

-- 字符开首，直接是0
xxxx.test> select cast('a1' as unsigned int) as test ;
+------+
| test |
+------+
|    0 |
+------+
1 row in set, 1 warning (0.00 sec)

xxxx.test> show warnings;
+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning | 1292 | Truncated incorrect INTEGER value: 'a1' |
+---------+------+-----------------------------------------+
1 row in set (0.00 sec)-- 开首不是字符，一直截取到第一个不是字符的位置
xxxx.test> select cast('1a1' as unsigned int) as test ; 
+------+
| test |
+------+
|    1 |
+------+
1 row in set, 1 warning (0.00 sec)

xxxx.test> select cast('123a1' as unsigned int) as test ;
+------+
| test |
+------+
|  123 |
+------+
1 row in set, 1 warning (0.00 sec)-- 直接根据字符截取，补上了20（不能补19）
xxxx.test> select cast('23:12:13' as datetime) as test ;
+---------------------+
| test                |
+---------------------+
| 2023-12-13 00:00:00 |
+---------------------+
1 row in set (0.00 sec)-- 为何不能转换为timestamp，没搞清楚，官方文档给的转换范例里没有timestamp。假如是如许的话，上面的datetime就不好诠释为什不是1923了。岂非是检测了当前的体系时候？
xxxx.test> select cast('23:12:13' as timestamp) as test ;    
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'timestamp) as test' at line 1-- 这个时候没法转换成datetime
xxxx.test> select cast('10:12:32' as datetime) as test ;         
+------+
| test |
+------+
| NULL |
+------+
1 row in set, 1 warning (0.00 sec)

xxxx.test> show warnings ;
+---------+------+--------------------------------------+
| Level   | Code | Message                              |
+---------+------+--------------------------------------+
| Warning | 1292 | Incorrect datetime value: '10:12:32' |
+---------+------+--------------------------------------+
1 row in set (0.00 sec)-- 5.5版本下，时候转字符，会增添ms
xxxx.(none)> select version();
+------------+
| version()  |
+------------+
| 5.5.31-log |
+------------+
1 row in set (0.00 sec)

xxxx.(none)> select CURTIME(), CURTIME()+0, NOW(), NOW()+0 ;
+-----------+---------------+---------------------+-----------------------+
| CURTIME() | CURTIME()+0   | NOW()               | NOW()+0               |
+-----------+---------------+---------------------+-----------------------+
| 15:40:01  | 154001.000000 | 2016-05-06 15:40:01 | 20160506154001.000000 |
+-----------+---------------+---------------------+-----------------------+
1 row in set (0.00 sec)-- 5.6 不会
xxxx.test> select version();
+------------+
| version()  |
+------------+
| 5.6.24-log |
+------------+
1 row in set (0.00 sec)

xxxx.test> select CURTIME(), CURTIME()+0, NOW(), NOW()+0 ;
+-----------+-------------+---------------------+----------------+
| CURTIME() | CURTIME()+0 | NOW()               | NOW()+0        |
+-----------+-------------+---------------------+----------------+
| 15:40:55  |      154055 | 2016-05-06 15:40:55 | 20160506154055 |
+-----------+-------------+---------------------+----------------+
1 row in set (0.00 sec)

为何不把 `where name = 0` 中的 `0` 转换为 `'0'` ？

假如是数字往字符去转换，如 0 转'0'，如许查询出来的效果只能是字段即是 '0'，而实际上，内外的数据，如'a0'，'00'，这实在都是用户想要的0，毕竟是用户指定了数字0，所以MySQL照样以用户发出的需求为准，不然，'00'这些都不会返回给用户。

总结

有了上面的内容，开首的题目是能够诠释了。
上图的例子，是否是能够用来绕过身份验证？

补充

-- 上面遗留的题目，跟体系时候并没有关联。疑心虽然指定的是datetime，然则内部照样根据timestamp去做的。
mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 1999-08-03 14:16:50 |
+---------------------+
1 row in set (0.00 sec)

mysql> select cast('23:12:13' as datetime) as test ;
+---------------------+
| test                |
+---------------------+
| 2023-12-13 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

以上就是MySQL中隐式转换要领的细致内容，更多请关注ki4网别的相干文章！

正文

MySQL中隐式转换要领【MySQL教程】,MySQL,方法,隐式

题目形貌

转换总结

案例剖析

诠释

转换举例

为何不把 `where name = 0` 中的 `0` 转换为 `'0'` ？

总结

补充

相关阅读

如何修改mysql的默认时区_MySQL教程,mysql,时区

MySQL如何使用授权命令grant_MySQL教程,MySQL,grant

mysql数据库如何创建数据表_MySQL教程,mysql,数据表

MySQL 连接查询超级详解_MySQL教程,MySQL,连接查询

题目形貌

转换总结

案例剖析

诠释

转换举例

为何不把 where name = 0 中的 0 转换为 '0' ？

总结

补充

相关阅读

如何修改mysql的默认时区_MySQL教程,mysql,时区

MySQL如何使用授权命令grant_MySQL教程,MySQL,grant

mysql数据库如何创建数据表_MySQL教程,mysql,数据表

MySQL 连接查询超级详解_MySQL教程,MySQL,连接查询

为何不把 `where name = 0` 中的 `0` 转换为 `'0'` ？