MySQL将存储日期与当前日期进行比较并执行 [英] MySQL comparing stored date with current date and execute

问题描述

我已将日期 (dd/mm/yyyy) 以文本格式存储在 table 中名为 dates 的字段中.我想将这些日期与当前日期进行比较，如果日期较小(如果日期已经过去)，则将整行移动到名为 archive 的新表中.用 DATEDIFF() 尝试了一些东西，但我是 MySQL 的新手，无法弄清楚.

I have stored dates (dd/mm/yyyy) in text format in a table in a field called dates. I want to compare those dates with the current date and if the dates are smaller (if the dates have passed) to move the entire row into a new table called archive. Tried something with the DATEDIFF() but I'm new to MySQL and can't figure it out.

推荐答案

我将以简短的评论作为我的回答的开头:在 SQL 数据库中以 VARCHAR 列存储日期"值是一种反模式.MySQL 提供本机数据类型 DATE，旨在处理日期"值.但这只是一句话，并不能回答你的问题.

I'm going to preface my answer with a short remark: storing "date" values in SQL database in VARCHAR columns is an anti-pattern. MySQL provides native datatype DATE which is designed to handle "date" values. But that's just a remark, doesn't answer your question.

您可以使用方便的 MySQL STR_TO_DATE 函数将字符串转换为 DATE 值.例如:

You can use the convenient MySQL STR_TO_DATE function to convert strings into DATE values. For example:

STR_TO_DATE('15/05/2015','%d/%m/%Y')

您可以使用列引用代替文字，例如

You could use a column reference in place of the literal, e.g.

STR_TO_DATE(t.mycharcol,'%d/%m/%Y')

这将返回一个 DATE 值，您可以将其与另一个 DATE 值进行比较，例如使用标准不等运算符 <.

and that will return a DATE value you can compare to another DATE value, using the standard inequality operator < for example.

要从数据库中返回当前日期，可以使用诸如

To return the current date from the database, you can use an expression such as

DATE(NOW())

综合起来，你可以写一个这样的查询:

Putting that together, you could write a query like this:

SELECT t.*
  FROM t
 WHERE STR_TO_DATE(t.mycharcol,'%d/%m/%Y') < DATE(NOW())

如果您想从 SELECT 语句中获取结果并将这些行插入到另一个表中，您可以使用 INSERT ... SELECT 形式的 INSERT 语句.

If you want to take the result from a SELECT statement and insert those rows into another table, you can use the INSERT ... SELECT form of the INSERT statement.

参考:https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_str-to-date

注意日期格式错误或无效的行为，例如

Beware of the behavior with badly formatted or invalid dates, e.g.

 SELECT STR_TO_DATE('35/05/2015','%d/%m/%Y')
      , STR_TO_DATE('15-05-2015','%d/%m/%Y')

这篇关于MySQL将存储日期与当前日期进行比较并执行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！