MySQL MySQL日期列自动填充当前日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21226571/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL date column auto fill with current date
提问by Jean Carlos Suárez Marranzini
I have a table where I have a datecolumn. Is there a way for MySQL to auto fill this field whenever I insert a new registry with the current date? Or is this made automatically by default?
我有一张桌子，上面有一date列。每当我插入具有当前日期的新注册表时，MySQL 是否可以自动填充此字段？或者这是默认情况下自动生成的？
P.S.: I'm using PHPMyAdmin
PS：我正在使用 PHPMyAdmin
采纳答案by Ben Siver
MySQL unfortunately doesn't allow specifying values other than constants as the default for columns other than TIMESTAMPs.
不幸的是，MySQL 不允许将常量以外的值指定为TIMESTAMPs以外的列的默认值。
This is a feature available in MySQL versions 8.0+, but for older versions the only solution for a database defined default would be to use a trigger.
这是 MySQL 8.0+ 版本中可用的功能，但对于旧版本，数据库定义默认值的唯一解决方案是使用触发器。
回答by MLBDG
Although it is an old post, maybe this image will help as it is more explicit: (For phpMyAdmin users)
虽然这是一篇旧帖子，但也许这张图片会有所帮助，因为它更明确：（对于 phpMyAdmin 用户）
This configuration sets that field with a value like:
此配置将该字段设置为如下值：
2015-12-11 07:50:47
2015-12-11 07:50:47
PS: Note that the timestamp will set the time OF your server!! (i.e. the example above got the time from Pacific Time (07:50:47) but it could have been from a Spanish user at 16:50:47 local time) Keep this in mind.
PS：注意时间戳会设置你服务器的时间！！（即上面的示例从太平洋时间 (07:50:47) 获取时间，但它可能来自当地时间 16:50:47 的西班牙用户）请记住这一点。
Also, if you already have a "Created Date" you might need another column that updates the modification date whenever there is an update: You only need to set on update CURRENT TIME STAMPin Attributes Field.
此外，如果您已经有一个“创建日期”，您可能需要在有更新时更新修改日期的另一列：您只需要在属性字段中设置更新当前时间戳。
Ready to rock!
准备摇滚！
回答by Harshada Chavan
Set Default to in your mySql query
在您的 mySql 查询中设置默认值
CURRENT_TIMESTAMP
回答by murtaza.webdev
you have to use
你必须使用
now()
现在（）
function where you want to fill current time.
要填充当前时间的函数。
i.e.:
IE：
INSERT INTO user_rights (`user_id`,`right`,`group_id`,`created_date`) VALUES ( '42', '160', '1', now());
回答by MER
I realize this may not be a direct answer to the question but I do believe this is the most useable solution.
I highly recommend using a DATETIME or TIMESTAMP data type for the column in question.
If you are utilizing a fairly current version of MySQL, MySQL will do the work for you.
Details:
To be very clear, as of 5.6.5, for both the TIMESTAMP & DATETIME datatypes, you can do the following:
我意识到这可能不是问题的直接答案，但我相信这是最有用的解决方案。
我强烈建议对相关列使用 DATETIME 或 TIMESTAMP 数据类型。
如果您使用的是最新版本的 MySQL，MySQL 将为您完成这项工作。
详细信息：
要非常清楚，从 5.6.5 开始，对于 TIMESTAMP 和 DATETIME 数据类型，您可以执行以下操作：
- Set a DEFAULT value of the current date & time (using NOW() or one of its aliases such as CURRENT_TIMESTAMP)
This means every time you insert a new row into this table a TIMESTAMP or DATETIME column with this default will get the current date and time - Set an ON UPDATE constraint that will UPDATE a column to the current date & time when, (you guessed it) the row is updated
- 设置当前日期和时间的 DEFAULT 值（使用 NOW() 或其别名之一，例如 CURRENT_TIMESTAMP）
这意味着每次在此表中插入新行时，具有此默认值的 TIMESTAMP 或 DATETIME 列将获取当前日期和时间 - 设置一个 ON UPDATE 约束，它将更新一列到当前日期和时间，（你猜对了）行被更新
Here's how:
An Example in a CREATE TABLE statement:
方法如下：
CREATE TABLE 语句中的示例：
CREATE TABLE t1 (
ts1 DATETIME ON UPDATE CURRENT_TIMESTAMP
,ts2 DATETIME DEFAULT NOW()
);
Please note that DATETIME can be replaced with TIMESTAMP for effectively the same functionality.
Additionally I suggest the use of the DATETIME data type over TIMESTAMP as DATETIME has a much larger range of dates it can support. It's worth mentioning that TIMESTAMP is smaller for those few cases that matters.
For more details please read my answer here: https://stackoverflow.com/a/26117532/1748266
请注意，DATETIME 可以替换为 TIMESTAMP 以获得有效的相同功能。
此外，我建议在 TIMESTAMP 上使用 DATETIME 数据类型，因为 DATETIME 可以支持更大范围的日期。值得一提的是，对于那些少数重要的情况，TIMESTAMP 较小。
有关更多详细信息，请在此处阅读我的回答：https: //stackoverflow.com/a/26117532/1748266
回答by Marcel Kraan
I have added this to my table and it works
我已将此添加到我的表中，并且可以正常工作
ALTER TABLE Medewerkers ADD med_created TIMESTAMP DEFAULT now();
When you insert data into your record it update automatically the med_created
当您将数据插入记录时，它会自动更新 med_created
回答by MZaragoza
You can do something like this from the SQL screen
您可以从 SQL 屏幕执行类似操作
ALTER TABLE `table_name` CHANGE `created_at` `created_at` TIMESTAMP NOT NULL
