首页 MySQL数据类型转换
文章
取消

MySQL数据类型转换

作者 小黑猫
发表于 2022-02-11 10 分钟阅读

关于MySQL的数据类型转换问题大概两年前就研究过了，只是那时候没有发博客的习惯，笔记记得也很简单。最近又遇到了这个问题，打开以往的笔记发现记得太简单了，自己看完了还是有好多疑问😭，故有了这篇文章。

参考资料：

MySQL :: MySQL 8.0 Reference Manual :: 12.11 Cast Functions and Operators

MySQL :: MySQL 8.0 Reference Manual :: 12.3 Type Conversion in Expression Evaluation

mysql 类型自动化转换问题 - whendream - 博客园

谈谈MySQL隐式类型转换 - 知乎

测试版本：

MySQL 8.0.18

显式转换

cast() 

1

CAST(expr AS type [ARRAY])

使用该函数可以进行强制类型转换。

type：

  • signed：signed bigint类型
  • char：varchar类型
  • float
  • double
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

# 将53.4转换为整数
mysql> select cast(53.4 as signed);
+----------------------+
| cast(53.4 as signed) |
+----------------------+
|                   53 |
+----------------------+
1 row in set (0.00 sec)

# 将53.6转换为整数
mysql> select cast(53.6 as signed);
+----------------------+
| cast(53.6 as signed) |
+----------------------+
|                   54 |
+----------------------+
1 row in set (0.00 sec)

# 将-53.4转换为整数
mysql> select cast(-53.4 as signed);
+-----------------------+
| cast(-53.4 as signed) |
+-----------------------+
|                   -53 |
+-----------------------+
1 row in set (0.00 sec)

# 将-53.6转换为整数
mysql> select cast(-53.6 as signed);
+-----------------------+
| cast(-53.6 as signed) |
+-----------------------+
|                   -54 |
+-----------------------+
1 row in set (0.00 sec)

# 将53.4转换为字符串
mysql> select cast(53.4 as char);
+--------------------+
| cast(53.4 as char) |
+--------------------+
| 53.4               |
+--------------------+
1 row in set (0.00 sec)

字符串转换为数字的规则

  • 不以数字开头的字符串会被转换为0

  • 以数字开头的字符串会被转换成开头的那些数字

此规则也适用于隐式转换。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

# 将字符串qwe456转换为整数
mysql> select cast('qwe456' as signed);
+--------------------------+
| cast('qwe456' as signed) |
+--------------------------+
|                        0 |
+--------------------------+
1 row in set, 1 warning (0.00 sec)

# 将字符串123qwe456转换为整数
mysql> select cast('123qwe456' as signed);
+-----------------------------+
| cast('123qwe456' as signed) |
+-----------------------------+
|                         123 |
+-----------------------------+
1 row in set, 1 warning (0.00 sec)

# 将字符串123.66qwe456转换为整数
mysql> select cast('123.66qwe456' as signed);
+--------------------------------+
| cast('123.66qwe456' as signed) |
+--------------------------------+
|                            123 |
+--------------------------------+
1 row in set, 1 warning (0.00 sec)

# 将字符串123.66qwe456转换为浮点数
mysql> select cast('123.66qwe456' as float);
+-------------------------------+
| cast('123.66qwe456' as float) |
+-------------------------------+
|                        123.66 |
+-------------------------------+
1 row in set, 1 warning (0.00 sec)

隐式转换

数字与字符串的相互转换

MySQL会在必要的时候将数字转换为字符串，或将字符串转换为数字。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

# 数字转换为字符串
mysql> select concat(2, 'test');
+-------------------+
| concat(2, 'test') |
+-------------------+
| 2test             |
+-------------------+
1 row in set (0.00 sec)

# 字符串转换为数字
mysql> select 1 + '1';
+---------+
| 1 + '1' |
+---------+
|       2 |
+---------+
1 row in set (0.00 sec)

# 字符串转换为数字
mysql> select 6 > '3qwe';
+------------+
| 6 > '3qwe' |
+------------+
|          1 |
+------------+
1 row in set, 1 warning (0.00 sec)

# 字符串转换为数字
mysql> select 0 = 'qwe345';
+--------------+
| 0 = 'qwe345' |
+--------------+
|            1 |
+--------------+
1 row in set, 1 warning (0.00 sec)

第一例，concat() 需要的参数为字符串，先将数字 2 转换为字符串 '2' ，再将 '2' 与 'test' 进行拼接得到 '2test' 。

第二例，加法运算，先将字符串 '1' 转换为数字 1 ，再做加法得到 2 。

第三例，比较运算，先将 '3qwe' 转换为数字 3 ，然后 6 > 3 为真，返回1。

第四例，比较运算，先将 'qwe345' 转换为数字 0 ，然后 0 = 0 为真，返回1。


下面是第五例的情景：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

# 有表如下
mysql> select * from test1;
+-------+-------+-------+
| no    | name  | grade |
+-------+-------+-------+
| 23    | admin |    50 |
| 24    | admin |    43 |
| 25    | qwe   |    54 |
| 25asd | qwe   |    54 |
+-------+-------+-------+
4 rows in set (0.00 sec)

# 表结构如下
mysql> desc test1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| no    | varchar(50) | NO   | PRI | NULL    |       |
| name  | varchar(50) | YES  |     | NULL    |       |
| grade | smallint(6) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

# 执行查询如下
mysql> select * from test1 where no = 25;
+-------+------+-------+
| no    | name | grade |
+-------+------+-------+
| 25    | qwe  |    54 |
| 25asd | qwe  |    54 |
+-------+------+-------+
2 rows in set, 1 warning (0.00 sec)

上面的情景中，执行 where no = 25 时，先看第一行，将字符串 '23' 转换为数字 23 ， 23 = 25 为假，不选该行；然后一样， 24 = 25 为假，不选该行；第三行，将字符串 '25' 转换为数字 25 ， 25 = 25 为真，选中该行；第四行，将字符串 25asd 转换为数字 25 ， 25 = 25 为真，选中该行；最后得到上面的查询结果。

总转换规则

下面的规则更详细精确，是来自官方文档的描述。

The following rules describe how conversion occurs for comparison operations:

  • 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. 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 (double-precision) numbers. For example, a comparison of string and numeric operands takes place as a comparison of floating-point numbers.


复制自 mysql 类型自动化转换问题 - whendream - 博客园 的翻译：

比较运算时的类型转换规则：

  • 如果一个或两个参数都是NULL，比较的结果是NULL，除了NULL安全的<=>相等比较运算符。对于NULL <=> NULL，结果为true。不需要转换。
  • 如果比较操作中的两个参数都是字符串，则将它们作为字符串进行比较。
  • 如果两个参数都是整数，则将它们作为整数进行比较。
  • 如果不与数字进行比较，则将十六进制值视为二进制字符串。
  • 如果其中一个参数是十进制值，则比较取决于另一个参数。 如果另一个参数是十进制或整数值，则将参数与十进制值进行比较，如果另一个参数是浮点值，则将参数与浮点值进行比较。
  • 如果其中一个参数是TIMESTAMP或DATETIME列，另一个参数是常量，则在执行比较之前将常量转换为时间戳。
  • 我的翻译：在所有其它情况下，参数作为浮点数（双精度）进行比较。例如，字符串和数字的比较就作为浮点数进行比较。
Tech, SQL
本文由作者按照 CC BY 4.0 进行授权
热门标签
gdb git kdb tls
文章内容

jekyll-chirpy-theme-github搭建个人博客

C-Cpp中的const关键字
热门标签
gdb git kdb tls