如何为MySQL Datetime列设置默认值?

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

如何为MySQL Datetime列设置默认值?

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

在SQL Server中，它是getdate()。MySQL的等价是什么?我使用的是MySQL 5。如果这是一个因子。

23 个解决方案

#1

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...

重要的编辑:现在可以使用DATETIME字段来实现这一点，因为MySQL 5.6.5，请看下面的另一篇文章……

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

以前的版本不能使用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 **

**警告:如果您定义一个带有CURRENT_TIMESTAMP的列为默认值，则需要始终为该列指定一个值，否则该值将在更新时自动重置为“now()”。这意味着如果不希望该值更改，则UPDATE语句必须包含“[您的列名]=[您的列名]”(或其他一些值)，否则该值将变为“now()”。奇怪,但是真的。我希望这可以帮助。我正在使用5.5.6 - mariadb **

#2

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:

在版本5.6.5中，可以在datetime列上设置一个默认值，甚至可以创建一个列，在行更新时更新该列。类型定义:

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

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

#3

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.)

MySQL(在版本5.6.5之前)不允许将函数用于默认的DateTime值。由于时间戳具有奇怪的行为，因此不适合使用它作为输入数据。(参见MySQL数据类型默认值)

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.

我有一个具有DateCreated类型DateTime字段的表。我在“插入之前”和“设置NEW.DateCreated=NOW()”表上创建了一个触发器，它工作得很好。

I hope this helps somebody.

我希望这对某人有帮助。

#4

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语句手动设置字段，比如:

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:

发生的情况是，触发器会立即覆盖字段的提供值，因此设置非当前时间的惟一方法是follow up UPDATE语句——呸!若要在提供值时重写此行为，请使用IFNULL操作符尝试此稍微修改过的触发器:

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!

这两个方面都是最好的:您可以为您的日期列提供一个值，它将占用，否则它将默认为当前时间。相对于表定义中的默认GETDATE()，它仍然是贫民区，但我们正在接近它!

#5

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

我可以使用表上的alter语句解决这个问题，该语句有两个datetime字段。

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.

这与您期望的now()函数的工作方式一致。插入null或忽略created_dt和updated_dt字段会在这两个字段中产生一个完美的时间戳值。对行的任何更新都会更改updated_dt。如果您通过MySQL查询浏览器插入记录，那么您还需要一个触发器来使用新的时间戳来处理created_dt。

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]

触发器可以是任何你想要的我喜欢命名约定[trig]_[my_table_name]_[插入]

#6

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;

#7

For all those who lost heart trying to set a default DATETIME value in MySQL, I know exactly how you feel/felt. So here is is:

对于那些试图在MySQL中设置默认的DATETIME值的人来说，我完全知道您的感受/感受。这是是:

ALTER TABLE  `table_name` CHANGE `column_name` DATETIME NOT NULL DEFAULT 0

Carefully observe that I haven't added single quotes/double quotes around the 0

注意，我没有在0附近添加单引号/双引号

I'm literally jumping after solving this one :D

在解决了这个问题之后，我就开始跳了。

#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.

这真是个坏消息。这里有一个等待已久的bug/特性请求。该讨论还讨论了时间戳数据类型的局限性。

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

我很想知道实现这个东西的问题是什么。

#9

MySQL 5.6 has fixed this problem.

MySQL 5.6解决了这个问题。

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

#10

For all who use the TIMESTAMP column as a solution i want to second the following limitation from the manual:

对于所有使用时间戳列作为解决方案的人，我想从手册中补充以下限制:

http://dev.mysql.com/doc/refman/5.0/en/datetime.html

http://dev.mysql.com/doc/refman/5.0/en/datetime.html

"The TIMESTAMP data type has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC. It has varying properties, depending on the MySQL version and the SQL mode the server is running in. These properties are described later in this section. "

“时间戳数据类型的范围是'1970-01 00:01' UTC到'2038-01-19 03:14:07' UTC。它有不同的属性，这取决于服务器运行的MySQL版本和SQL模式。本节后面将描述这些属性。”

So this will obviously break your software in about 28 years.

这显然会在28年内破坏你的软件。

I believe the only solution on the database side is to use triggers like mentioned in other answers.

我相信数据库方面的唯一解决方案是使用其他答案中提到的触发器。

#11

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'

将默认值更改为'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';

#12

