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

Source: Internet
Author: User
Tags date command line current time datetime mysql mysql command line

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/

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.