Flexible Use of MySQL database TIMESTAMP settings Default Value

MySQLDatabaseTIMESTAMPSetting the default value is what we will introduce in this article. We know that CURRENT_TIMESTAMP: When I update this record, this field of this record will not change.

CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP: When I UPDATE this record, this field of this record will change. Changes instantly to the Update time. 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 .)

If multiple TIMESTAMP columns exist, only the first one is automatically updated.

Next we will look at several practical examples:

#1 create a table dj1 with two timestamp fields.

 
 

  
  
root@localhost:test >create table dj1 (a char(1), b timestamp ,c timestamp);   
  
  
Query OK, 0 rows affected (0.01 sec) 
 
 

#2 insert two rows of data, only assigned to column

 
 

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

#3 query the inserted data. Both columns B and c use current timestamp as the default value.

 
 

  
  
root@localhost:test >select * from dj1;  
  
  
+------+---------------------+---------------------+  
  
  
| a | b | c |  
  
  
+------+---------------------+---------------------+  
  
  
| 1 | 2009-09-09 13:48:40 | 2009-09-09 13:48:40 |   
  
  
| 2 | 2009-09-09 13:48:44 | 2009-09-09 13:48:44 |   
  
  
+------+---------------------+---------------------+  
  
  
2 rows in set (0.00 sec) 
 
 

#4 update a row of data. It is found that the timestamp of Column B is automatically updated, while the value of column c remains unchanged.

 
 

  
  
root@localhost:test >update dj1 set a=9 where a=1;   
  
  
Query OK, 1 row affected (0.00 sec)  
  
  
Rows matched: 1 Changed: 1 Warnings: 0  
  
  
root@localhost:test >select * from dj1;  
  
  
+------+---------------------+---------------------+  
  
  
| a | b | c |  
  
  
+------+---------------------+---------------------+  
  
  
| 9 | 2009-09-09 13:49:08 | 2009-09-09 13:48:40 |   
  
  
| 2 | 2009-09-09 13:48:44 | 2009-09-09 13:48:44 |   
  
  
+------+---------------------+---------------------+  
  
  
2 rows in set (0.00 sec) 
 
 

#5 update another column, as shown in Figure #4

 
 

  
  
root@localhost:test >update dj1 set a=8 where a=2;   
  
  
Query OK, 1 row affected (0.00 sec)  
  
  
Rows matched: 1 Changed: 1 Warnings: 0  
  
  
root@localhost:test >select * from dj1;  
  
  
+------+---------------------+---------------------+  
  
  
| a | b | c |  
  
  
+------+---------------------+---------------------+  
  
  
| 9 | 2009-09-09 13:49:08 | 2009-09-09 13:48:40 |   
  
  
| 8 | 2009-09-09 13:49:36 | 2009-09-09 13:48:44 |   
  
  
+------+---------------------+---------------------+  
  
  
2 rows in set (0.00 sec) 
 
 

#6 create a unique index on column B

 
 

  
  
root@localhost:test >create unique index dj1_idx_u1 on dj1(b);  
  
  
Query OK, 2 rows affected (0.01 sec)  
  
  
Records: 2 Duplicates: 0 Warnings: 0 
 
 

#7 Update column a of all rows to report a uniqueness conflict.

 
 

  
  
root@localhost:test >update dj1 set a=1;  
  
  
ERROR 1062 (23000): Duplicate entry '2009-09-09 13:54:45' for key 'dj1_idx_u1' 
 
 

Conclusion:

1. The first timestamp Field in the DEFAULT MySQL table is not null default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP attribute, which must be explicitly defined to change this behavior.

2. MySQL only allows one timestamp field to have the [DEFAULT CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP] attribute. I understand that either DEFAULT CURRENT_TIMESTAMP or DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

3. Modify Field Property Values

 
 

  
  
show create table tbl_ledgerrecord;  
  
  
alter table tbl_ledgerrecord change intoStorageDate  intoStorageDate timestamp DEFAULT CURRENT_TIMESTAMP; 
 
 

Here is an introduction to the flexible use of MySQL database TIMESTAMP settings for default values. I hope this introduction will be helpful to you!