Date type is a common data type in database operation. TIMESTAMP type is believed to be used by many friends, but do you really understand it?

Introduced in MySQL to use TIMESTAMP type some potential problems, the maximum time limit is equal to the pit, buried in the future because some of the default rules system trigger date automatically update, the performance of the system default time zone problem, found the problem at the same time, behind also recommends some personally think good solution on date, for your reference.

MySQL installation

Docker is recommended to install a native MySQL, the steps are also very simple, as shown below, for learning or very convenient, installed can be ignored.

$ docker pull mysql
$ docker run -itd --name mysql-test -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 mysql
$ docker exec -it mysql-test /bin/sh
$ mysql -h localhost -u root -p
Copy the code

A pit buried in the future

Suppose you “38-01-19 03:14:07” execute a SQL update in the future, the first time “2038-01-19 03:14:07” succeeds, and the second time “2038-01-19 03:14:08” fails, and the error is only one second. Look at quite a normal SQL ah! According to?

# the first1UPDATE $UPDATEuser SET birthday = 'the 2038-01-19 03:14:07'  WHERE id = 1;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1  Changed: 0  Warnings: 0# the first2UPDATE $UPDATEuser SET birthday = 'the 2038-01-19 03:14:08'  WHERE id = 1;

ERROR 1292 (22007): Incorrect datetime value: 'the 2038-01-19 03:14:08' for column 'birthday' at row 1
Copy the code

In MySQL, because the space occupied by TIMESTAMP type is 4 bytes, it can theoretically store the maximum date “2038-01-19 03:14:07”, while the memory occupied by MySQL 5.6 is 7 bytes, which can be accurate to milliseconds or microseconds. But the maximum date has not been changed.

So we set more than one second above, the error is reported, for the system, even a little more is not good, exceed is exceed.

This restriction is also described in MySQL 11.2.2 The DATE, DATETIME, and TIMESTAMP Types:

The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of 'the 1970-01-01 00:00:01' UTC to 'the 2038-01-19 03:14:07' UTC.
Copy the code

Watch out for automatic updates of TIMESTAMP

Suppose a table has fields such as name and birthday. Automatic update is when you modify the name field in the table but find that birthday is updated to the current system time.

This is not always the case. It is related to a MySQL rule **explicit_defaults_for_timestamp**, which by default is OFF.

Run the following command to view information.

$ show variables like '%explicit_defaults_for_timestamp%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| explicit_defaults_for_timestamp | OFF   |
+---------------------------------+-------+
Copy the code

However, there are potential pitfalls, and some MySQL images simply change this value to ON to disable the function. For example, the Docker installation above already disables this feature.

Problem of repetition

To reproduce and illustrate the problem, I now need to unpack this functionality and use the following command.

$ SET @@SESSION.explicit_defaults_for_timestamp = 'ON';
Copy the code

First, let’s create a database and a user table. Note that birthday TIMESTAMP is currently defined as birthday TIMESTAMP NOT NULL.

$ CREATE DATABASE test;
$ CREATE TABLE user(
  id BIGINT NOT NULL AUTO_INCREMENT,
  name VARCHAR(20) NOT NULL,
  birthday TIMESTAMP NOT NULL.PRIMARY KEY (id)
);
Copy the code

Perform DESC user; Select * from ‘birthday’ where ‘Extra’ = ‘birthday’;

DESC user;
+----------+-------------+------+-----+-------------------+-----------------------------------------------+
| Field    | Type        | Null | Key | Default           | Extra                                         |
+----------+-------------+------+-----+-------------------+-----------------------------------------------+
| id       | bigint      | NO   | PRI | NULL              | auto_increment                                |
| name     | varchar(20) | NO   |     | NULL              |                                               |
| birthday | timestamp   | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+----------+-------------+------+-----+-------------------+-----------------------------------------------+
Copy the code

This section has a DEFAULT rule. When explicit_defaults_for_TIMESTAMP is enabled, create the first column of the TIMESTAMP type specified by the table. If NULL or DEFAULT or ON UPDATE is not displayed, The **DEFAULT_GENERATED on UPDATE CURRENT_TIMESTAMP** attribute is automatically generated for us after the table is created successfully. Our example corresponds to the birthday TIMESTAMP NOT NULL defined above.

If set to this, it means modifying the data, changing the field corresponding to this type to the current system date of the database.

Change the rule, and only one field in a table can have this feature, if set to two will report an error.

