Two TIMESTAMP fields are urgently needed when designing a MYSQL table.

When designing a MYSQL table, two TIMESTAMP fields are required. Sometimes, a database table requires a record creation time and a record modification time. The ideal design is as follows: the initial value of the update time is the same as the Creation time: CREATETABLE 'test _ table' ('id' INT (10) NOTNULL, 'create _ time' TIMEST

When designing a MYSQL table, two TIMESTAMP fields are required. Sometimes, a database table requires a record creation time and a record modification time. The ideal design is as follows: the initial value of the update time is the same as the Creation time: create table 'test _ table' ('id' INT (10) not null, 'create _ time' TIMEST

When designing a MYSQL table, two TIMESTAMP fields are required.

Sometimes, a database table requires a record creation time and a record modification time.

The ideal design is as follows: the initial value of the update time is the same as the creation time:

CREATE TABLE `test_table` (`id` INT( 10 ) NOT NULL,`create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) ENGINE = INNODB;

Or, the initial value of the update time is null and the value is available only during the update:

CREATE TABLE `test_table` (`id` INT( 10 ) NOT NULL,`create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,`update_time` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) ENGINE = INNODB;

Such an SQL statement for table creation cannot be executed. If an error is reported during execution, it is estimated that many people have encountered this problem:

Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

Find a solution on the Internet (only applicable to the initial value of the update time, which is the same as the creation time. Of course, this is also true ):

CREATE TABLE `test_table` (`id` INT( 10 ) NOT NULL,`create_time` TIMESTAMP NOT NULL DEFAULT 0,`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) ENGINE = INNODB;

INSERT statement:

INSERT INTO test_table (id, create_time, update_time) VALUES (1, NULL, NULL);

Or write like this (Note: create_time is not written ):

INSERT INTO test_table (id, update_time) VALUES (1, NULL);

The UPDATE statement is written normally (Suppose test_table.id can be modified ):

UPDATE test_table (id) VALUES (2);