mysql - 我愚蠢地将日期作为 VARCHAR 进行了一行，我仍然可以根据它进行基于日期的选择吗？

Question

所以我有一个大表，如果不破坏我的 PHP 应用程序就无法对其进行更改。愚蠢的(是的，我知道)，我将开始日期和结束日期设置为 VARCHAR，例如，数据存储为“03/04/2013”​​。

现在我需要能够搜索以查看哪些行当前处于“事件”状态，这意味着哪些行的开始日期早于今天，结束日期晚于今天。

使用 SQL 查询完全可以做到这一点吗？

Answer 1

在不破坏应用程序的情况下将 VARCHAR 列迁移到 DATE 的行动计划:

1. 创建新的索引 DATE 列并用 VARCHAR 列中的相应值填充它们:

   -- new column
   ALTER TABLE MY_TABLE ADD `NEW_DATE_COLUMN` DATE;    
   -- index
   CREATE INDEX `MY_TABLE_NEW_DATE_IDX` ON MY_TABLE(`NEW_DATE_COLUMN`);
   -- initial values
   UPDATE MY_TABLE 
   SET `NEW_DATE_COLUMN` = STR_TO_DATE(`VARCHAR_DATE`, '%d/%m/%Y')
   WHERE `NEW_DATE_COLUMN` IS NULL;
   

2. 创建插入/更新触发器以将您的 VARCHAR 列转换为 DATE 并使用它们各自的值更新您的新 DATE 列:

   -- triggers
   DELIMITER //
   CREATE TRIGGER `MY_TABLE_VARCHAR_DATE_BI` BEFORE INSERT ON MY_TABLE
     FOR EACH ROW
     BEGIN
       IF NEW.`NEW_DATE_COLUMN` IS NULL AND NEW.`VARCHAR_DATE` IS NOT NULL THEN
         SET NEW.NEW_DATE_COLUMN = STR_TO_DATE(NEW.`VARCHAR_DATE`, '%d/%m/%Y');
       END IF;
     END;
   //
   CREATE TRIGGER `MY_TABLE_VARCHAR_DATE_BU` BEFORE UPDATE ON MY_TABLE
     FOR EACH ROW
     BEGIN
       IF NEW.`NEW_DATE_COLUMN` IS NULL AND NEW.`VARCHAR_DATE` IS NOT NULL THEN
         SET NEW.NEW_DATE_COLUMN = STR_TO_DATE(NEW.`VARCHAR_DATE`, '%d/%m/%Y');
       END IF;
     END;
   //
   DELIMITER;
   

3. 在查询中使用 DATE 列:

   SELECT * FROM MY_TABLE
   WHERE `NEW_DATE_COLUMN` BETWEEN 
   CURRENT_DATE AND DATE_ADD(CURRENT_DATE, INTERVAL 1 DAY);
   

4. 花点时间更新您的应用程序，以直接使用原始 VARCHAR 列，同时不会破坏任何内容。

5. 完成后删除触发器和 VARCHAR 列:

   DROP TRIGGER `MY_TABLE_VARCHAR_DATE_BI`;
   DROP TRIGGER `MY_TABLE_VARCHAR_DATE_BU`;
   
   ALTER TABLE MY_TABLE DROP `VARCHAR_DATE`;
   

---

工作 SQL Fiddle .