如何设置 MySQL 日期时间列的默认值?

How do you set a default value for a MySQL Datetime column?

In SQL Server it's getdate(). What is the equivalant for MySQL? I'm using MySQL 5.x if that is a factor.

转载于:https://stackoverflow.com/questions/168736/how-do-you-set-a-default-value-for-a-mysql-datetime-column

csdnceshi74
7*4 quite the mission to install it, at least on Debian, but definitely an awesome feature. I think eventually all these "two CURRENT_TIMESTAMP" questions will be alleviated!
7 年多之前 回复
csdnceshi73
喵-见缝插针 This feature has now been added to MySQL 5.6.5. Hope this helps someone. optimize-this.blogspot.co.uk/2012/04/…
大约 8 年之前 回复
csdnceshi59
ℙℕℤℝ I use CURRENT_TIMESTAMP in my mysql table (not in the query), maybe this can be helpfull too.
大约 8 年之前 回复

24个回答

IMPORTANT EDIT: It is now possible to achieve this with DATETIME fields since MySQL 5.6.5, take a look at the other post below...

Previous versions can't do that with DATETIME...

But you can do it with TIMESTAMP:

mysql> create table test (str varchar(32), ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.00 sec)

mysql> desc test;
+-------+-------------+------+-----+-------------------+-------+
| Field | Type        | Null | Key | Default           | Extra |
+-------+-------------+------+-----+-------------------+-------+
| str   | varchar(32) | YES  |     | NULL              |       | 
| ts    | timestamp   | NO   |     | CURRENT_TIMESTAMP |       | 
+-------+-------------+------+-----+-------------------+-------+
2 rows in set (0.00 sec)

mysql> insert into test (str) values ("demo");
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+------+---------------------+
| str  | ts                  |
+------+---------------------+
| demo | 2008-10-03 22:59:52 | 
+------+---------------------+
1 row in set (0.00 sec)

mysql>

**CAVEAT: IF you define a column with CURRENT_TIMESTAMP ON as default, you will need to ALWAYS specify a value for this column or the value will automatically reset itself to "now()" on update. This means that if you do not want the value to change, your UPDATE statement must contain "[your column name] = [your column name]" (or some other value) or the value will become "now()". Weird, but true. I hope this helps. I am using 5.5.56-MariaDB **

csdnceshi59
ℙℕℤℝ ti doesn't seem to be possible for DATE columns: you have to use DATETIME data type
大约 4 年之前 回复
weixin_41568184
叼花硬汉 Hi, is it possible to have a default value for a DATE column? (not a datetime column).
4 年多之前 回复
csdnceshi69
YaoRaoLov It is possible from version 5.6.5, see Gustav's answer below (I realize your answer was posted when MySQL was still 5.0!)
7 年多之前 回复
csdnceshi71
Memor.の Be carefull too with timestamp as it auto-updates magically ... see next answer stackoverflow.com/a/1483959/233906
接近 8 年之前 回复
csdnceshi64
游.程 it's important to note that datetime has a range of 1000-9999, but the range for timestamp is only 1970-2038. this can be a problem if your system has to store birthdates, or you have to handle something like the payment plan for a 30-year mortgage. dev.mysql.com/doc/refman/5.0/en/datetime.html
接近 9 年之前 回复

For me the trigger approach has worked the best, but I found a snag with the approach. Consider the basic trigger to set a date field to the current time on insert:

CREATE TRIGGER myTable_OnInsert BEFORE INSERT ON `tblMyTable`
    FOR EACH ROW SET NEW.dateAdded = NOW();

This is usually great, but say you want to set the field manually via INSERT statement, like so:

INSERT INTO tblMyTable(name, dateAdded) VALUES('Alice', '2010-01-03 04:30:43');

What happens is that the trigger immediately overwrites your provided value for the field, and so the only way to set a non-current time is a follow up UPDATE statement--yuck! To override this behavior when a value is provided, try this slightly modified trigger with the IFNULL operator:

CREATE TRIGGER myTable_OnInsert BEFORE INSERT ON `tblMyTable`
    FOR EACH ROW SET NEW.dateAdded = IFNULL(NEW.dateAdded, NOW());

This gives the best of both worlds: you can provide a value for your date column and it will take, and otherwise it'll default to the current time. It's still ghetto relative to something clean like DEFAULT GETDATE() in the table definition, but we're getting closer!

csdnceshi55
~Onlooker In MySQL 5.5 the IFNULL doesn't work on DATETIME; using the above trigger the dateAdded will show all '0000-00-00 00:00:00'. Using this does the trick: FOR EACH ROW IF NEW.dateAdded = 0 THEN SET NEW.dateAdded = NOW(); END IF;
大约 5 年之前 回复
csdnceshi70
笑故挽风 Triggers are evil! Only ever use them when there's no other way of doing something. Better to upgrade to 5.6.x id you can than use a trigger.
5 年多之前 回复
weixin_41568110
七度&光 Never mind, I figured it out. Forgot to set the field to allow NULL. No warnings anymore.
8 年多之前 回复
csdnceshi53
Lotus@ I personally think this is the best way to go. TIMESTAMP does indeed have odd behavior and I would never write code that has a 2038 year limitation.
8 年多之前 回复
weixin_41568183
零零乙 +1 for acknowledging the caveat with overwriting of explicit user values on insert.
接近 9 年之前 回复

