MySQL database TIMESTAMP sets the default value of several Application Instances

Previously we introduced:MySQLDatabaseTIMESTAMPSet the default value for flexible use, this next time we will introduce several MySQL database TIMESTAMP set the default value of several application instances, hope to help you.

#1 check the table definition. You can see that column B has an attribute ON UPDATE CURRENT_TIMESTAMP. As a result, the column data is automatically updated even if the column is not involved. On the other hand, the default value of column c is '2017-00-00 00:00:00 ', and the actual insertion has been automatically assigned current_timestamp.

 
 

  
  
root@localhost:test >show create table dj1\G  
  
  
*************************** 1. row ***************************  
  
  
Table: dj1  
  
  
Create Table: CREATE TABLE `dj1` (  
  
  
`a` char(1) COLLATE utf8_bin DEFAULT NULL,  
  
  
`b` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  
  
  
`c` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',  
  
  
UNIQUE KEY `dj1_idx_u1` (`b`)  
  
  
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin 
  
  
1 row in set (0.00 sec) 
 
 

#2 create a table dj2 and column B does not contain automatic attribute update.

 
 

  
  
root@localhost:test >CREATE TABLE `dj2` (  
  
  
-> `a` char(1) COLLATE utf8_bin DEFAULT NULL,  
  
  
-> `b` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ,  
  
  
-> `c` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',  
  
  
-> UNIQUE KEY `dj1_idx_u1` (`b`)  
  
  
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;  
  
  
Query OK, 0 rows affected (0.01 sec) 
 
 

#3 insert dj2 Test Data

 
 

  
  
root@localhost:test >insert into dj2 values (1,null,null);   
  
  
Query OK, 1 row affected (0.00 sec)  
  
  
root@localhost:test >insert into dj2 values (2,null,null);   
  
  
Query OK, 1 row affected (0.00 sec) 
 
 

#4 View dj2 data

 
 

  
  
root@localhost:test >select * from dj2;  
  
  
+------+---------------------+---------------------+  
  
  
| a | b | c |  
  
  
+------+---------------------+---------------------+  
  
  
| 1 | 2009-09-09 14:02:55 | 2009-09-09 14:02:55 |   
  
  
| 2 | 2009-09-09 14:03:00 | 2009-09-09 14:03:00 |   
  
  
+------+---------------------+---------------------+  
  
  
2 rows in set (0.00 sec) 
 
 

# Create a unique index on 5dj2

 
 

  
  
Root @ localhost: test> create unique index dj2_idx_u1 on dj2 (B );
  
  
Query OK, 2 rows affected (0.02 sec)
  
  
Records: 2 Duplicates: 0 Warnings: 0
  
  
# Data updated successfully
  
  
Root @ localhost: test> update dj2 set a = 9;
  
  
Query OK, 2 rows affected (0.00 sec)
  
  
Rows matched: 2 Changed: 2 Warnings: 0
  
  
Root @ localhost: test> select * from dj2;
  
  
+ ------ + --------------------- +
  
  
| A | B | c |
  
  
+ ------ + --------------------- +
  
  
| 9 | 14:02:55 | 14:02:55 |
  
  
| 9 | 14:03:00 | 14:03:00 |
  
  
+ ------ + --------------------- +
  
  
2 rows in set (0.00 sec)
 
 

#6 create a table dj3. The default value of Column B is CURRENT_TIMESTAMP. The default value of column c is CURRENT_TIMESTAMP, which includes automatic attribute update. An error is returned and no behavior is allowed.

 
 

  
  
root@localhost:test >CREATE TABLE `dj3` (  
  
  
-> `a` char(1) COLLATE utf8_bin DEFAULT NULL,  
  
  
-> `b` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ,  
  
  
-> `c` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  
  
  
-> UNIQUE KEY `dj1_idx_u1` (`b`)  
  
  
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;  
  
  
ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause 
 
 

#7 create a table dj4. The default value of Column B is CURRENT_TIMESTAMP, and the default value of column c is '2017-00-00 00:00:00 '. attributes are automatically updated. An error is returned and no behavior is allowed.

 
 

  
  
root@localhost:test >CREATE TABLE `dj4` (  
  
  
-> `a` char(1) COLLATE utf8_bin DEFAULT NULL,  
  
  
-> `b` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ,  
  
  
-> `c` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,  
  
  
-> UNIQUE KEY `dj1_idx_u1` (`b`)  
  
  
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;  
  
  
ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause 
 
 

#8 create a table dj5. The default value of Column B is CURRENT_TIMESTAMP and the attribute is automatically updated. The default value of column c is CURRENT_TIMESTAMP. An error is returned and no behavior is allowed.

 
 

  
  
root@localhost:test >CREATE TABLE `dj5` (  
  
  
-> `a` char(1) COLLATE utf8_bin DEFAULT NULL,  
  
  
-> `b` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  
  
  
-> `c` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ,  
  
  
-> UNIQUE KEY `dj1_idx_u1` (`b`)  
  
  
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;  
  
  
ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause 
 
 

The above are several application instances for setting the default value of TIMESTAMP in the MySQL database. This article will introduce them here. I hope this introduction will help you gain some benefits!