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

文章目录

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

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

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

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

Mysql 8.0 cast函数官网

一、语法

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

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

参数说明：

参数描述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-29SELECT CAST('2017-08-29'ASDATE);-- 2022-04-27 15:42:19SELECTNOW();-- 2022-04-27SELECT CAST(NOW()ASDATE);

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

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

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

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

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

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

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

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

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

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

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

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

2.1、复杂示例

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

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

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

数据准备

CREATETABLE`test1`(`id`intNOTNULLPRIMARYKEYAUTO_INCREMENTcomment'主键id',`file_name`varchar(10)DEFAULTNULLCOMMENT'文件名')ENGINE=InnoDBAUTO_INCREMENT=1;insertinto 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'%中文%'ORDERBY CAST(REGEXP_SUBSTR(file_name ,'\\d+')ASUNSIGNED);

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

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

三、cast与convert的区别

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