时间戳

首页 > 解决方案 > 如何比较 MySQL 中格式为 dd.mm.yyyy 的日期（varchar）？

mysql - 如何比较 MySQL 中格式为 dd.mm.yyyy 的日期（varchar）？

问题描述

我希望能够选择 MySQL 数据库中两个日期之间的所有日期，其中日期是 dd.mm.yyyy 格式的 varchar。

Database
date (varchar) | value
------------------------
31.01.2018     | 123
------------------------
28.02.2018     | 456

这不起作用

Query
SELECT date,value from Database WHERE date BETWEEN '2018-01-01' AND '2018-01-31'

我知道我可以使用WHERE date LIKE '%.01.2018'整整几个月，但这还不够。

我可以在 PHP 中做到这一点，但不想这样做，因为这将花费太长时间。我想我可能会使用该CAST功能，但不确定如何使用。

在 MySQL 中执行此操作的最佳方法是什么？

标签： mysqldatecastingvarchar

解决方案

The best way to do this in MySQL is to avoid storing dates as a string in DD.MM.YYYY format. Instead, store dates in proper DATE columns, so they match the native format: YYYY-MM-DD.

The solutions of using STR_TO_DATE() or other functions means you can't use an index to optimize comparisons. The search will run with poor performance, because it has to do a table-scan.

Re your comment:

2 million rows is not a large table. It shouldn't take more than a minute or two to restructure it. It will only get harder if you wait until the table gets larger.

Since your data is in the wrong format, you can't just change the data type. Here's how I would fix this:

Step 1: Add a new column that is a proper DATE data type:

ALTER TABLE MyTable ADD COLUMN myDate DATE;

(By the way, don't name your table "Database" or your column "date". Those are reserved words, and not descriptive anyway. Do you name program variables "variable"?)

Step 2: Create a trigger so your app's use of the old column is automatically copied to the new column:

CREATE TRIGGER InsMyDate BEFORE INSERT ON MyTable
FOR EACH ROW 
SET NEW.myDate = COALESCE(NEW.myDate, STR_TO_DATE(NEW.oldDate, '%d.%m.%Y'));

CREATE TRIGGER UpdMyDate BEFORE UPDATE ON MyTable
FOR EACH ROW 
SET NEW.myDate = COALESCE(NEW.myDate, STR_TO_DATE(NEW.oldDate, '%d.%m.%Y'));

The reason for the COALESCE will be clear in a moment.

Step 3: Backfill all the values in past rows:

UPDATE MyTable
SET myDate = STR_TO_DATE(NEW.oldDate, '%d.%m.%Y')
WHERE myDate IS NULL;

You only need to do this once. Any new rows inserted will automatically be copying the proper date value to the new column after you create the triggers. Any rows inserted before you created the triggers will have NULL in the new column, so you need to UPDATE them as shown above.

Step 4: Change your application code so any reference to the old wrong date column uses the new date column. Address any INSERT, UPDATE, or SELECT that references the date. Test and deploy this code change.

Once you do this, any INSERT or UPDATE will specify a value in the new date column instead of a NULL. That will make the trigger effectively a no-op, because a non-NULL value in myDate will take precedence over the STR_TO_DATE() expression. This allows you to deploy the code at your convenience, and the database will just continue to do the right thing with your data.

Step 5: Drop the triggers and the old string-date column:

DROP TRIGGER InsMyDate;
DROP TRIGGER UpdMyDate;
ALTER TABLE MyTable DROP COLUMN oldDate;

If you're worried that each ALTER TABLE will lock the table during the table-restructuring, you should learn to use pt-online-schema-change. This allows you to do an ALTER TABLE without locking (except for a brief moment at the end of the restructuring).

推荐阅读