MySQL前世今生_MySQL 隐式转换的前世今生

最新推荐文章于 2024-06-13 16:55:00 发布

沥氿

最新推荐文章于 2024-06-13 16:55:00 发布

阅读量210

点赞数

文章标签： MySQL前世今生

本文链接：https://blog.csdn.net/weixin_33553051/article/details/113300164

版权

前言：MySQL的隐式转换是什么样子的，什么时候会进行隐式转换，下面让我们来掀开MySQL隐式转换的面纱，下面我们通过一个例子来进行说明

隐式转换的几种情况：

1、当不同类型的字段一起使用时，会发生隐式转换

root@localhost:mysql.sock 16:18:23 [tom]>SELECT 1+'1';

+-------+

| 1+'1' |

+-------+

| 2 |

+-------+

1 row in set (0.00 sec)

root@localhost:mysql.sock 16:18:26 [tom]>SELECT CONCAT(2,' test');

+-------------------+

| CONCAT(2,' test') |

+-------------------+

| 2 test |

+-------------------+

1 row in set (0.00 sec)

或者使用函数显示或者隐式进行转换

root@localhost:mysql.sock 16:29:39 [tom]>SELECT 38.8, CAST(38.8 AS CHAR);

+------+--------------------+

| 38.8 | CAST(38.8 AS CHAR) |

+------+--------------------+

| 38.8 | 38.8 |

+------+--------------------+

1 row in set (0.00 sec)

root@localhost:mysql.sock 16:30:27 [tom]>SELECT 38.8, CONCAT(38.8);

+------+--------------+

| 38.8 | CONCAT(38.8) |

+------+--------------+

| 38.8 | 38.8 |

+------+--------------+

1 row in set (0.00 sec)

2、下面是一些会发生隐式转换的规则

1、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.

2、If both arguments in a comparison operation are strings, they are compared as strings.

3、If both arguments are integers, they are compared as integers.

4、Hexadecimal values are treated as binary strings if not compared to a number.

5、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.

6、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.

7、In all other cases, the arguments are compared as floating-point (real) numbers.

3、下面是字符串转换成数字进行比较的

root@localhost:mysql.sock 17:23:38 [tom]>SELECT 1 > '6x';

+----------+

| 1 > '6x' |

+----------+

| 0 |

+----------+

1 row in set, 1 warning (0.00 sec)

root@localhost:mysql.sock 17:23:41 [tom]>SELECT 7 > '6x';

+----------+

| 7 > '6x' |

+----------+

| 1 |

+----------+

1 row in set, 1 warning (0.01 sec)

root@localhost:mysql.sock 17:23:48 [tom]>SELECT 0 > 'x6';

+----------+

| 0 > 'x6' |

+----------+

| 0 |

+----------+

1 row in set, 1 warning (0.00 sec)

root@localhost:mysql.sock 17:23:55 [tom]>SELECT 0 = 'x6';

+----------+

| 0 = 'x6' |

+----------+

| 1 |

+----------+

1 row in set, 1 warning (0.00 sec)

4、如果在sql中对一个字符串和数字比较，mysql不会使用索引

root@localhost:mysql.sock 17:24:03 [tom]>explain select * from test where name = 1;;

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

1 row in set, 3 warnings (0.00 sec)

ERROR:

No query specified

root@localhost:mysql.sock 17:25:42 [tom]>show create table test;

+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table |

| test | CREATE TABLE `test` (

`id` int(11) DEFAULT NULL,

`age` int(11) DEFAULT NULL,

`name` varchar(4) NOT NULL DEFAULT 'tom',

KEY `name` (`name`),

KEY `id` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

1 row in set (0.00 sec)

The reason for this is that there are many different strings that may convert to the value1, such as‘1’,‘ 1’, or ‘1a’

5、浮点数的比较是近似值，可能导致结果不准确，可能会有取舍

root@localhost:mysql.sock 17:30:19 [tom]>SELECT '18015376320243459'+0.0;