MySQL datetime data Type Setting the current time is the default value

Environment: MySQL Sever 5.1 + MySQL command line tool

Problem: MySQL datetime data Type set the current time to the default value

Solve:

Method One:

Because the default value for the current field in MySQL does not support functions, it is not possible to set the default value in the form of create_time datetime default Now (). The alternative is to use the timestamp type instead of the datetime type.

The timestamp column type automatically marks the INSERT or update operation with the current date and time. If there are multiple timestamp columns, only the first one is automatically updated.

The automatic update of the first timestamp column occurs under any of the following conditions:

1. The column values are not explicitly specified in an INSERT or load DATA infile statement.

2. Column values are not explicitly specified in an UPDATE statement and some other columns change values. (Note An update setting that lists the values it already has, which will not cause the timestamp column to be updated, because if you set a list as its current value, MySQL ignores the changes for efficiency.) ）

3. You explicitly set the timestamp column to be null.

4. The timestamp column except the first one can also be set to the current date and time, as long as the column is set to NULL, or now ().

So choose the date type to timestamp allow empty.

2. CREATE TABLE Test (

4. uname varchar() not NULL,

6. UpdateTime timestamp NULL defaultcurrent_timestamp on UPDATE Current_timestamp

8. ) Engine=innodb DEFAULT Charset=utf8;

If you want to operate under Navicat, set the field to timestamp, and then write the default value Current_timestamp.

Method Two:

You can also use trigger to implement this feature in MySQL5.0 or later versions.

2. Create Table Test_time (

4. Idint (11),

6. Create_time datetime

8. );

10. Delimiter |

12. Create Trigger default_datetime beforeinsert on Test_time

14. foreach row

16. If New.create_time is null Then

18. Set New.create_time = Now ();

20. End if;|

22. delimiter;

MySQL datetime data Type Setting the current time is the default value