An in-depth summary of MySQL time setting considerations

Does time really exist? Some people believe that time is just a concept conceived by humans and is a standard used to measure the changes in things. For databases, time goes hand in hand with data. Enter the MySQL time vortex.

1. Time type fields

MySQL time type field:

The following are easy to overlook:

TIMESTAMP saves data in the following way:

MySQL converts TIMESTAMP values ​​from the current time zone to UTC for storage, and from UTC back to the current time zone for retrieval.
(This does not apply to other types, such as DATETIME.) By default, the current time zone for each connection is the server's time. The time zone can be set on a per connection basis. As long as the time zone setting remains the same, the same value stored is returned. If you store a timestamp value, then change the time zone and retrieve the value, the retrieved value will be different from the stored value. This occurs because the conversion is not done using the same time zone in both directions. The current time zone is available as the value of the time_zone system variable.

TIMESTAMP and SQL_MODE Combination

sql_mode also affects the timestamp value:

mysql> CREATE TABLE ts (
         id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
         col TIMESTAMP NOT NULL
     ) AUTO_INCREMENT = 1;

mysql> SHOW VARIABLES LIKE '%sql_mode%';
+---------------+---------------------+
| Variable_name | Value |
+---------------+---------------------+
| sql_mode | STRICT_TRANS_TABLES |
+---------------+---------------------+
mysql> INSERT INTO ts (col) VALUES ('1969-01-01 01:01:10');
ERROR 1292 (22007): Incorrect datetime value: '1969-01-01 01:01:10' for column 'col' at row 1

