Set the current time as the default value for the MySQL datetime data type.

Environment: MySQL Sever 5.1 + MySQL command line tool

Problem: the current time of the MySQL datetime data type is set to the default value.

Solution:

Method 1:

Because the default value of the current MySQL field does not support functions, it is impossible 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 multiple TIMESTAMP columns exist, only the first one is automatically updated.

The first TIMESTAMP column is automatically updated under any of the following conditions:

1. The column value is not explicitly specified in an INSERT or load data infile statement.

2. The column value is not explicitly specified in an UPDATE statement and other columns change the value. (Note that setting an UPDATE column as its existing value does not cause the TIMESTAMP column to be updated, because if you set a column as its current value, MySQL ignores the changes for efficiency .)

3. You explicitly set the TIMESTAMP column as 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 ().

Therefore, you can set the date type to timestamp to allow null values.

1. CREATE TABLETest (
2. UnameVarchar(50)NOT NULL,
3. UpdatetimeTimestamp NULLDEFAULTCURRENT_TIMESTAMPON UPDATE CURRENT_TIMESTAMP
4. ) ENGINE = InnoDBDEFAULTCHARSET = utf8;

If you want to operate under navicat, set the field to timestamp, and enter CURRENT_TIMESTAMP by default.

Method 2:

You can also use trigger to implement this function in MySQL or later versions.

1. Create TableTest_time (
2. Idint (11 ),
3. Create_time datetime
4. );
5. Delimiter |
6. Create TriggerDefault_datetime beforeinsertOnTest_time
7. Foreach row
8. If new. create_timeIs Null Then
9. SetNew. create_time = now ();
10. EndIf; |
11. Delimiter;