MySQL table field time setting default value

Application Scenario

• In the data table, the application does not need to record when each piece of data was created. Instead, the database automatically records the creation time by obtaining the current time.
• In the database, to record when each piece of data was modified, the application does not need to record it specifically, but the database obtains the current time and automatically records the modification time.

Get the current time in the database

• oracle: select sysdate from dual;
• sqlserver: select getdate();
• mysql: select sysdate(); select now();

The difference between NOW() and SYSDATE() in MySQL

NOW() takes the time when the statement starts executing, and SYSDATE() takes the dynamic real-time time.

Because NOW() is taken from a MySQL variable "TIMESTAMP", and this variable is set when the statement starts executing, it will not change during the entire statement execution process.

Execute the following example to understand:

SELECT NOW(),SYSDATE(),SLEEP(3),NOW(),SYSDATE()

First, NOW() and SYSDATE() are queried, then sleep for 3 seconds, and then NOW() and SYSDATE() are queried again. The results are as follows:

Implementation

1. Set the field type to TIMESTAMP.
2. Set the default value to CURRENT_TIMESTAMP.

Example Application

Create a table time with the primary key id and one field date, which defaults to the current system time:

CREATE TABLE time(
id INT PRIMARY KEY,
date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Insert a piece of data:

INSERT INTO time(id) VALUES(1);

Query results:

This is the end of this article about setting default values ​​for MySQL table field time. For more information about default values ​​for MySQL field time, please search previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!