Author: Wei Xinping, the 5th MySQL practical class student of zhishutang, the 10th MySQL optimization class student, now works as a teaching assistant.

Read MySQL 5.7 document 11.2 Date and Time Data Types

MySQL can be divided into five types: date, datetime, timestamp, time, and year. Next, I'd like to introduce you one by one.

DATE type

Store time of type YYYY-MM-DD, value range is' 1000-01-01 'to' 9999-12-31 '.

DATETIME and TIMESTAMP types

Both types are used to store time in YYYY-MM-DD hh:mm:ss[.fraction] format. However, there are some differences in value range and storage mode.

Range of values

The value range of DATETIME is' 1000-01-01 00:00:00 'to' 9999-12-31 23:59:59 ', and the value range of timestamp is' 1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC. It can be seen that the value range of DATETIME is much larger.

Storage mode

DATETIME is the time to directly store the insert. Time stamp is to convert the inserted value into utc time, then store it in the database, and then display it according to the time zone of the current session.

admin@localhost [weixinpingtest] 10:05:51>create table testtime(a timestamp,b datetime);
Query OK, 0 rows affected (0.05 sec)
admin@localhost [weixinpingtest] 10:07:20>insert into testtime() values(now(),now());
Query OK, 1 row affected (0.01 sec)
admin@localhost [weixinpingtest] 10:07:37>select * from testtime;
+---------------------+---------------------+
| a | b |
+---------------------+---------------------+
| 2020-03-04 10:07:37 | 2020-03-04 10:07:37 |
+---------------------+---------------------+
1 row in set (0.00 sec)
admin@localhost [weixinpingtest] 10:07:43>set timezone = '+9:00';#After changing the time zone, the time shown below will be different
Query OK, 0 rows affected (0.00 sec)
admin@localhost [weixinpingtest] 10:07:50>select * from testtime;
+---------------------+---------------------+
| a | b |
+---------------------+---------------------+
| 2020-03-04 11:07:37 | 2020-03-04 10:07:37 |
+---------------------+---------------------+
1 row in set (0.00 sec)

You can see that as long as you modify the time zone, when you query the second time, the field time of timestamp type is one hour longer, but the field time of datetime type does not change.

Automatic initialization and update

It's a bit of rubbish, but I still want to introduce what these two functions are.

The key for automatic initialization is default current? Timestamp.

CREATE TABLE t1 (
a int,
ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ,
)

When inserting data into table t1, if the value of ts is not specified, ts will be set to the current time.

The key of automatic update is on update current "timestamp

CREATE TABLE t1 (
a int,
ts TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

When the a field of table t1 is updated, ts will automatically update to the modified time. But if the value does not change when a is modified, then ts will not change.

Default current menu timestamp and on update current menu timestamp can be specified at the same time, or only one of them can be specified. The order of the two will have little effect.

Explicit? Defaults? For? Timestamp variable

There is also a database variable for timestamp. explicit_defaults_for_timestamp. This variable is off by default. So what happens when you are off.

• When creating a table, if neither automatic initialization nor automatic update is added. Then the first timestamp field will automatically add default current "timestamp and on update current" timestamp.

admin@localhost [weixinpingtest] 10:07:52>show global variables like '%explicitdefaultsfortimestamp%';
+---------------------------------+-------+
| Variablename | Value |
+---------------------------------+-------+
| explicitdefaultsfortimestamp | OFF |
+---------------------------------+-------+
1 row in set (0.00 sec)
admin@localhost [weixinpingtest] 10:15:14>create table testt3(a timestamp,b timestamp);
Query OK, 0 rows affected (0.02 sec)
admin@localhost [weixinpingtest] 10:15:44>show create table testt3;
+---------+------------------------------------------------------------------------------+
| Table | Create Table
+---------+------------------------------------------------------------------------------+
| testt3 | CREATE TABLE test_t3 (a timestamp NOT NULL DEFAULT CURRENTTIMESTAMP ON UPDATE CURRENT_TIMESTAMP,b timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
1 row in set (0.00 sec)

There are two ways to prevent this phenomenon. The first is to directly set the explicit default for timestamp parameter to on, the second is to set the default value for the field, or directly set it to null able.

• When timestamp does not specify that null is allowed, null cannot be inserted. When null is inserted, it will be converted to the current time by default

admin@localhost [weixinpingtest] 10:21:47>show create table testt4;
+---------+--------------------------------------------+
| Table | Create Table |
+---------+-------------------------------------------+
| test_t4 | CREATE TABLE test_t4 (a timestamp NULL DEFAULT NULL,b timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+---------+-------------------------------------------+
1 row in set (0.00 sec)
admin@localhost [weixinpingtest] 10:21:54>insert into testt4() values(null,null);
Query OK, 1 row affected (0.01 sec)
admin@localhost [weixinpingtest] 10:22:03>select * from testt4;
+------+---------------------+
| a | b |
+------+---------------------+
| NULL | 2020-03-05 10:22:03 |
+------+---------------------+
1 row in set (0.00 sec)
admin@localhost [weixinpingtest] 10:22:12>set explicitdefaultsfortimestamp=on;
Query OK, 0 rows affected (0.00 sec)
admin@localhost [weixinpingtest] 10:25:11>insert into testt4() values(null,null);
ERROR 1048 (23000): Column 'b' cannot be null

Field a can be null in the test_t4 table, and field b can be null by default, but there is no error in inserting the null value, except that field a becomes null and field b becomes the current time. Later, I opened explicit default for timestamp, inserted null again, and reported an error, saying that b cannot be null.

As for whether to open this variable or not, it depends on you. You can close and open it online anyway.

Decimals after seconds

Both DATETIME and TIMESTAMP can be added (n) when defining field types, such as DATETIME(3), where 3 means that the decimal after the second can be accurate to several digits. The value range of n is 0-6. If not set, the default is 0. Note that when initialization and automatic update are specified, this number will be added to the following. And it has to be consistent. as follows

admin@localhost [weixinpingtest] 10:34:53>CREATE TABLE testt5( ts TIMESTAMP(6) DEFAULT CURRENTTIMESTAMP(6) ON UPDATE CURRENTTIMESTAMP(6) );
Query OK, 0 rows affected (0.01 sec)
admin@localhost [weixinpingtest] 10:35:29>CREATE TABLE testt5( ts TIMESTAMP(6) DEFAULT CURRENTTIMESTAMP(6) ON UPDATE CURRENTTIMESTAMP(3) );
ERROR 1294 (HY000): Invalid ON UPDATE clause for 'ts' column

If there is a discrepancy, an error will be reported.

TIME type

Store data of type hh:mm:ss, and the value range is' - 838:59:59.000000 'to' 838:59:59.000000 '. The reason why the range can be so large is that time can also represent the time difference, so the range will be greater than 24 hours, or even negative. TIME also supports fractional seconds.

YEAR type

That stores the year. Values range from 1901 to 2155 and 0000.

tips

For DATE and DATETIME, the range of representation is very large, but mysql has a hint. For the DATE and DATETIME range descriptions, "supported" means that although searcher values might work, there is no guarantee.

Personal wechat: lvqingshan_