mysql - 将 string/varchar 转换为 MySQL 表之间的日期

Question

我有一个大型 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 是否可以直接做到这一点。

Answer 1

看看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 格式化您想要的格式)