You can use now() to set the value of a datetime column, but keep in mind that you can't use that as a default value.

可以使用now()设置datetime列的值，但请记住不能将其用作默认值。

#13

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.

当定义多行触发器时，必须将分隔符更改为分号，MySQL编译器将作为触发器的末尾并生成错误。如。

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

#14

I'm running MySql Server 5.7.11 and this sentence:

我运行的是MySql Server 5.7.11语句:

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:

作为一个旁注，它在mysql文档中被提到:

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'.

日期类型用于具有日期部分但没有时间部分的值。MySQL检索并显示“YYYY-MM-DD”格式的日期值。支持的范围是'1000-01-01'到'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').

无效的日期、日期时间或时间戳值被转换为适当类型的“零”值('0000-00-00'或'0000-00-00 00:00')。

#15

While you can't do this with DATETIME in the default definition, you can simply incorporate a select statement in your insert statement like this:

虽然不能在默认定义中使用DATETIME，但可以将select语句合并到insert语句中，如下所示:

INSERT INTO Yourtable (Field1, YourDateField) VALUES('val1', (select now()))

Note the lack of quotes around the table.

注意表中缺少引号。

For MySQL 5.5

MySQL 5.5

#16

Here is how to do it on MySQL 5.1:

下面是如何在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.

我不知道为什么要输入两次列名。

#17

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.

如果您试图像NOW()那样设置默认值，我认为MySQL不支持这一点。在MySQL中，不能将函数或表达式用作任何类型列的默认值，除了可以指定CURRENT_TIMESTAMP数据类型列作为默认值的TIMESTAMP数据类型列。

#18

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

在上面创建“testtable”的查询中，我使用“1999-12-12 12:12:12”作为DATETIME列colname的默认值

#19

Use the following code

使用下面的代码

DELIMITER $$

    CREATE TRIGGER bu_table1_each BEFORE UPDATE ON table1 FOR EACH ROW
    BEGIN
      SET new.datefield = NOW();
    END $$

    DELIMITER ;

#20

If you are trying to set default value as NOW(),MySQL supports that you have to change the type of that column TIMESTAMP instead of DATETIME. TIMESTAMP have current date and time as default..i think it will resolved your problem..

如果您试图将默认值设置为NOW()，那么MySQL支持您必须更改列时间戳的类型，而不是DATETIME。时间戳默认为当前日期和时间。我想它会解决你的问题的。

#21

Take for instance If I had a table named 'site' with a created_at and an update_at column that were both DATETIME and need the default value of now, I could execute the following sql to achieve this.

例如，如果我有一个名为“site”的表，其中包含一个created_at和一个update_at列，它们都是DATETIME，并且需要现在的默认值，那么我可以执行下面的sql来实现这一点。

ALTER TABLE `site` CHANGE `created_at` `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

ALTER TABLE `site` CHANGE `created_at` `created_at` DATETIME  NULL DEFAULT NULL;

ALTER TABLE `site` CHANGE `updated_at` `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

ALTER TABLE `site` CHANGE `updated_at` `updated_at` DATETIME NULL DEFAULT  NULL;

The sequence of statements is important because a table can not have two columns of type TIMESTAMP with default values of CUREENT TIMESTAMP

语句的顺序很重要，因为表不能有两个具有CUREENT时间戳默认值的类型时间戳列

#22

This is my trigger example:

这是我的触发例子:

/************ ROLE ************/
drop table if exists `role`;
create table `role` (
    `id_role` bigint(20) unsigned not null auto_increment,
    `date_created` datetime,
    `date_deleted` datetime,
    `name` varchar(35) not null,
    `description` text,
    primary key (`id_role`)
) comment='';

drop trigger if exists `role_date_created`;
create trigger `role_date_created` before insert
    on `role`
    for each row 
    set new.`date_created` = now();

#23

You can resolve the default timestamp. First consider which character set you are using for example if u taken utf8 this character set support all languages and if u taken laten1 this character set support only for English. Next setp if you are working under any project you should know client time zone and select you are client zone. This step are mandatory.

您可以解析默认的时间戳。首先考虑使用哪个字符集，例如，如果你使用utf8，这个字符集支持所有语言，如果你使用laten1，这个字符集只支持英语。下一个setp如果你在任何项目下工作，你应该知道客户时区，并选择你是客户时区。这一步是必需的。