极客笔记MySQL中如何转换不规范格式的日期？
在MySQL中，日期通常使用规范的格式进行存储和比较。不过，有时候我们在数据中会遇到一些不规范的日期格式，例如”2020/1/1″或”Jan 1, 2020″等。如果直接在MySQL中进行比较或排序，这些日期会被当做字符串处理，结果并不准确。因此，本文将介绍如何将不规范的日期格式转换成规范的日期格式。
阅读更多：MySQL 教程
1. 使用STR_TO_DATE函数
MySQL提供了STR_TO_DATE函数，可以将字符串按指定格式转换为日期。
SELECT STR_TO_DATE('2020/1/1', '%Y/%m/%d');
上面的例子中，将字符串”2020/1/1″转换成日期格式”%Y/%m/%d”，即年份、月份、日期都用两位数表示。执行以上SQL查询，输出结果如下：
2020-01-01
注意，STR_TO_DATE函数中的格式参数必须和字符串中的日期格式完全匹配，否则转换会失败。以下是一些常见的日期格式转换示例：
SELECT STR_TO_DATE('Jan 1, 2020', '%M %d, %Y'); -- 将"Jan 1, 2020"转换为"%M %d, %Y"格式
SELECT STR_TO_DATE('2020年1月1日', '%Y年%m月%d日'); -- 将"2020年1月1日"转换为"%Y年%m月%d日"格式
SELECT STR_TO_DATE('20200101', '%Y%m%d'); -- 将"20200101"转换为"%Y%m%d"格式
2. 使用DATE_FORMAT函数
如果不想直接将字符串转换成日期，而是将日期格式化成需要的格式，可以使用DATE_FORMAT函数。
SELECT DATE_FORMAT('2020/1/1', '%Y年%m月%d日');
以上SQL查询将日期”2020/1/1″格式化成”%Y年%m月%d日”的形式，即输出”2020年01月01日”。
3. 使用UNIX_TIMESTAMP函数
如果日期格式中包含时分秒等信息，可以使用UNIX_TIMESTAMP函数将日期转换为Unix时间戳，再将时间戳转换成标准格式。
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP('2020-01-01 12:34:56'), '%Y-%m-%d %H:%i:%s');
以上SQL查询将日期字符串”2020-01-01 12:34:56″转换为Unix时间戳，再将时间戳转换为”%Y-%m-%d %H:%i:%s”格式的日期，输出结果为”2020-01-01 12:34:56″。
4. 使用CASE语句转换多种格式
如果不确定字符串中的日期格式，可以使用CASE语句将不同的格式转换为规范的日期格式。
SELECT CASE
WHEN date like '%/%/%'
THEN STR_TO_DATE(date, '%Y/%m/%d')
WHEN date like '%, %M %d, %Y'
THEN STR_TO_DATE(date, '%b %d, %Y')
ELSE STR_TO_DATE(date, '%Y-%m-%d')
END AS proper_date
FROM dates;
以上SQL查询从数据表dates中获取字符串格式的日期，并根据不同的格式转换成规范的日期格式。若字符串格式为”%Y/%m/%d”，则使用”%Y/%m/%d”的格式转换；若字符串格式为”%b %d, %Y”，则使用”%b %d, %Y”的格式转换；否则使用”%Y-%m-%d”的格式转换。输出结果中的proper_date列即为转换后的日期。
结论
总之，在MySQL中，将不规范格式的日期转换成规范的日期格式，可以使用STR_TO_DATE函数、DATE_FORMAT函数、UNIX_TIMESTAMP函数或CASE语句。需要根据实际情况选择合适的方法。在处理日期的过程中，一定要注意格式参数和字符串格式的匹配，以免数据转换出错。同时，在设计数据库时，也应该尽量避免使用不规范的日期格式，以保证数据的准确性和可读性。