mysql> SET sql_mode="";
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES LIKE '%sql_mode%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode | |
+---------------+-------+
mysql> INSERT INTO ts (col) VALUES ('1969-01-01 01:01:10'),('2999-01-01 01:01:10');
Query OK, 2 rows affected, 2 warnings (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 2
mysql> show warnings;
+---------+------+----------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------+
| Warning | 1264 | Out of range value for column 'col' at row 1 |
| Warning | 1264 | Out of range value for column 'col' at row 2 |
+---------+------+----------------------------------------------+

mysql> SELECT * FROM TS;
+----+---------------------+
| id | col |
+----+---------------------+
| 1 | 0000-00-00 00:00:00 |
| 2 | 0000-00-00 00:00:00 |
+----+---------------------+
2 rows in set (0.00 sec)

By controlling sql_mode, values ​​exceeding the timestamp limit are still inserted, but the method of filling in blanks with 0 is adopted.
For STRICT_TRANS_TABLES , MySQL converts an invalid value to the closest valid value and then inserts the adjusted value. If a value is missing, MySQL inserts the implicit default value for the column data type.

2. explicit_defaults_for_timestamp time processing mechanism

The default is enabled.

As of MySQL 8.0.22, any attempt to insert NULL into a column declared as TIMESTAMP NOT NULL is rejected with an error.

1. When explicit_defaults_for_timestamp is disabled:

• A timestamp column that is not explicitly declared with the NULL attribute is automatically declared with the NOT NULL attribute. Assignment of NULL to such a column is permitted and sets the column to the current timestamp. As of MySQL 8.0.22, any attempt to insert NULL into a column declared as TIMESTAMP NOT NULL is rejected with an error.
• If the first column in a table is not declared with the NULL attribute or with an explicit DEFAULT or ON UPDATE attribute, it is automatically declared with the default CURRENT_TIMESTAMP attribute and the ON UPDATE CURRENT_TIMESTAMP attribute.
• TIMESTAMP is automatically declared with a default value of '0000-00-00 00:00:00' (the "zero" timestamp) if not explicitly declared with the NULL attribute or an explicit default attribute.
• The default value of "0000-00-00 00:00:00" might not be valid, depending on whether strict SQL mode or NO_ZERO_DATE SQL mode is enabled.

2. explicit_defaults_for_timestamp is enabled:

• It is not possible to specify a NULL value for TIMESTAMP to set it to the current timestamp. To specify the current timestamp, set CURRENT_TIMESTAMP or a synonym such as NOW().
• A TIMESTAMP column that is not explicitly declared with the not NULL attribute is automatically declared with the NULL attribute and allows null values.
• A timestamp column declared with the NOT NULL attribute does not allow null values. Inserts that specify NULL for such a column will result in an error for a single-row insert if strict SQL mode is enabled, or '0000-00-00 00:00:00' for a multiple-row insert if strict SQL mode is disabled. In any case, assigning a value of NULL to a column does not set it to the current timestamp.
• A timestamp column that is explicitly declared with the NOT NULL attribute and has no explicit default attribute is treated as having no default value. For inserted rows that do not specify explicit values ​​for such columns, the results depend on the SQL mode. If strict SQL mode is enabled, this will result in an error. If strict SQL mode is not enabled, the column is declared with the default implicit value of '0000-00-00 00:00:00' and a warning appears.
• Timestamp type columns are not automatically declared with the default CURRENT_TIMESTAMP attribute or with an updated CURRENT_TIMESTAMP attribute. These properties must be specified explicitly.

test:

CREATE TABLE `test1`(
id bigint not null AUTO_INCREMENT COMMENT 'Primary key ID', 
name varchar(20) COMMENT 'Primary key ID',
create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'cr time',
PRIMARY KEY(id)
)ENGINE=InnoDB AUTO_INCREMENT=1 ;
SHOW VARIABLES LIKE 'explicit_defaults_for_timestamp';
SET GLOBAL explicit_defaults_for_timestamp=ON;
SET GLOBAL explicit_defaults_for_timestamp=OFF;
INSERT INTO test1(id,name,create_time) VALUES(1,'Kit',NULL);

3.mysql system configuration

System-related event parameters include 3:

mysql>show global variables where Variable_name like '%time_zone%' or Variable_name like 'log_timestamp%';
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| system_time_zone | CST |
| time_zone | SYSTEM |
| log_timestamps | UTC |
+------------------+--------+
3 rows in set (0.00 sec)

1. System time zone: When the server starts, it tries to automatically determine the host's time zone and uses it to set the system_time_zone system variable. The value does not change thereafter.

2.time_zone: The full time_zone indicates the time zone in which the server is currently running. The initial time_zone value is "SYSTEM", which means that the server time zone is the same as the system time zone.

• If set to SYSTEM, MySQL function calls will call a system library to determine the current system time zone. This call might be protected by a global mutex, causing contention. High CPU usage issue.
• Setting the session time zone affects the display and storage of time zone-sensitive time values. This includes values ​​displayed by functions such as NOW() or CURTIME(), as well as values ​​stored in and retrieved from timestamp columns. Values ​​of timestamp columns are converted from the session time zone to UTC for storage, and from UTC to the session time zone for retrieval.
• The session time zone setting does not affect the values ​​displayed by functions such as UTC_TIMESTAMP(), or values ​​in DATE, time, or DATETIME columns. Values ​​of these data types are also not stored in UTC; the time zone applies to them only when converting from timestamp values.

Note: MySQL also provides a method to import time zones into the MySQL system library. Use the mysql_tzinfo_to_sql program to load the time zone information in /usr/share/zoneinfom.

mysql> SELECT COUNT(*) FROM mysql.time_zone_name;
+----------+
| COUNT(*) |
+----------+
| 0 |
+----------+

##mysql_tzinfo_to_sql tool to import time zone values

shell>mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

mysql> SELECT COUNT(*) FROM mysql.time_zone_name;
+----------+
| COUNT(*) |
+----------+
| 1780 |
+----------+

3.log_timestamps

This variable controls the time zone of timestamps in messages written to the error log and in general query log and slow query log messages written to files.

It does not affect the time zone to which the general query log and slow query log tables are written (mysql.general_log and mysql.slow_log).

Allowed log_timestamps values ​​are UTC (default) and SYSTEM (local system time zone)

Note: UTC generally refers to Coordinated Universal Time. Coordinated Universal Time, also known as World Unified Time, World Standard Time, International Coordinated Time, is UTC + 8 hours = China time

Of course, the value needs to be consistent with the system recording time for better management.

#Set the time zone and change it to East 8th zone SET GLOBAL time_zone = '+8:00';   

suggestion:

mysql configuration file my.cnf

[mysqld]
log_timestamps=SYSTEM
default-time_zone = '+8:00'

mysql>show global variables where Variable_name like '%time_zone%' or Variable_name like 'log_timestamp%';
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| log_timestamps | SYSTEM |
| system_time_zone | CST |
| time_zone | +08:00 |
+------------------+--------+

Summarize

Learn how to set and use time in MySQL from time types, parameters, and system time zones.

In particular, do not change sql_mode easily without special requirements.

This is the end of this article about MySQL time setting precautions. For more relevant MySQL time setting precautions, please search 123WORDPRESS.COM’s previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!