MySQL 类型转换函数

最新推荐文章于 2024-08-01 08:23:29 发布

韩未零

最新推荐文章于 2024-08-01 08:23:29 发布

阅读量411

点赞数 1

分类专栏： MySQL 文章标签： mysql 数据库 linux

本文链接：https://blog.csdn.net/qq_60271706/article/details/132188813

版权

MySQL 专栏收录该内容

23 篇文章 0 订阅

订阅专栏

类型转换

隐式类型转换

两个值进行运算或者比较，首先要求数据类型必须一致。如果发现有类型不一致的情况，MySQL就尝试做隐式类型转换。例如，把字符串转成数字，或者相反：

SELECT  1+‘1’;  -- 字符串1转成数字
SELECT  concat(2,' test');  -- 数字2转成字符串

显式类型转换

通过调用类型转换函数来实现

cast函数

CAST(expr AS type)
将任意类型的表达式expr转换成指定类型type的值。type可以是以下任意类型之一：

BINARY[(N)] ：二进制字符串，转换后长度小于N个字节（ 不足长度N则尾部补上0x00 ）
CHAR[(N)] ：字符串，转换后长度小于N个字符
DATE ：日期
DATETIME ：日期时间
DECIMAL[(M[,N])] ：浮点数，M为数字总位数(包括整数部分和小数部分)，N为小数点后的位数
SIGNED [INTEGER] ：有符号整数
TIME ：时间
UNSIGNED [INTEGER] ：无符号整数

字符串转成数字

转成decimal时，从头扫描字符串直到第一个不为数字的字符为至。对截断的那一位进行四舍五入
1）默认不限定M,N，转换为整数，按照小数点后第一位进行四舍五入：

root@hunan 15:10  mysql>select cast('28.82abc' as decimal);
+-----------------------------+
| cast('28.82abc' as decimal) |
+-----------------------------+
|                          29 |
+-----------------------------+
1 row in set (0.00 sec)

2）限定M不限定N，转换为整数（M尽量大一些）：

root@hunan 15:11  mysql>select cast('28.82abc' as decimal(4));
+--------------------------------+
| cast('28.82abc' as decimal(4)) |
+--------------------------------+
|                             29 |
+--------------------------------+
1 row in set (0.00 sec)

3）如果M的长度小于实际的数字位数时，会转换成设定位数的最大值。下面这个转换中M为1，即显示不了29这个两位数，就会显示1位数中最大的9。

root@hunan 15:12  mysql>select cast('28.82abc' as decimal(1));
+--------------------------------+
| cast('28.82abc' as decimal(1)) |
+--------------------------------+
|                              9 |
+--------------------------------+
1 row in set, 1 warning (0.00 sec)

M、N都限定，必须先满足小数点后的N位小数。下例中，先满足了1位小数，再取1位整数：

root@hunan 15:12  mysql>SELECT  cast('28.82abc'  AS  DECIMAL(2,1));
+------------------------------------+
| cast('28.82abc'  AS  DECIMAL(2,1)) |
+------------------------------------+
|                                9.9 |
+------------------------------------+
1 row in set, 1 warning (0.00 sec)

先满足2位小数，不取整数部分：

root@hunan 15:13  mysql>SELECT  cast('28.82abc'  AS  DECIMAL(2,2));
+------------------------------------+
| cast('28.82abc'  AS  DECIMAL(2,2)) |
+------------------------------------+
|                               0.99 |
+------------------------------------+
1 row in set, 1 warning (0.00 sec)

root@hunan 15:13  mysql>SELECT  cast('28.82abc' AS UNSIGNED);
+------------------------------+
| cast('28.82abc' AS UNSIGNED) |
+------------------------------+
|                           28 |
+------------------------------+
1 row in set, 1 warning (0.00 sec)

root@hunan 15:13  mysql>SELECT  cast('-28abc'  AS  SIGNED);
+----------------------------+
| cast('-28abc'  AS  SIGNED) |
+----------------------------+
|                        -28 |
+----------------------------+
1 row in set, 1 warning (0.00 sec)

字符串到日期

root@hunan 15:14  mysql>SELECT  cast('2007-12-25' AS DATETIME);
+--------------------------------+
| cast('2007-12-25' AS DATETIME) |
+--------------------------------+
| 2007-12-25 00:00:00            |
+--------------------------------+
1 row in set (0.00 sec)

root@hunan 15:15  mysql>SELECT  cast('2007-12-25' AS DATE);
+----------------------------+
| cast('2007-12-25' AS DATE) |
+----------------------------+
| 2007-12-25                 |
+----------------------------+
1 row in set (0.00 sec)