$ CREATE TABLE user(
  birthday TIMESTAMP NOT NULL,
  utime TIMESTAMP NOT NULL,);//When you run it, you get oneshow variables like '%explicit_defaults_for_timestamp%'; Error.Copy the code

Insert a row into the user table.

$ INSERT INTO user(name, birthday) VALUES('Tom', NOW(6));
Copy the code

Assume that the current time point is T1 (the current time of T1 is 2021-01-01 06:10:27) and view the data in the current user table.

$ SELECT * FROM user;
+----+------+---------------------+
| id | name | birthday            |
+----+------+---------------------+
|  1 | Tom  | 2021- 06- 06 06:10:27 |
+----+------+---------------------+
Copy the code

Alter table user = Tom2; alter table user = Tom2;

$ UPDATE user SET name = 'Tom2' WHERE id = 1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0
Copy the code

Query again and found that the value of the birthday was changed to T2 this point in time, but didn’t write clearly the above SQL statement to update birthday this field! According to?

$ SELECT * FROM user;
+----+------+---------------------+
| id | name | birthday            |
+----+------+---------------------+
|  1 | Tom2 | 2021- 06- 06 06:13:06 |
+----+------+---------------------+
Copy the code

The solution

When explicit_defaults_for_timestamp is turned on (its value is OFF), if we do not explicitly assign a value to a TIMESTAMP field, we will default to the current system time when updating.

If you are not aware of this problem, it is very frustrating to look up the SQL statement, but it is still updated.

Most of the time, this is not what we want. How do you disable it?

Method 1: Modify system parameters

Disable this property by changing the explicit_DEFAULts_for_TIMESTAMP value to ‘ON’.

SET @@session. explicit_defaults_for_timestamp = ‘ON’; Modification. Here is another pit that tests verify is invalid in Settings once the table has been created. If tables are created after this rule is disabled, this is ok.

Method 2: Modify the table structure

For those that are running online and cannot be modified, you can’t just delete the table and change it again.

There are also two methods to disable when the explicit_DEFAULts_for_TIMESTAMP attribute is OFF, requiring modification of the table structure.

//Specify the columnNULL, such as $ALTER TABLE user MODIFY birthday TIMESTAMP NULL.//useDEFAULTSpecify a default value for this column, such as $ALTER TABLE user MODIFY birthday TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
Copy the code

Finally, as described in the MYSQL official documentation sysvar_explicit_defaults_for_timestamp, this non-standard behavior has been deprecated and is expected to be removed in future MYSQL releases. It is really a pit of behavior, if not familiar with the document, it is easy to step on the pit.

TIMESTAMP Performance problem

The TIMESTAMP type supports time zone conversion, which has both advantages and disadvantages. When the default time zone of the operating SYSTEM is (time_zone=SYSTEM), querying the SYSTEM time zone will call the SYSTEM time zone for time zone conversion. The system time zone needs to be locked to ensure that the operating system time zone is not changed.

When there is concurrent access, there is bound to be resource competition, multithreading context switch consumption, performance will also appear to decline, below we do a performance test.

View the current time zone information. Time_zone =SYSTEM Indicates the time zone of the operating SYSTEM.

$ show variables like "%time_zone%";
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | UTC    |
| time_zone        | SYSTEM |
+------------------+--------+
Copy the code

Time zone changes

By default, MySQL uses the system time zone. You can change the system time zone in either of the following ways: Using SQL commands to change the system time zone temporarily or modifying the configuration file permanently.

# SQLCommand to modify $SET time_zone = 'Asia/Shanghai'; # config file $vim/etc/mysql/my.cnf
default-time_zone = 'Asia/Shanghai'
Copy the code

If using Docker can be modified during Docker run. -e TZ=’Asia/Shanghai’ However, running the show variables like “%time_zone%” command time_zone will still show SYSTEM.

$ docker run -itd --name mysql-test -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 -e TZ='Asia/Shanghai' mysql
Copy the code

Recommended to modify files, first into the container to perform the cat/etc/mysql/conf. D/mysql. CNF command to see the default configuration, copy a to own this machine computer, in the execution docker run when mounted to the container, this way is when you have multiple configuration needs to be modified, All can be changed in the configuration file.

A configuration file might look like this:

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
secure-file-priv= NULL
default-time_zone = 'Asia/Shanghai'# Custom config should go here ! includedir /etc/mysql/conf.d/Copy the code

The final docker run command is as follows:

