我有一个大型的PHP项目,我一直在为我自己的公司工作。
我有一个MySQL表,大约有2600个条目,我想"修复"日期。我当前的表有3个包含字符串日期的VARCHAR列。我想将这些转换成一个重复的表,但在此过程中将它们更改为DATE类型。
一旦我完成了转换并且一切看起来都很好,我将把新表复制到旧数据库中,并且只允许日期选择器在向前的基础上输入这些日期。
大多数现有字符串日期的格式为mm-dd-yyyy
,例如:05-03-2013
。部分为mm/dd/yyyy
格式,例如:05/03/2013
,少数为MONTH DAY, YEAR格式,例如:March 26th, 2013
.
我最终想要的是yyyy-mm-dd
格式,例如:2013-05-02
.
我认为我可以使用PHP做到这一点,通过strtotime()和date()传递每个字符串日期,但想知道MySQL是否可以直接做到这一点。
看一下STR_TO_DATE函数:
UPDATE yourtable
SET
new_column = STR_TO_DATE(varchar_date, '%m-%d-%Y')
WHERE
STR_TO_DATE(varchar_date, '%m-%d-%Y') IS NOT NULL
然后是相同的查询,日期格式为'%m/%d/%Y'
,然后是'%M %D, %Y'
。如果格式不匹配,STR_TO_DATE将返回NULL,并且不会更新行。
如果您希望将现有数据插入到新表中,您可以使用如下命令:
INSERT INTO new_table (ID, col1, col2, new_column)
SELECT
ID, col1, col2,
COALESCE(STR_TO_DATE(varchar_date, '%m-%d-%Y'),
STR_TO_DATE(varchar_date, '%m/%d/%Y'),
STR_TO_DATE(varchar_date, '%M %D, %Y'))
FROM oldtable
(new_column是一个日期列,您可以使用PHP或DATE_FORMAT格式化)
Try
SELECT STR_TO_DATE('May 1, 2013','%M %d,%Y');
参考:https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html function_str-to-date