MySQL datetime data Type set the current time as the default value

Environment: MySQL Sever 5.1 + MySQL command line tool

Problem: MySQL datetime data Type set current time as default

Solve:

Method One:

Because the default value of 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 automatic update is available.

Automatic Updates the first timestamp column occurs 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 some other columns change the value. (Note that an update setting lists the values it already has, which will not cause the timestamp column to be updated because if you set a list of its current values, MySQL ignores the changes for efficiency.) ）

3. You explicitly set the timestamp column null.

4. Timestamp columns other than the first 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 as timestamp and allow it to be empty.

CREATE TABLE Test (   
uname varchar NOT NULL,   
updatetime timestamp null defaultcurrent_timestamp on UPDATE Curre Nt_timestamp
) 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 functionality in the MySQL5.0 version.

CREATE TABLE Test_time (   
 idint (one),   
 create_time datetime   
);   
Delimiter |   
Create Trigger Default_datetime BeforeInsert on Test_time   
  foreach row   
    if new.create_time are null then
      set new . Create_time = Now ();   
    End if;|   
delimiter;

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/MySQL/