将当前日期或时间插入 MySQL [英] INSERT current date or time into MySQL

问题描述

如果我创建一个表，其中的实体假定为 DATE 并且当我 Insert 并将该列留空时，它不应该显示当前日期吗?时间一样吗?

If I create a table with an entity that is suppose to be DATE and when I Insert and leave that column blank shouldn't it display the current date? Same with time?

例如...

CREATE TABLE Register
(
Name CHAR(20) NOT NULL,
Date DATE,
Time TIME
);

然后我插入:

INSERT INTO Register (Name)
VALUES ('Howard');

我希望它显示在桌子上:

I want it to display on the table:

Howard | 5/6/2014 | 8:30 PM

而是显示:

Howard | NULL | NULL

这是否不正确，如果是，我应该如何Insert 以允许显示插入的当前日期和时间?

Is this incorrect and if so what am I suppose to Insert to allow the current date and time of insert to display?

推荐答案

首先，您的表中应该有一个 PRIMARY KEY.

其次，您还没有为 Date 和 Time 列设置默认值.此外，您不能为 DATE 和 TIME 类型分别设置它们 - 您应该使用 TIMESTAMP 类型和 DEFAULT CURRENT_TIMESTAMP喜欢:

Secondly, you have not set default values for columns Date and Time. Also, you can't set them separately for the DATE and TIME types – you should use TIMESTAMP type and DEFAULT CURRENT_TIMESTAMP like :

 CREATE TABLE Register (
    Name CHAR(20) PRIMARY KEY NOT NULL,
    Date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
 );

第三，如果你想正好使用两列来存储日期，你可以在这个表的INSERT事件上设置一个触发器，如下图所示:

Thirdly, if you want to use exactly two columns for date storing, you can set a trigger on INSERT event for this table, like it is shown below :

 CREATE TRIGGER default_date_time
 BEFORE INSERT ON my_table_name
 FOR EACH ROW
 BEGIN
    SET NEW.Date = CURDATE();
    SET NEW.Time = CURTIME();
 END;
 $$

这篇关于将当前日期或时间插入 MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！