In version 5.6.5, it is possible to set a default value on a datetime column, and even make a column that will update when the row is updated. The type definition:

CREATE TABLE foo (
    `creation_time`     DATETIME DEFAULT CURRENT_TIMESTAMP,
    `modification_time` DATETIME ON UPDATE CURRENT_TIMESTAMP
)

Reference: http://optimize-this.blogspot.com/2012/04/datetime-default-now-finally-available.html

weixin_41568110
七度&光 This solution does not work for me. Error when creating table /* Error SQL (1067): Invalid default value for 'creation_time' */
2 年多之前 回复
weixin_41568131
10.24 I think the "ON UPDATE" syntax is wrong. According to dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html it should be dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
大约 3 年之前 回复
csdnceshi68
local-host Just a note - this does not work for date
4 年多之前 回复
csdnceshi77
狐狸.fox You're using a TIMESTAMP, not a DATETIME. Also, don't make it NULL.
5 年多之前 回复
csdnceshi78
程序go I am using version 5.6.22, but still I got error as follows : CREATE TABLE tbl ( InsertDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, UpdateDate TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP(6) ); Error Code: 1294. Invalid ON UPDATE clause for 'UpdateDate' column
5 年多之前 回复
csdnceshi77
狐狸.fox You need MySQL version 5.6.5 or later. You can see it working here: sqlfiddle.com/#!9/dd2be
大约 6 年之前 回复
csdnceshi57
perhaps? Invalid default value for 'menu_creation_time'
大约 6 年之前 回复

MySQL (before version 5.6.5) does not allow functions to be used for default DateTime values. TIMESTAMP is not suitable due to its odd behavior and is not recommended for use as input data. (See MySQL Data Type Defaults.)

That said, you can accomplish this by creating a Trigger.

I have a table with a DateCreated field of type DateTime. I created a trigger on that table "Before Insert" and "SET NEW.DateCreated=NOW()" and it works great.

I hope this helps somebody.

csdnceshi79
python小菜 You will probably want to set default value only IF NEW.created_at IS NOT NULL
8 年多之前 回复
csdnceshi73
喵-见缝插针 You're probably better off using a timestamp than a trigger for future sanity (maintenance). This is too trivial a use case for a trigger, although it is a solution.
接近 9 年之前 回复
csdnceshi67
bug^君 CREATE TRIGGER trigger_foo_SetCreatedAt BEFORE INSERT ON foo FOR EACH ROW SET NEW.created_at = UTC_TIMESTAMP();
接近 10 年之前 回复

Here is how to do it on MySQL 5.1:

ALTER TABLE `table_name` CHANGE `column_name` `column_name` 
TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

I have no clue why you have to enter the column name twice.

csdnceshi71
Memor.の CHANGE is also for renaming the field. The first column name is the 'old', the second column name is the 'new'. It looks redundant if you aren't changing the name of the field. If it's too aesthetically displeasing, try MODIFY.
8 年多之前 回复

this is indeed terrible news.here is a long pending bug/feature request for this. that discussion also talks about the limitations of timestamp data type.

I am seriously wondering what is the issue with getting this thing implemented.

csdnceshi60
℡Wang Yan It happened: dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html
7 年多之前 回复

MySQL 5.6 has fixed this problem.

ALTER TABLE mytable CHANGE mydate datetime NOT NULL DEFAULT 'CURRENT_TIMESTAMP'

If you have already created the table then you can use

To change default value to current date time

ALTER TABLE <TABLE_NAME> 
CHANGE COLUMN <COLUMN_NAME> <COLUMN_NAME> DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;

To change default value to '2015-05-11 13:01:01'

ALTER TABLE <TABLE_NAME> 
CHANGE COLUMN <COLUMN_NAME> <COLUMN_NAME> DATETIME NOT NULL DEFAULT '2015-05-11 13:01:01';
CREATE TABLE `testtable` (
    `id` INT(10) NULL DEFAULT NULL,
    `colname` DATETIME NULL DEFAULT '1999-12-12 12:12:12'
)

In the above query to create 'testtable', i used '1999-12-12 12:12:12' as default value for DATETIME column colname

While defining multi-line triggers one has to change the delimiter as semicolon will be taken by MySQL compiler as end of trigger and generate error. e.g.

DELIMITER //
CREATE TRIGGER `MyTable_UPDATE` BEFORE UPDATE ON `MyTable`
FOR EACH ROW BEGIN
        -- Set the udpate date
    Set new.UpdateDate = now();
END//
DELIMITER ;
共24条数据 1 3 尾页
Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问