Клише! Как база данных хранит время? ты действительно знаешь?

В нашей обычной разработке хранение времени неизбежно, например, нам нужно записать время этой записи в таблицу операций, записать время транзакции перевода, записать время отправления и так далее. Вы обнаружите, что эта вещь все еще очень тесно связана с нашим развитием в настоящее время, и независимо от того, правильно ли она используется или нет, она окажет большое влияние на наш бизнес и даже функции. Поэтому нам необходимо начать заново и хорошо узнать это дело.

Это короткая и лаконичная статья, прочтите ее внимательно и вы многому научитесь!

1. Не храните даты в строках

Я помню, что я делал это, когда учился в колледже, и сейчас это будут делать многие новички, которые мало что знают о базах данных.Видно, что этот способ хранения дат имеет некоторые преимущества, то есть он прост и понятен , и он прост в использовании.

Однако это неправильный подход, и есть две основные проблемы:

Строки занимают больше места!
Эффективность сравнения даты хранения строк относительно низка (сравнение символ за символом), и ее нельзя вычислить и сравнить с API, связанными с датой.

2. Выбор между Datetime и Timestamp

Datetime и Timestamp — это два похожих типа данных, предоставляемых MySQL для экономии времени. Как они должны выбирать между ними?

Обычно мы предпочитаем Timestamp.Давайте поговорим о том, почему!

2.1 Тип DateTime без информации о часовом поясе

Тип DateTime не имеет информации о часовом поясе (не зависит от часового пояса), время, сохраняемое типом DateTime, — это время, соответствующее часовому поясу, установленному текущим сеансом. Так в чем проблема? Когда ваш часовой пояс изменяется, например, если ваш сервер меняет адрес или меняет настройку часового пояса для подключения клиента, это приведет к тому, что время, которое вы читаете из базы данных, будет неправильным. Не стоит недооценивать эту проблему, многие системы сделали много шуток из-за этой проблемы.

Отметка времени связана с часовым поясом. Значение поля Timestamp type будет меняться при смене часового пояса сервера, и оно будет автоматически преобразовано в соответствующее время, проще говоря, значение этого поля будет разным для одной и той же записи в разных часовых поясах .

Продемонстрируем это в действии!

Оператор SQL для создания таблицы:

CREATE TABLE `time_zone_test` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `date_time` datetime DEFAULT NULL,
  `time_stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Вставить данные:

INSERT INTO time_zone_test(date_time,time_stamp) VALUES(NOW(),NOW());

Просмотр данных:

select date_time,time_stamp from time_zone_test;

результат:

+---------------------+---------------------+
| date_time           | time_stamp          |
+---------------------+---------------------+
| 2020-01-11 09:53:32 | 2020-01-11 09:53:32 |
+---------------------+---------------------+

Теперь мы бежим

Измените часовой пояс текущего сеанса:

set time_zone='+8:00';

Посмотрите еще раз на данные:

+---------------------+---------------------+
| date_time           | time_stamp          |
+---------------------+---------------------+
| 2020-01-11 09:53:32 | 2020-01-11 17:53:32 |
+---------------------+---------------------+

Расширение: некоторые общие команды sql для настройки часового пояса MySQL

# 查看当前会话时区
SELECT @@session.time_zone;
# 设置当前会话时区
SET time_zone = 'Europe/Helsinki';
SET time_zone = "+00:00";
# 数据库全局时区设置
SELECT @@global.time_zone;
# 设置全局时区
SET GLOBAL time_zone = '+8:00';
SET GLOBAL time_zone = 'Europe/Helsinki';

2.2 Тип DateTime занимает больше места

Отметка времени должна использовать только 4 байта дискового пространства, но DateTime требует 8 байтов дискового пространства. Однако это также создает проблему, поскольку Timestamp представляет меньший временной диапазон.

DateTime: 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
Отметка времени: 1970-01-01 00:00:01 ~ 2037-12-31 23:59:59

Отметка времени имеет небольшие различия в разных версиях MySQL.

3 Снова посмотрите на место хранения типа даты MySQL

На следующем рисунке показано место для хранения, занимаемое типом даты в MySQL 5.6:

Видно, что MySQL после 5.6.4 имеет дополнительный десятичный разряд, который требует от 0 до 3 байтов. Datatime и Timestamp занимают несколько разных мест хранения.

Для удобства в этой статье по умолчанию для Timestamp по-прежнему требуется только 4 байта дискового пространства, а для DateTime требуется 8 байт дискового пространства.

4. Является ли числовая временная метка лучшим выбором?

Много раз мы также используем значения типа int или bigint, то есть метки времени, для представления времени.

Этот метод хранения имеет некоторые преимущества типа Timestamp, и его использование для операций сортировки и сравнения дат будет более эффективным, а также он очень удобен для разных систем, в конце концов, это просто хранимое значение. Недостаток тоже очевиден, то есть читаемость данных слишком плохая, и визуально нельзя увидеть конкретное время.

Временные метки определяются следующим образом:

Определение метки времени должно начинаться с эталонного времени, эталонное время «1970-1-1 00:00:00 +0:00», начиная с этого времени, оно выражается как целое число, исчисляемое в секундах, и с время Время, прошедшее через это целое число, продолжает увеличиваться. Таким образом, мне нужно только одно значение для точного представления времени, и это значение является абсолютным значением, то есть независимо от того, где я нахожусь в любом уголке земли, временная метка, представляющая время, одинакова, генерируется. все одинаковы, и нет понятия часового пояса, поэтому при промежуточной передаче системного времени не требуется дополнительное преобразование.Только при отображении пользователю оно преобразуется в местное время в строковом формате.

Фактическая работа в базе данных:

mysql> select UNIX_TIMESTAMP('2020-01-11 09:53:32');
+---------------------------------------+
| UNIX_TIMESTAMP('2020-01-11 09:53:32') |
+---------------------------------------+
|                            1578707612 |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> select FROM_UNIXTIME(1578707612);
+---------------------------+
| FROM_UNIXTIME(1578707612) |
+---------------------------+
| 2020-01-11 09:53:32       |
+---------------------------+
1 row in set (0.01 sec)

5. Резюме

Как хранить время в MySQL? Datetime?Timestamp?Временная метка числового значения?

Похоже, что серебряной пули не существует.Многие программисты думают, что числовые метки времени действительно хороши, эффективны и совместимы, но многие люди считают, что их производительность недостаточно интуитивна. Просто слово здесь, автор волшебной книги «Высокопроизводительный MySQL» рекомендует отметку времени, потому что числовое представление времени недостаточно интуитивно. Вот исходный текст:

Каждый метод имеет свои преимущества, в зависимости от конкретной сцены. Давайте проведем простое сравнение этих трех методов, чтобы вы могли выбрать правильный тип данных о времени хранения в реальной разработке:

Если у вас есть какие-либо вопросы, пожалуйста, оставьте мне сообщение! Если есть какие-либо проблемы со статьей, пожалуйста, укажите на это, Гид очень благодарен!

В следующих статьях я расскажу:

[ ] Поддержка дат в Java8 и почему нельзя использовать SimpleDateFormat.
[ ] Как на самом деле использовать SpringBoot (например, JPA)