MySQL字符串转时间详解

一、MySQL字符串转时间格式

MySQL字符串转时间格式是开发中经常遇到的问题，转换的方式有多种，下面介绍两种常用的方式：

1、使用STR_TO_DATE函数可以将字符串转为时间格式：

SELECT STR_TO_DATE('2021-02-12 12:30:45', '%Y-%m-%d %H:%i:%s');

解释：

STR_TO_DATE函数需要两个参数，第一个参数是需要转换的字符串，第二个参数是字符串的格式。例如上面的例子中，字符串的格式是’%Y-%m-%d %H:%i:%s’，其对应的是年月日时分秒。

2、使用日期/时间函数可以将字符串转为时间格式：

SELECT DATE_FORMAT('2021-02-12 12:30:45', '%Y-%m-%d %H:%i:%s');

解释：

DATE_FORMAT函数需要两个参数，第一个参数是需要转换的字符串，第二个参数是需要转换成的格式。例如上面的例子中，需要将字符串转换成年月日时分秒的格式，所以第二个参数是’%Y-%m-%d %H:%i:%s’。

二、MySQL字符串转整数

将字符串转换为整数时需要使用CAST函数或者CONVERT函数。

1、使用CAST函数：

SELECT CAST('123' AS SIGNED);

解释：

CAST函数需要两个参数，第一个参数是需要转换的字符串，第二个参数是需要转换成什么类型。例如上面的例子中，需要将字符串转换成有符号的整数类型，所以第二个参数是SIGNED。

2、使用CONVERT函数：

SELECT CONVERT('123', SIGNED);

解释：

CONVERT函数需要两个参数，第一个参数是需要转换的字符串，第二个参数是需要转换成什么类型。例如上面的例子中，需要将字符串转换成有符号的整数类型，所以第二个参数是SIGNED。

三、MySQL字符串转时间格式字符串

将字符串转换成时间格式字符串时需要使用DATE_FORMAT函数。

SELECT DATE_FORMAT('2021-02-12 12:30:45', '%Y年%m月%d日 %H时%i分%s秒');

解释：

DATE_FORMAT函数需要两个参数，第一个参数是需要转换的字符串，第二个参数是需要转换成的格式。例如上面的例子中，需要将字符串转换成’2021年02月12日 12时30分45秒’的格式，所以第二个参数是’%Y年%m月%d日 %H时%i分%s秒’。

四、MySQL字符串转数组

将一个多列字符串转换成数组时可以使用SUBSTRING_INDEX函数和TRIM函数。

SELECT TRIM(BOTH ' ' FROM SUBSTRING_INDEX('a,b,c,d', ',', 1)) AS col_1,
TRIM(BOTH ' ' FROM SUBSTRING_INDEX(SUBSTRING_INDEX('a,b,c,d', ',', 2), ',', -1)) AS col_2,
TRIM(BOTH ' ' FROM SUBSTRING_INDEX(SUBSTRING_INDEX('a,b,c,d', ',', 3), ',', -1)) AS col_3,
TRIM(BOTH ' ' FROM SUBSTRING_INDEX(SUBSTRING_INDEX('a,b,c,d', ',', 4), ',', -1)) AS col_4;

解释：

SUBSTRING_INDEX函数需要三个参数，第一个参数是需要转换的字符串，第二个参数是分隔符，第三个参数是需要取出的位置。例如上面的例子中，需要将字符串’a,b,c,d’转换成数组，并且每个元素去掉空格。第一个元素可以使用SUBSTRING_INDEX(‘a,b,c,d’, ‘,’, 1)得到，取出的位置是1，也就是第一个。每个元素依次类推。

五、MySQL字符串转时间函数

MySQL提供了许多函数可以将字符串转换成时间类型，在这里介绍几个常用的函数。

1、NOW函数可以获取当前时间：

SELECT NOW();

2、UNIX_TIMESTAMP函数可以将时间类型转换为UNIX时间戳：

SELECT UNIX_TIMESTAMP('2021-02-12 12:30:45');

3、FROM_UNIXTIME函数可以将UNIX时间戳转换为时间类型：

SELECT FROM_UNIXTIME(1613082645);

六、MySQL字符串转时间格式hms

将字符串转换为时间格式HMS时需要使用TIME_FORMAT函数。

SELECT TIME_FORMAT('12:30:45', '%H时%i分%s秒');

解释：

TIME_FORMAT函数需要两个参数，第一个参数是需要转换的字符串，第二个参数是需要转换成的格式。例如上面的例子中，需要将字符串’12:30:45’转换成’12时30分45秒’的格式，所以第二个参数是’%H时%i分%s秒’。

七、MySQL字符串转数字

将字符串转换为数字时可以使用CAST函数或CONVERT函数。

1、使用CAST函数：

SELECT CAST('123' AS SIGNED);

解释：

CAST函数需要两个参数，第一个参数是需要转换的字符串，第二个参数是需要转换成什么类型。例如上面的例子中，需要将字符串转换成有符号的整数类型，所以第二个参数是SIGNED。

2、使用CONVERT函数：

SELECT CONVERT('123', SIGNED);

解释：

CONVERT函数需要两个参数，第一个参数是需要转换的字符串，第二个参数是需要转换成什么类型。例如上面的例子中，需要将字符串转换成有符号的整数类型，所以第二个参数是SIGNED。

八、MySQL时间函数

MySQL提供了很多有用的时间函数，下面介绍其中的一部分。

1、DATE_ADD函数用于加上一个时间间隔：

SELECT DATE_ADD('2021-02-12', INTERVAL 1 DAY);

2、DATE_SUB函数用于减去一个时间间隔：

SELECT DATE_SUB('2021-02-12', INTERVAL 1 DAY);

3、DATEDIFF函数用于计算两个日期之间的天数：

SELECT DATEDIFF('2021-02-12', '2021-02-10');

九、MySQL时间格式

MySQL支持多种时间格式，下面介绍一些常用的格式。

1、YYYY-MM-DD HH:MM:SS：

例如：2021-12-31 23:59:59

2、YYYY-MM-DD：

例如：2021-12-31

3、HH:MM:SS：

例如：23:59:59

4、UNIX_TIMESTAMP：

UNIX时间戳格式

十、MySQL转换时间格式

MySQL提供了许多函数可以将时间转换为指定的格式。

1、DATE_FORMAT函数可以将日期/时间格式化成指定的格式：

SELECT DATE_FORMAT('2021-02-12 12:30:45', '%Y年%m月%d日 %H时%i分%s秒');

2、STR_TO_DATE函数可以将字符串转换成日期/时间格式：

SELECT STR_TO_DATE('2021-02-12', '%Y-%m-%d');

3、CONVERT_TZ函数可以将日期/时间转换为指定时区的时间：

SELECT CONVERT_TZ('2021-02-12 12:30:45', '+00:00', '+08:00');

解释：

CONVERT_TZ函数需要三个参数，第一个参数是日期/时间，第二个参数是原始时区，第三个参数是目标时区。例如上面的例子中，需要将UTC时间转换为东八区时间，所以原始时区是+00:00，目标时区是+08:00。