| |
First, how MySQL said the current time?
In fact, the expression still find many, are summarized as follows:
CURRENT_TIMESTAMP
CURRENT_TIMESTAMP ()
NOW ()
LOCALTIME
LOCALTIME ()
LOCALTIMESTAMP
LOCALTIMESTAMP ()
Second, on the comparisons TIMESTAMP and DATETIME
A complete date in the following format: YYYY-MM-DD HH: MM: SS [.fraction], it can be divided into two parts: date and time portion portion, wherein, date portion corresponding format "YYYY-MM-DD" , time section corresponding to the format "HH: MM: SS [.fraction]". For the date field, the date portion only supports, if you insert the contents of the time portion, it will discard the contents of the section and prompt a warning.
mysql> create table test (id int, hiredate date);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into test values (1, '20151208000000');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values (1, '20151208104400');
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> show warning;
ERROR 1064 (42000): 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 1
mysql> select * from test;
+ ------ + ------------ +
| Id | hiredate |
+ ------ + ------------ +
| 1 | 2015-12-08 |
| 1 | 2015-12-08 |
+ ------ + ------------ +
2 rows in set (0.00 sec)
Note: The first reason is that the warning did not suggest that it's time parts are 0
TIMESTAMP and DATETIME the same point:
1> Both can be used to represent the YYYY-MM-DD HH: MM: SS [.fraction] type of date.
TIMESTAMP and DATETIME differences:
1> both are stored differently
For TIMESTAMP, it's time to insert the client conversion from the current time zone is UTC (Coordinated Universal Time) for storage. When the query, which in turn is converted to the client's current time zone to return.
For DATETIME, without any change, as it is basically the input and output.
Next, we verify
First, create two test table, use a timestamp format, use a datetime format.
mysql> create table test (id int, hiredate timestamp);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into test values (1, '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 (1, '20151208000000');
Query OK, 1 row affected (0.00 sec)
mysql> select * from test;
+ ------ + --------------------- +
| Id | hiredate |
+ ------ + --------------------- +
| 1 | 2015-12-08 00:00:00 |
+ ------ + --------------------- +
1 row in set (0.01 sec)
mysql> select * from test1;
+ ------ + --------------------- +
| Id | hiredate |
+ ------ + --------------------- +
| 1 | 2015-12-08 00:00:00 |
+ ------ + --------------------- +
1 row in set (0.00 sec)
Both the output is 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 | 2015-12-07 16:00:00 |
+ ------ + --------------------- +
1 row in set (0.00 sec)
mysql> select * from test1;
+ ------ + --------------------- +
| Id | hiredate |
+ ------ + --------------------- +
| 1 | 2015-12-08 00:00:00 |
+ ------ + --------------------- +
1 row in set (0.01 sec)
He said "CST" refers to the host where the MySQL system time, is an abbreviation for China standard time, China Standard Time UT + 8: 00
By results can be seen, test returned eight hours ahead of time, and the time test1 will remain unchanged. This fully verify the difference between the two.
2> can store both the time is not the same
Time timestamp can be stored as follows: '1970-01-0100: 00: 01.000000' to '2038-01-1903: 14: 07.999999'.
Datetime time can be stored as follows: '1000-01-0100: 00: 00.000000' to '9999-12-3123: 59: 59.999999'.
Summary: TIMESTAMP and DATETIME in addition to storage and storage range is not the same, not much difference. Of course, for business across time zones, TIMESTAMP more appropriate.
Third, the automatic initialization and updating on TIMESTAMP and DATETIME of
First, we 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 | 2015-12-08 14:34:46 |
+ ------ + --------------------- +
1 row in set (0.00 sec)
mysql> show create table test \ G
*************************** 1. row ******************** *******
Table: test
Create Table: CREATE TABLE `test` (
`Id` int (11) DEFAULT NULL,
`Hiredate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE = InnoDB DEFAULT CHARSET = latin1
1 row in set (0.00 sec)
Appears to be not a bit strange, I do not have to insert hiredate fields, its value is automatically changed to the current value, and create a table, I also did not define "show create table test \ G" displayed in the results "DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP".
In fact, this feature is automatically initialized and automatically update (Automatic Initialization and Updating).
Automatic initialization means is that if there is no explicit assignment of the field (such as in the example above hiredate field) is automatically set to the current system time.
Automatic updates means that if you modify the other fields, the value of this field will automatically be updated to the current system time.
It "explicit_defaults_for_timestamp" parameters.
By default, 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)
Here we look at the official description of the file:
By default, the first TIMESTAMP column has both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP if neither is specified explicitly.
In many cases, this is not what we want, how to disable it?
1. The "explicit_defaults_for_timestamp" value is set to ON.
2. "explicit_defaults_for_timestamp" value is still OFF, there are two ways to disable
1> that the column specify a default value with the DEFAULT clause
2> NULL attribute specified for that column.
As follows:
mysql> create table test1 (id int, hiredate timestamp null);
Query OK, 0 rows affected (0.01 sec)
mysql> show create table test1 \ G
*************************** 1. row ******************** *******
Table: test1
Create Table: CREATE TABLE `test1` (
`Id` int (11) DEFAULT NULL,
`Hiredate` timestamp NULL DEFAULT NULL
) ENGINE = InnoDB DEFAULT CHARSET = latin1
1 row in set (0.00 sec)
mysql> create table test2 (id int, hiredate timestamp default 0);
Query OK, 0 rows affected (0.01 sec)
mysql> show create table test2 \ G
*************************** 1. row ******************** *******
Table: test2
Create Table: CREATE TABLE `test2` (
`Id` int (11) DEFAULT NULL,
`Hiredate` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE = InnoDB DEFAULT CHARSET = latin1
1 row in set (0.00 sec)
Before MySQL 5.6.5 version, Automatic Initialization and Updating only applies to TIMESTAMP, and a table, allows up to a TIMESTAMP field using this feature. MySQL 5.6.5 from the beginning, Automatic Initialization and Updating applies to TIMESTAMP and DATETIME, and do not limit the number. |
|
|
|