root@hunan 15:15  mysql>SELECT  cast('20:20:20' AS TIME);
+--------------------------+
| cast('20:20:20' AS TIME) |
+--------------------------+
| 20:20:20                 |
+--------------------------+
1 row in set (0.00 sec)

日期格式必须是‘YYYY-MM-DD’

root@hunan 15:15  mysql>SELECT  cast('25-12-2007' AS DATETIME);
+--------------------------------+
| cast('25-12-2007' AS DATETIME) |
+--------------------------------+
| NULL                           |
+--------------------------------+
1 row in set, 1 warning (0.00 sec)

root@hunan 15:15  mysql>SELECT cast('2007-may-25' AS DATETIME);
+---------------------------------+
| cast('2007-may-25' AS DATETIME) |
+---------------------------------+
| NULL                            |
+---------------------------------+
1 row in set, 1 warning (0.00 sec)

数字到字符串

root@hunan 15:15  mysql>SELECT  cast(123  AS  CHAR);
+---------------------+
| cast(123  AS  CHAR) |
+---------------------+
| 123                 |
+---------------------+
1 row in set (0.00 sec)

root@hunan 15:15  mysql>SELECT  cast(123  AS  BINARY);
+-----------------------+
| cast(123  AS  BINARY) |
+-----------------------+
| 123                   |
+-----------------------+
1 row in set (0.00 sec)

日期到字符串

root@hunan 15:15  mysql>SELECT  cast(now()  AS  CHAR);
+-----------------------+
| cast(now()  AS  CHAR) |
+-----------------------+
| 2023-08-09 15:16:34   |
+-----------------------+
1 row in set (0.00 sec)

字符串到二进制字符串（binary string）

二进制字符串：二进制字符串被视为一个连续的字节序列，与字符集无关。非二进制字符串（即我们通常所说的字符串）被视为一个连续排列的字符序列，与字符集有关。所谓与字符集无关，是指与MySQL自己的字符集无关，而是按照操作系统的字符集把字符串转换成字节进行存储
两种字符串的比较方式：二进制字符串的比较方式是一个字节一个字节进行的，比较的依据是两个字节的二进制值。因为同一个字母的大小写的数值编码是不一样的，因此它是区分大小写的。另外，由于它和字符集无关，因此也就没有大写和小写字母一说。

非二进制字符串的比较方式是一个字符一个字符进行的，比较的依据是两个字符在字符集中的先后顺序。根据使用的校对不同，可以进行区分大小写的比较和不区分大小写的比较
使用CAST(str AS BINARY)将字符串转换成二进制字符串时，通常使用它的快捷方式写法： BINARY str
查看当前字符集和校对规则设置

root@hunan 15:16  mysql>SHOW VARIABLES LIKE 'collation_%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_general_ci |
| collation_server     | utf8_general_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)

在比较表达式中，binary影响后面所有的字符串，并且不会忽略字符串的尾部空格

root@hunan 15:17  mysql>SELECT 'a' = 'A' ,BINARY 'a' = 'A';
+-----------+------------------+
| 'a' = 'A' | BINARY 'a' = 'A' |
+-----------+------------------+
|         1 |                0 |
+-----------+------------------+
1 row in set (0.00 sec)

root@hunan 15:17  mysql>SELECT 'a' = 'a ',BINARY 'a' = 'a ';
+------------+-------------------+
| 'a' = 'a ' | BINARY 'a' = 'a ' |
+------------+-------------------+
|          1 |                 0 |
+------------+-------------------+
1 row in set (0.00 sec)

CONVERT函数

CONVERT(expr, type), CONVERT(expr USING sharset_name)
convert函数的作用和cast函数几乎相同，但是它可以把字符串从一种字符集转换成另一种字符集。下例将字符串’abc’从默认的字符集转换成utf8字符集。

root@hunan 15:17  mysql>SELECT  convert('abc'  USING  utf8);
+-----------------------------+
| convert('abc'  USING  utf8) |
+-----------------------------+
| abc                         |
+-----------------------------+
1 row in set (0.00 sec)

root@hunan 15:18  mysql>SELECT convert('你好' USING  gbk);
+------------------------------+
| convert('你好' USING  gbk)   |
+------------------------------+
| 你好                         |
+------------------------------+
1 row in set (0.00 sec)

如果目标字符集不能表示该字符，则返回乱码

root@hunan 15:18  mysql>SELECT convert('你好' USING latin1);
+--------------------------------+
| convert('你好' USING latin1)   |
+--------------------------------+
| ??                             |
+--------------------------------+
1 row in set (0.00 sec)

日期字符串转换函数

DATE_FORMAT(date, format)
将日期date按照给定的模式format转换成字符串。

TIME_FORMAT(date, format)
format中只可使用时、分、秒和微秒模式元素

