插入MYSQL时自动初始化GETDATE() [英] Automatically Initialize GETDATE() when inserting into MYSQL

问题描述

~类似问题~w3schools 也许有用的链接

~问题:我将行插入到 mysql 数据库中，我希望每一行都有一列显示当前日期(插入行的日期).

~Problem: I'm inserting rows into a mysql database and I want each row to have a column where the current date(date when row was inserted) is shown.

~尝试的解决方案:

CREATE TABLE $this->DB.$table
              (
              id int,
              Report_Date date DEFAULT CURRENT_DATE ON UPDATE CURRENT_DATE)//this fails.

有人能解释一下 ON UPDATE 是什么意思吗?我很紧张，当我有多天的数据时，它会更新 Report_Date 的所有值并弄乱我的数据.我认为，解决方案就在 GETDATE 中.此外，我不想要时间，只想要日期.非常感谢.

Would someone also explain what ON UPDATE means? I'm nervous that when I have multiple days of data it will update all values of Report_Date and mess up my data. The solution lies somewhere in GETDATE, I think. Furthermore I do not want time, only date. Much appreciated.

推荐答案

1. 如CREATE TABLE 语法所述(强调):

1. As documented under CREATE TABLE Syntax (emphasis added):

DEFAULT 子句指定列的默认值.除了一个例外，默认值必须是一个常量；它不能是函数或表达式.这意味着，例如，您不能将日期列的默认值设置为函数的值，例如 NOW() 或 CURRENT_DATE. 例外是您可以指定 CURRENT_TIMESTAMP 作为 CURRENT_TIMESTAMP 的默认值a href="http://dev.mysql.com/doc/en/datetime.html" rel="noreferrer">TIMESTAMP 或(从 MySQL 5.6.5 开始)DATETIME 列.请参阅 第 11.3.5 节，TIMESTAMP 和 DATETIME".

The DEFAULT clause specifies a default value for a column. With one exception, the default value must be a constant; it cannot be a function or an expression. This means, for example, that you cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE. The exception is that you can specify CURRENT_TIMESTAMP as the default for a TIMESTAMP or (as of MySQL 5.6.5) DATETIME column. See Section 11.3.5, "Automatic Initialization and Updating for TIMESTAMP and DATETIME".

因此您的选择是:

• 使用 TIMESTAMP 列(从 MySQL v5.6.5 开始，您也可以使用 DATETIME 列)并忽略时间部分；

• use a TIMESTAMP column (since MySQL v5.6.5, you can also use a DATETIME column) and ignore the time part;

在 INSERT 命令中明确指定当前日期:

explicitly specify the current date in the INSERT command:

INSERT INTO myTable (id, Report_Date) VALUES (123, CURRENT_DATE)

定义一个BEFORE INSERT 触发器:

CREATE TRIGGER myTrigger BEFORE INSERT ON myTable FOR EACH ROW
  SET NEW.Report_Date = IFNULL(NEW.Report_Date, CURRENT_DATE)

有人能解释一下 ON UPDATE 是什么意思吗?

Would someone also explain what ON UPDATE means?

如自动初始化和更新TIMESTAMP 所述和 DATETIME:

As documented under Automatic Initialization and Updating for TIMESTAMP and DATETIME:

• 自动初始化的列被设置为未指定列值的插入行的当前时间戳.

• An auto-initialized column is set to the current timestamp for inserted rows that specify no value for the column.

当行中任何其他列的值从其当前值更改时，自动更新的列会自动更新为当前时间戳.如果所有其他列都设置为其当前值，则自动更新的列保持不变.要防止自动更新的列在其他列更改时更新，请将其显式设置为其当前值.要在其他列未更改时更新自动更新的列，请将其显式设置为应具有的值(例如，将其设置为 CURRENT_TIMESTAMP).

An auto-updated column is automatically updated to the current timestamp when the value of any other column in the row is changed from its current value. An auto-updated column remains unchanged if all other columns are set to their current values. To prevent an auto-updated column from updating when other columns change, explicitly set it to its current value. To update an auto-updated column even when other columns do not change, explicitly set it to the value it should have (for example, set it to CURRENT_TIMESTAMP).

这篇关于插入MYSQL时自动初始化GETDATE()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！