如何设置 MySQL 日期时间列的默认值?
如何设置MySQL Datetime列的默认值? 在SQL Server中是 getdate(). MySQL的等效功能是什么? 如果这是一个原因的话,那么我来使用MySQL5.x尝试一下。

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

csdnceshi78
程序go 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 年多之前 回复
weixin_41568196
撒拉嘿哟木头 This feature has now been added to MySQL 5.6.5. Hope this helps someone. optimize-this.blogspot.co.uk/2012/04/…
大约 8 年之前 回复
csdnceshi53
Lotus@ 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 **

weixin_41568131
10.24 ti doesn't seem to be possible for DATE columns: you have to use DATETIME data type
4 年多之前 回复
csdnceshi53
Lotus@ Hi, is it possible to have a default value for a DATE column? (not a datetime column).
4 年多之前 回复
csdnceshi55
~Onlooker 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 年多之前 回复
csdnceshi79
python小菜 Be carefull too with timestamp as it auto-updates magically ... see next answer stackoverflow.com/a/1483959/233906
接近 8 年之前 回复
weixin_41568184
叼花硬汉 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 年之前 回复

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.

csdnceshi67
bug^君 You will probably want to set default value only IF NEW.created_at IS NOT NULL
接近 9 年之前 回复
csdnceshi75
衫裤跑路 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 年之前 回复
csdnceshi72
谁还没个明天 CREATE TRIGGER trigger_foo_SetCreatedAt BEFORE INSERT ON foo FOR EACH ROW SET NEW.created_at = UTC_TIMESTAMP();
接近 10 年之前 回复

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!

csdnceshi80
胖鸭 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 年多之前 回复
weixin_41568126
乱世@小熊 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 年多之前 回复
csdnceshi69
YaoRaoLov Never mind, I figured it out. Forgot to set the field to allow NULL. No warnings anymore.
8 年多之前 回复
csdnceshi70
笑故挽风 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 年多之前 回复
csdnceshi77
狐狸.fox +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_41568127
?yb? This solution does not work for me. Error when creating table /* Error SQL (1067): Invalid default value for 'creation_time' */
2 年多之前 回复
weixin_41568126
乱世@小熊 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 年之前 回复
weixin_41568183
零零乙 Just a note - this does not work for date
4 年多之前 回复
csdnceshi63
elliott.david You're using a TIMESTAMP, not a DATETIME. Also, don't make it NULL.
接近 6 年之前 回复
csdnceshi69
YaoRaoLov 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
接近 6 年之前 回复
csdnceshi63
elliott.david You need MySQL version 5.6.5 or later. You can see it working here: sqlfiddle.com/#!9/dd2be
大约 6 年之前 回复
weixin_41568174
from.. Invalid default value for 'menu_creation_time'
大约 6 年之前 回复

I'm running MySql Server 5.7.11 and this sentence:

ALTER TABLE table_name CHANGE date_column datetime NOT NULL DEFAULT '0000-00-00 00:00:00'

is not working. But the following:

ALTER TABLE table_name CHANGE date_column datetime NOT NULL DEFAULT '1000-01-01 00:00:00'

just works.

As a sidenote, it is mentioned in the mysql docs:

The DATE type is used for values with a date part but no time part. MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format. The supported range is '1000-01-01' to '9999-12-31'.

even if they also say:

Invalid DATE, DATETIME, or TIMESTAMP values are converted to the “zero” value of the appropriate type ('0000-00-00' or '0000-00-00 00:00:00').

If you are trying to set default value as NOW(), I don't think MySQL supports that. In MySQL, you cannot use a function or an expression as the default value for any type of column, except for the TIMESTAMP data type column, for which you can specify the CURRENT_TIMESTAMP as the default.

You can use triggers to do this type of stuff.

CREATE TABLE `MyTable` (
`MyTable_ID`  int UNSIGNED NOT NULL AUTO_INCREMENT ,
`MyData`  varchar(10) NOT NULL ,
`CreationDate`  datetime NULL ,
`UpdateDate`  datetime NULL ,
PRIMARY KEY (`MyTable_ID`)
)
;

CREATE TRIGGER `MyTable_INSERT` BEFORE INSERT ON `MyTable`
FOR EACH ROW BEGIN
        -- Set the creation date
    SET new.CreationDate = now();

        -- Set the udpate date
    Set new.UpdateDate = now();
END;

CREATE TRIGGER `MyTable_UPDATE` BEFORE UPDATE ON `MyTable`
FOR EACH ROW BEGIN
        -- Set the udpate date
    Set new.UpdateDate = now();
END;

MySQL 5.6 has fixed this problem.

ALTER TABLE mytable CHANGE mydate datetime NOT NULL DEFAULT 'CURRENT_TIMESTAMP'
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

I was able to solve this using this alter statement on my table that had two datetime fields.

ALTER TABLE `test_table`
  CHANGE COLUMN `created_dt` `created_dt` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
  CHANGE COLUMN `updated_dt` `updated_dt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

This works as you would expect the now() function to work. Inserting nulls or ignoring the created_dt and updated_dt fields results in a perfect timestamp value in both fields. Any update to the row changes the updated_dt. If you insert records via the MySQL query browser you needed one more step, a trigger to handle the created_dt with a new timestamp.

CREATE TRIGGER trig_test_table_insert BEFORE INSERT ON `test_table`
    FOR EACH ROW SET NEW.created_dt = NOW();

The trigger can be whatever you want I just like the naming convention [trig]_[my_table_name]_[insert]

weixin_41568208
北城已荒凉 This will work as workaround for Mysql 5.5. thanks
2 年多之前 回复
weixin_41568131
10.24 Yikes, sorry about the lack of line breaks. Use the semi-colons to mark the end of each line.
接近 11 年之前 回复
weixin_41568131
10.24 Whoops! I meant The trigger (name) can be whatever you want it to be because the trigger name doesn't affect the functionality at all. Most people will know that, but some folks new to MySQL might not know...
接近 11 年之前 回复
weixin_41568131
10.24 I meant to say, If you insert records via the MySQL query browser manually via the grid without an insert() statement the trigger is needed. If you always use an insert statement the trigger is completely unnecessary.
接近 11 年之前 回复
共24条数据 1 3 尾页
Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问
相关内容推荐