Shulou(Shulou.com)06/01 Report--

1. How to represent the current time in MySQL?

In fact, there are many ways to express it, which are summarized as follows:

CURRENT_TIMESTAMP

CURRENT_TIMESTAMP ()

NOW ()

LOCALTIME

LOCALTIME ()

LOCALTIMESTAMP

LOCALTIMESTAMP ()

II. Comparison between TIMESTAMP and DATETIME

A complete date format is as follows: YYYY-MM-DD HH:MM:SS [.dates], which can be divided into two parts: the date part and the time part, where the date part corresponds to the "YYYY-MM-DD" in the format and the time part corresponds to the "HH:MM:SS [.dates]" in the format. For the date field, it only supports the date part, and if the content of the time part is inserted, it discards that part and prompts for a warning.

As follows:

Mysql > create table test (id int,hiredate date); Query OK, 0 rows affected (0.01 sec) mysql > insert into test values; Query OK, 1 row affected (0.00 sec) mysql > insert into test values; Query OK, 1 row affected, 1 warning (0.01 sec) mysql > show warning;ERROR 1064 (42 000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'warning' at line 1mysql > select * from test +-+-+ | id | hiredate | +-+-+ | 1 | 2015-12-08 | | 1 | 2015-12-08 | +-+-+ 2 rows in set (2015 sec)

Note: the first reason warning is not prompted is that its time part is all 0.

What TIMESTAMP and DATETIME have in common:

1 > both can be used to represent dates of type YYYY-MM-DD HH:MM:SS [.dates].

The differences between TIMESTAMP and DATETIME:

1 > the storage methods of the two are not the same

For TIMESTAMP, it converts the time inserted by the client from the current time zone to UTC (Universal Standard time) for storage. When querying, it is converted to the client's current time zone for return.

For DATETIME, no change is made, basically as-is input and output.

Next, let's verify it.

First create two kinds of test tables, one in timestamp format and one in datetime format.

Mysql > create table test (id int,hiredate timestamp); Query OK, 0 rows affected (0.01 sec) mysql > insert into test values (1 row affected 20151208000000'); Query OK, 1 row affected (0.00 sec) mysql > create table test1 (id int,hiredate datetime); Query OK, 0 rows affected (0.01 sec) mysql > insert into test1 values (1Zhe 20151208000000'); Query OK, 1 row affected (0.00 sec) mysql > select * from test +-+-+ | id | hiredate | +-+-+ | 1 | 0-12-08 00:00:00 | +-+-+ 1 row in set (0.01sec) mysql > select * from test1 +-+-+ | id | hiredate | +-+-+ | 1 | 0-12-08 00:00:00 | +-+-+ 1 row in set (2015 sec)

The two outputs are the same.

Second, modify the time zone of the current session

Mysql > show variables like'% time_zone%' +-+-+ | Variable_name | Value | +-+-+ | system_time_zone | CST | | time_zone | SYSTEM | +-+-+ 2 rows in set (0.00 sec) mysql > set time_zone='+0:00' Query OK, 0 rows affected (0.00 sec) mysql > select * from test +-+-+ | id | hiredate | +-+-+ | 1 | 0-12-07 16:00:00 | +-+-+ 1 row in set (sec) mysql > select * from test1 +-+-+ | id | hiredate | +-+-+ | 1 | 0-12-08 00:00:00 | +-+-+ 1 row in set (2015 sec)

The above "CST" refers to the system time of the host where the MySQL is located, which is the abbreviation of Chinese Standard time, China Standard Time UT+8:00.

As can be seen from the results, the return time in test is 8 hours earlier, while the time in test1 remains the same. This fully verifies the difference between the two.

2 > the time range that the two can store is not the same.

The time range that a timestamp can store ranges from '1970-01-01 00-0000-01.000000' to' 2038-01-1903-14purl 07.999999'.

The time range that the datetime can store ranges from '1000-01-01 0000VL 00.000000' to' 9999-12-31 2323RV 59.999999'.

Summary: there is not much difference between TIMESTAMP and DATETIME except that the storage scope and storage mode are different. Of course, TIMESTAMP is more appropriate for businesses across time zones.

III. Automatic initialization and update of TIMESTAMP and DATETIME

First, let's take a look at the following operations

Mysql > create table test (id int,hiredate timestamp); Query OK, 0 rows affected (0.01 sec) mysql > insert into test (id) values (1); Query OK, 1 row affected (0.00 sec) mysql > select * from test +-+-+ | id | hiredate | +-+-+ | 1 | 0-12-08 14:34:46 | +-+-+ 1 row in set (2015 sec) mysql > show create table Test\ CREATE TABLE * 1. Row * * Table: testCreate Table: CREATE TABLE `test` (`id` int (11) DEFAULT NULL `hiredate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.00 sec)

Strange as it seems, I didn't insert the hiredate field, its value was automatically changed to the current value, and when I created the table, I didn't define the "DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP" shown in the "show create table test\ G" result.

In fact, this feature is automatic initialization and automatic update (Automatic Initialization and Updating).

Automatic initialization means that if there is no explicit assignment to the field (such as the hiredate field in the example above), it is automatically set to the current system time.

Automatic update means that if other fields are modified, the value of that field is automatically updated to the current system time.

It is related to the "explicit_defaults_for_timestamp" parameter.

By default, the value of this parameter is OFF, as follows:

Mysql > show variables like'% explicit_defaults_for_timestamp%' +-- +-+ | Variable_name | Value | +-+-+ | explicit_defaults_for_timestamp | OFF | +- -+-+ 1 row in set (0.00 sec)

Let's take a look at the description of the official file:

By default, the first TIMESTAMP column has both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP if neither is specified explicitly .

Very often, this is not what we want. How can we ban it?

1. Set the value of explicit_defaults_for_timestamp to ON.

2. The value of "explicit_defaults_for_timestamp" is still OFF, and there are two ways to disable it.

1 > specify a default value for this column with the DEFAULT clause

2 > specify the NULL property for the column.

As follows:

Mysql > create table test1 (id int,hiredate timestamp null) Query OK, 0 rows affected mysql > show create table test1\ show create table test1 * 1. Row * * Table: test1Create Table: CREATE TABLE `test1` (`id` int (11) DEFAULT NULL, `hiredate` timestamp NULL DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.00 sec) mysql > create table test2 (id int,hiredate timestamp default 0) Query OK, 0 rows affected mysql > show create table test2\ show create table test2 * 1. Row * * Table: test2Create Table: CREATE TABLE `test2` (`id`int (11) DEFAULT NULL, `hiredate`timestamp NOT NULL DEFAULT '0000-00-0000: 00mysql) ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.00 sec)

Prior to MySQL version 5.6.5, Automatic Initialization and Updating was only available for TIMESTAMP, and at most one TIMESTAMP field in a table was allowed to use this feature. Starting from MySQL 5.6.5, Automatic Initialization and Updating is available for both TIMESTAMP and DATETIME, and there is no limit to the number.

Reference:

1. Http://dev.mysql.com/doc/refman/5.6/en/datetime.html

2. Http://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html

The above is the whole content of this article, I hope it will be helpful to your study, and I also hope that you will support it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.