root@hunan 15:19  mysql>SELECT  time_format('22:23:01', '%H.%i.%s');
+-------------------------------------+
| time_format('22:23:01', '%H.%i.%s') |
+-------------------------------------+
| 22.23.01                            |
+-------------------------------------+
1 row in set (0.00 sec)

STR_TO_DATE(str, format)
将字符串str以指定的模式format转换成日期。format中可以包含模式元素和字面量，字面量必须匹配str中的字面量：

root@hunan 15:21  mysql>SELECT str_to_date('01,5,2013','%d,%m,%Y');
+-------------------------------------+
| str_to_date('01,5,2013','%d,%m,%Y') |
+-------------------------------------+
| 2013-05-01                          |
+-------------------------------------+
1 row in set (0.00 sec)

root@hunan 15:21  mysql>SELECT str_to_date('May 1, 2013','%M %d,%Y');
+---------------------------------------+
| str_to_date('May 1, 2013','%M %d,%Y') |
+---------------------------------------+
| 2013-05-01                            |
+---------------------------------------+
1 row in set (0.00 sec)

以下在会话变量@@sql_mode设置中包含no_zero_date和no_zero_in_date时转换失败，没有包含这些设置时转换成功。

root@hunan 15:21  mysql>SELECT str_to_date('a09:30:17','a%h:%i:%s');
+--------------------------------------+
| str_to_date('a09:30:17','a%h:%i:%s') |
+--------------------------------------+
| NULL                                 |
+--------------------------------------+
1 row in set, 1 warning (0.00 sec)

root@hunan 15:22  mysql>SELECT str_to_date('09:30:17a','%h:%i:%s');
+-------------------------------------+
| str_to_date('09:30:17a','%h:%i:%s') |
+-------------------------------------+
| NULL                                |
+-------------------------------------+
1 row in set, 2 warnings (0.00 sec)

NULL值处理函数

IFNULL(expr1,expr2)
如果第一个参数 expr1 不为NULL则直接返回它，否则返回第二个参数 expr2。返回值是数字或者字符串。它相当于oracle中的nvl函数

root@hunan 15:22  mysql>SELECT  ifnull(1,0);
+-------------+
| ifnull(1,0) |
+-------------+
|           1 |
+-------------+
1 row in set (0.01 sec)

root@hunan 15:23  mysql>SELECT  ifnull(NULL,10);
+-----------------+
| ifnull(NULL,10) |
+-----------------+
|              10 |
+-----------------+
1 row in set (0.00 sec)

root@hunan 15:23  mysql>SELECT  ifnull(1/0,'yes');
+-------------------+
| ifnull(1/0,'yes') |
+-------------------+
| yes               |
+-------------------+
1 row in set, 1 warning (0.00 sec)

该函数常用在算术表达式计算和组函数中，用来对null值进行转换处理，非常有用

NULLIF(expr1,expr2)
如果两个参数相等则返回NULL，否则返回第一个参数的值expr1


root@hunan 15:23  mysql>SELECT nullif(1,1),nullif(1,2);
+-------------+-------------+
| nullif(1,1) | nullif(1,2) |
+-------------+-------------+
|        NULL |           1 |
+-------------+-------------+
1 row in set (0.00 sec)

IF(expr1,expr2,expr3)
如果第一个参数expr1为TRUE (expr1 <> 0 and expr1 <> NULL) ，则返回第二个参数expr2; 否则返回第三个参数expr3。返回值是数字或者字符串

root@hunan 15:23  mysql>SELECT  if(1>2,2,3), if(1<2,'yes','no');
+-------------+--------------------+
| if(1>2,2,3) | if(1<2,'yes','no') |
+-------------+--------------------+
|           3 | yes                |
+-------------+--------------------+
1 row in set (0.01 sec)

root@hunan 15:24  mysql>SELECT  if(NULL,2,3);
+--------------+
| if(NULL,2,3) |
+--------------+
|            3 |
+--------------+
1 row in set (0.00 sec)

函数的嵌套

函数嵌套：直接把一个函数调用用作其它函数的参数
单行函数可以嵌套到任意深度
在函数嵌套的情况下，先执行最里面的函数

对于姓名以大写B开头的球员，得到其编号和名字的首字母，随后跟着一个小数点和姓

root@TENNIS 15:26  mysql>SELECT playerno, concat(left(initials,1),'. ',NAME) AS full_name FROM  PLAYERS WHERE  left(NAME,1) = 'B';
+----------+-----------+
| playerno | full_name |
+----------+-----------+
|       39 | D. Bishop |
|       44 | E. Baker  |
+----------+-----------+
2 rows in set (0.00 sec)