$docker run -itd --name mysql-test -p $docker run -itd --name mysql-test -p3306:3306 -e MYSQL_ROOT_PASSWORD=123456 -v /${root}/mysql.cnf:/etc/mysql/my.cnf mysql
Copy the code

The performance test

Mysqlslap: MysqlSLAP is a stress test tool that simulates multiple concurrent clients to perform stress tests on MySQL.

The following statement means to simulate 100 client concurrent execution of 1,000,000 queries.

# --number-of-queries Indicates the total number of test queries
# -C concurrency, simulating execution of multiple clients. The following example simulates execution of multiple clients.SELECTNOW # ()"--create-schema indicates the user-defined test library name, which is the name of the MySQL database
$ mysqlslap -u root -p --number-of-queries=1000000 --create-schema=test -c 100 --query='SELECT NOW()'
Copy the code

Here is a performance test based on mysqlSLAP. The time taken in seconds in different time zones is obviously much longer in the system time zone, with a direct 25% difference. This is just a time difference, CPU information I didn’t look at. And different computers, the performance gap will be different.

—— System Asia/Shanghai difference
Average number of seconds to run all queries 35.55 s 28.42 s 25%

How to choose the date?

In MySQL, there are usually three schemes for date type storage, including INT, TIMESTAMP and DATETIME.

The INT type

INT stores the date type and the timestamp type. For example, the timestamp of 2021-01-01 06:10:27 is 1609452627000.

The actual database store is a string of numbers, this advantage is no time up and down range, performance is better than TIMESTAMP, but this performance is a little effect, an unfriendly problem is that when we want to view the data to do some troubleshooting or data analysis, usually not very intuitive.

TIMESTAMP type

The TIMESTAMP type converts the local time zone to the UTC time zone, and then converts the UTC time zone to 4 bytes. When read, convert to TIMESTAMP again in reverse, do some time formatting, it looks more intuitive.

The TIMESTAMP type has the maximum time limit. The time range that can be stored effectively is “‘1970-01-01 00:00:01.000000’ to ‘2001-19 03:14:07.999999′”. The year 2038 is far away and very fast. This is something that needs to be considered. Don’t bury a hole for the future.

The TIMESTAMP type, although supported after version 5.6, is accurate to smile, milliseconds after 6, but the 2038 maximum time limit issue has not been resolved.

Another problem I find hidden is that it’s easy to crash when you define a field as birthday DATETIME NOT NULL and trigger its auto-update rule. The terrible thing is that the development and production environment configuration is not consistent, this kind of problem can not be found in the early stage, unless stepping on the pit.

A DATETIME type

The DATETIME type is recommended by the authors. It takes 8 bytes, can be stored with subtle precision, and is specified by DATETIME(6) when declaring the type.

Its time range is ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’. This time is enough for us at present. Of course, if you want to store when Zhang Fei was born in The Three Kingdoms Period, you can’t store the 160th birthday.

DATETIME does not store time zone information. This problem is not necessarily solved in the data layer, nor is it a big deal. If you want to do this kind of internationalized cross-time zone service, it can be solved by the middle layer service (node.js is a good fit). I think this date type solves some of the problems we encountered with TIMESTAMP above.

Modify the user table structure above to declare the date type as DATETIME.

  • The birthday field is passed in as a user custom, specified as non-null, and the DATETIME is declared to be exact in seconds.
  • The ctime field defaults to the current time, specified only at creation time and accurate to microseconds.
  • The utime field records the time of each UPDATE. This is not affected by the explicit_defaults_for_TIMESTAMP parameter and is also defined in our display ON UPDATE… Then automatic updates are triggered.
CREATE TABLE user(
  id BIGINT NOT NULL AUTO_INCREMENT,
  name VARCHAR(20) NOT NULL,
  birthday DATETIME NOT NULL,
  ctime DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  utime DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id)
);
Copy the code

Suppose we insert a DATETIME and pass birthday to 2039, it’s ok to use DATETIME, and we can also look at ctime and utime, which is the precision we defined.

+----+------+---------------------+----------------------------+----------------------------+
| id | name | birthday            | ctime                      | utime                      |
+----+------+---------------------+----------------------------+----------------------------+
|  1 | Tom  | 2039- 01- 01 22:00:28 | 2021- 01- 01 22:00:28.112048 | 2021- 01- 01 22:00:28.112048 |
+----+------+---------------------+----------------------------+----------------------------+
Copy the code

Do you really know about MySQL timestamp types? Early watch, early avoid!