Mysql cast函数、cast用法、字符串转数字、字符串转日期、数据类型转换

CAST 函数是 SQL 中的一种类型转换函数，它用于将一个数据类型转换为另一个数据类型，这篇文章主要介绍了Mysql中Cast()函数的用法,需要的朋友可以参考下。

Mysql提供了两种将值转换成指定数据类型的函数

• CONVERT函数：用于将值转换为指定的数据类型或字符集。更多内容参考：Mysql convert函数
• CAST函数 ：用于将值转换为指定的数据类型。更多内容参考：Mysql cast函数

以下内容基于mysql8.0进行讲解。

Mysql 8.0 cast函数官网

一、语法

# 语法如下
CAST(expr AS type [ARRAY])

示例：将值转换为日期数据类型:
SELECT CAST('2021-08-29' AS DATE);

参数说明：

参数	描述
expr	必填。要转换的值、列名
type	必填。要转换成的数据类型。mysql版本不同，支持的数据类型不同，使用时需注意。

数据类型支持如下

类型	描述	备注
DATE	将 value 转化为 DATE 类型。 格式: “YYYY-MM-DD”
DATETIME	将 value 转化为 DATETIME 类型。格式: “YYYY-MM-DD HH:MM:SS”
DECIMAL[(M[,D])]	将 value 转化为 DECIMAL 类型。使用可选的 M 和 D 参数指定最大位数（M）和小数点（D）后的位数
TIME	将 value 转化为 TIME 类型。格式: “HH:MM:SS”
CHAR	将 value 转化为 CHAR 类型 (固定长度的字符串)
NCHAR	将 value 转化为 NCHAR (类似 CHAR, 但生成一个具有国家字符集的字符串)
SIGNED	将 value 转化为 SIGNED (有符号的 64 位整数)
UNSIGNED	将 value 转化为 UNSIGNED (无符号 64 位整数)
BINARY	将 value 转化为 BINARY (二进制字符串)
DOUBLE	将value转化为DOUBLE类型	Added in MySQL 8.0.17
FLOAT	将value转化为FLOAT类型 。	Added in MySQL 8.0.17

更多内容参考官网：Mysql 8.0 cast函数官网

二、示例

1.将值转换为DATE数据类型

-- 2017-08-29
SELECT CAST('2017-08-29' AS DATE); 
 
-- 2022-04-27 15:42:19
SELECT NOW();
-- 2022-04-27
SELECT CAST(NOW() AS DATE);

2.将值转换为DATETIME数据类型

-- 2022-04-27 00:00:00
SELECT CAST('2022-04-27' AS DATETIME); 	

3.将值转换为TIME数据类型

-- 14:06:10
SELECT CAST('14:06:10' AS TIME); 
-- 14:06:10
SELECT CAST('2022-04-27 14:06:10' AS TIME);

4.将值转换为CHAR数据类型

-- '150'
SELECT CAST(150 AS CHAR); 
 
-- 'Hello World437'
SELECT CONCAT('Hello World',CAST(437 AS CHAR));	

5.将值转换为SIGNED数据类型

-- 5
SELECT CAST('5.0' AS SIGNED);
-- 2
SELECT (1 + CAST('3' AS SIGNED))/2;
-- -5
SELECT CAST(5-10 AS SIGNED); 
-- 6
SELECT CAST(6.4 AS SIGNED);
-- -6
SELECT CAST(-6.4 AS SIGNED);
-- 7
SELECT CAST(6.5 AS SIGNED); 
-- -7
SELECT CAST(-6.5 AS SIGNED);	

6.将值转换为UNSIGNED数据类型

-- 5
SELECT CAST('5.0' AS UNSIGNED);
-- 6
SELECT CAST(6.4 AS UNSIGNED);
-- 0
SELECT CAST(-6.4 AS UNSIGNED);
-- 7
SELECT CAST(6.5 AS UNSIGNED); 
-- 0
SELECT CAST(-6.5 AS UNSIGNED);

7.将值转换为DECIMAL数据类型

-- 9
SELECT CAST('9.0' AS DECIMAL);
 
-- DECIMAL(数值精度，小数点保留长度)
-- DECIMAL(10,2)可以存储最多具有8位整数和2位小数的数字
-- 精度与小数位数分别为10与2
-- 精度是总的数字位数，包括小数点左边和右边位数的总和
-- 小数位数是小数点右边的位数
-- 9.50
SELECT CAST('9.5' AS DECIMAL(10,2));
-- 99999999.99
SELECT CAST('1234567890.123' AS DECIMAL(10,2));
 
-- 220.232
SELECT CAST('220.23211231' AS DECIMAL(10, 3));
-- 220.232
SELECT CAST(220.23211231 AS DECIMAL(10, 3));

2.1、复杂示例

1.如下，按文件名中的数字进行排序

让我们先看几个 file_name的示例值：

• 中文_1.mp4
• 中文_12.mp4
• 中文_2.mp4
• 中文_10.mp4

数据准备

CREATE TABLE `test1` (
  `id` int NOT NULL PRIMARY KEY AUTO_INCREMENT comment '主键id',
  `file_name` varchar(10)  DEFAULT NULL COMMENT '文件名'
) ENGINE=InnoDB AUTO_INCREMENT=1;

insert into test1(file_name) values
('中文_1.txt'),
('中文_2.txt'),
('中文_10.txt'),
('中文_12.txt');

使用 REGEXP_SUBSTR 函数提取并排序
在MySQL 8.0及以上版本中，我们可以使用 REGEXP_SUBSTR() 函数来提取字符串中的数字部分。这个函数允许我们使用正则表达式来指定我们想要匹配的模式。在这个例子中，我们使用正则表达式 \d+ 来匹配一个或多个数字。

以下是完整的SQL查询，用于实现按数字排序：

SELECT *
FROM test1
WHERE file_name LIKE '%中文%'
ORDER BY CAST(REGEXP_SUBSTR(file_name , '\\d+') AS UNSIGNED);

这条SQL语句做了以下几件事：

• WHERE original_name LIKE '%中文%'：筛选出所有文件名包含“中文”的记录。
• REGEXP_SUBSTR(original_name, '\\d+')：从 original_name 中提取第一组连续的数字。
• CAST(... AS UNSIGNED)：将提取出的字符串转换成无符号整数，以便按数字进行排序。

三、cast与convert的区别

CAST() 和 CONVERT() 都可以更改 MySQL 中的数据类型。主要区别在于 CONVERT() 还可以将数据的字符集转换为另一个字符集。CAST() 就不能用于更改字符集。