您的位置:

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。