Раздел 34 · MySQL с нуля

Числовые типы и AUTO_INCREMENT

junior~25 мин

без входаВойди, чтобы отмечать прогресс, вести заметки и решать задачи в редакторе. войти

В прошлом уроке мы договорились хранить все таблицы на InnoDB. Теперь решаем, что класть в колонки. Начнём с чисел: сколько мест в зале, сколько стоит билет, какой номер у строки. У MySQL на каждый такой случай свой тип, и неудачный выбор всплывёт ошибкой в самый неподходящий момент.

Счётчик на турникете

На входе в зал стоит турникет со счётчиком из трёх колёсиков. Он насчитает до 999, а тысячного зрителя показать не сможет. Счётчик из пяти колёсиков дороже и занимает больше места, зато дотянет до 99 999.

Целые типы MySQL устроены так же: чем больше байтов отведено под число, тем больше оно может быть.

ТипБайтовСо знакомUNSIGNED
TINYINT1от -128 до 127от 0 до 255
SMALLINT2от -32 768 до 32 767от 0 до 65 535
MEDIUMINT3от -8 388 608 до 8 388 607от 0 до 16 777 215
INT4от -2 147 483 648 до 2 147 483 647от 0 до 4 294 967 295
BIGINT8примерно ±9,2 × 10¹⁸от 0 до примерно 1,8 × 10¹⁹

Слово UNSIGNED убирает отрицательные числа. Места в зале и остаток на складе не бывают меньше нуля, поэтому в учебной базе halls.seats объявлена как SMALLINT UNSIGNED.

Проверим крайние значения. Создадим таблицу с колонкой каждого типа:

CREATE TABLE ranges (t TINYINT, ut TINYINT UNSIGNED, s SMALLINT, i INT, b BIGINT, flag BOOLEAN, width INT(11));
INSERT INTO ranges VALUES (127, 255, 32767, 2147483647, 9223372036854775807, TRUE, 1);
INSERT INTO ranges VALUES (-128, 0, -32768, -2147483648, -9223372036854775808, FALSE, 2);
SELECT * FROM ranges;
+------+------+--------+-------------+----------------------+------+-------+
| t    | ut   | s      | i           | b                    | flag | width |
+------+------+--------+-------------+----------------------+------+-------+
|  127 |  255 |  32767 |  2147483647 |  9223372036854775807 |    1 |     1 |
| -128 |    0 | -32768 | -2147483648 | -9223372036854775808 |    0 |     2 |
+------+------+--------+-------------+----------------------+------+-------+
2 rows in set (0.00 sec)

Обе строки поместились. А на единицу больше уже нет:

INSERT INTO ranges (t) VALUES (128);
ERROR 1264 (22003): Out of range value for column 't' at row 1

Для номера строки обычно хватает INT: больше двух миллиардов строк. Если таблица копит каждое событие, каждый клик или каждую строку журнала, бери BIGINT. Переделать тип колонки в таблице на миллиард строк намного дольше, чем сразу взять тип пошире.

Строгий режим

Ошибка Out of range это заслуга строгого режима. В MySQL 8.4 он включён по умолчанию. Без него сервер молча записал бы 127 вместо 128 и ограничился предупреждением. В старых статьях про MySQL ты встретишь такие истории: данные «сами» обрезались. Если увидишь в настройках сервера пустой sql_mode, это тот самый случай.

Остатки в буфете

Заведём в кинотеатре буфет. Таблица для закусок:

CREATE TABLE snacks (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(40) NOT NULL,
  stock TINYINT UNSIGNED NOT NULL,
  price DECIMAL(6, 2) NOT NULL,
  weight FLOAT
);

Остаток stock у нас TINYINT UNSIGNED, от 0 до 255. Попкорн помещается:

INSERT INTO snacks (name, stock, price, weight) VALUES ('Попкорн', 200, 250, 0.1);
Query OK, 1 row affected (0.00 sec)

А триста пачек начос и минус одна пачка нет:

INSERT INTO snacks (name, stock, price, weight) VALUES ('Начос', 300, 320, 0.2);
INSERT INTO snacks (name, stock, price, weight) VALUES ('Начос', -1, 320, 0.2);
ERROR 1264 (22003): Out of range value for column 'stock' at row 1
ERROR 1264 (22003): Out of range value for column 'stock' at row 1

Обе вставки отклонены. Сто двадцать пачек проходят:

INSERT INTO snacks (name, stock, price, weight) VALUES ('Начос', 120, 320.5, 0.2);
Query OK, 1 row affected (0.00 sec)

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

Деньги: DECIMAL, а не FLOAT

Цена у нас DECIMAL(6, 2). DECIMAL хранит число точно, цифра в цифру: шесть цифр всего, две после запятой, то есть до 9999.99. Вес закуски FLOAT, это число с плавающей точкой, приближённое. Посмотрим, что выйдет, если умножить на три цену и вес попкорна:

SELECT price * 3, weight * 3 FROM snacks WHERE id = 1;
+-----------+---------------------+
| price * 3 | weight * 3          |
+-----------+---------------------+
|    750.00 | 0.30000000447034836 |
+-----------+---------------------+
1 row in set (0.00 sec)

Цена ровно 750.00. А вес 0.1 превратился в 0.30000000447034836: FLOAT не умеет хранить 0.1 точно, только близкое двоичное число. То же видно на простом сложении. Литерал 0.1 в MySQL точный, а 0.1e0 записан в научной форме и становится приближённым DOUBLE:

SELECT 0.1 + 0.2, 0.1e0 + 0.2e0, 0.1 + 0.2 = 0.3, 0.1e0 + 0.2e0 = 0.3e0;
+-----------+---------------------+-----------------+-----------------------+
| 0.1 + 0.2 | 0.1e0 + 0.2e0       | 0.1 + 0.2 = 0.3 | 0.1e0 + 0.2e0 = 0.3e0 |
+-----------+---------------------+-----------------+-----------------------+
|       0.3 | 0.30000000000000004 |               1 |                     0 |
+-----------+---------------------+-----------------+-----------------------+
1 row in set (0.00 sec)

С DOUBLE 0.1 + 0.2 не равно 0.3, сравнение вернуло 0, то есть «ложь». Для веса или температуры такой хвост не страшен. Для денег страшен: на тысяче чеков копейки разойдутся с кассой. Деньги всегда храни в DECIMAL.

Что DECIMAL делает с лишними цифрами? Третью цифру после запятой он округляет и оставляет заметку:

INSERT INTO snacks (name, stock, price) VALUES ('Кола', 50, 99.999);
SHOW WARNINGS;
+-------+------+--------------------------------------------+
| Level | Code | Message                                    |
+-------+------+--------------------------------------------+
| Note  | 1265 | Data truncated for column 'price' at row 1 |
+-------+------+--------------------------------------------+
1 row in set (0.00 sec)

Кола записалась за 100.00. А вот лишнюю цифру до запятой он не примет:

INSERT INTO snacks (name, stock, price) VALUES ('Сок', 40, 12345.67);
ERROR 1264 (22003): Out of range value for column 'price' at row 1

Две старые привычки

В старых схемах ты увидишь INT(11). Число в скобках никогда не ограничивало размер, это была только ширина при выводе. В MySQL 8.4 она устарела, и сервер предупреждает об этом при создании таблицы:

+---------+------+------------------------------------------------------------------------------+
| Level   | Code | Message                                                                      |
+---------+------+------------------------------------------------------------------------------+
| Warning | 1681 | Integer display width is deprecated and will be removed in a future release. |
+---------+------+------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Это ответ SHOW WARNINGS сразу после CREATE TABLE ranges. Пиши просто INT.

Вторая привычка: отдельного логического типа в MySQL нет. BOOLEAN это другое имя для TINYINT(1), а TRUE и FALSE это 1 и 0. Вот как сервер описал таблицу ranges:

DESCRIBE ranges;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| t     | tinyint          | YES  |     | NULL    |       |
| ut    | tinyint unsigned | YES  |     | NULL    |       |
| s     | smallint         | YES  |     | NULL    |       |
| i     | int              | YES  |     | NULL    |       |
| b     | bigint           | YES  |     | NULL    |       |
| flag  | tinyint(1)       | YES  |     | NULL    |       |
| width | int              | YES  |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

Колонка flag стала tinyint(1), а у width скобки пропали.

AUTO_INCREMENT: номер выдаёт сервер

Вернёмся к буфету. Мы ни разу не указали id, а строки получили номера. Это работа AUTO_INCREMENT: сервер держит счётчик таблицы и при каждой вставке выдаёт следующий номер. Как номерки в гардеробе.

Номер последней вставки в твоём подключении вернёт LAST_INSERT_ID(). Вот его ответ сразу после удачной вставки начос, ещё до колы:

SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                2 |
+------------------+
1 row in set (0.00 sec)

Начос получили номер 2. Две вставки, отклонённые из-за Out of range, номеров не съели: значение не прошло проверку ещё до выдачи номера. Приложение берёт LAST_INSERT_ID(), чтобы узнать номер только что созданного заказа. Счёт идёт отдельно для каждого подключения, поэтому чужие вставки его не собьют.

Номер можно указать и самому. Тогда счётчик перепрыгнет вперёд:

INSERT INTO snacks (id, name, stock, price) VALUES (10, 'Лимонад', 50, 150);
INSERT INTO snacks (name, stock, price) VALUES ('Вода', 80, 90);
SELECT id, name, price FROM snacks;
+----+----------------+--------+
| id | name           | price  |
+----+----------------+--------+
|  1 | Попкорн        | 250.00 |
|  2 | Начос          | 320.50 |
|  3 | Кола           | 100.00 |
| 10 | Лимонад        | 150.00 |
| 11 | Вода           |  90.00 |
+----+----------------+--------+
5 rows in set (0.00 sec)

После ручного 10 вода получила 11. Номера с 4 по 9 пропущены навсегда.

Занятый номер вручную не вставить, это же первичный ключ:

INSERT INTO snacks (id, name, stock, price) VALUES (11, 'Дубль', 1, 1);
ERROR 1062 (23000): Duplicate entry '11' for key 'snacks.PRIMARY'

Следующая обычная вставка, чай, получит 12: неудачная попытка с явным номером счётчик не тронула.

Дыры в нумерации

Дыры появляются и без ручных номеров. Откроем транзакцию, добавим морс и передумаем:

START TRANSACTION;
INSERT INTO snacks (name, stock, price) VALUES ('Морс', 25, 120);
ROLLBACK;
INSERT INTO snacks (name, stock, price) VALUES ('Квас', 25, 110);
SELECT id, name FROM snacks WHERE id > 10;
+----+----------+
| id | name     |
+----+----------+
| 11 | Вода     |
| 12 | Чай      |
| 14 | Квас     |
+----+----------+
3 rows in set (0.00 sec)

Морс успел получить 13, ROLLBACK отменил строку, но номер не вернул. Квас получил 14. Сервер не возвращает выданные номера, потому что иначе двум подключениям пришлось бы ждать друг друга на каждой вставке. Номер из AUTO_INCREMENT гарантирует только одно: он не повторится. Без дыр он идти не обязан. Не используй его как номер чека для налоговой, где пропуски запрещены. Как то же самое устроено в PostgreSQL, смотри в уроке про serial и identity.

Что запомнить

  • Целые типы отличаются диапазоном, UNSIGNED сдвигает его в неотрицательные числа.
  • В строгом режиме число вне диапазона не обрезается, а даёт ошибку 1264.
  • Деньги храни в DECIMAL: он считает точно, а FLOAT и DOUBLE хранят приближение.
  • AUTO_INCREMENT выдаёт номер на сервере, LAST_INSERT_ID() возвращает последний выданный.
  • Дыры в нумерации нормальны: номер не возвращается ни после дубликата ключа, ни после отката.

Выбирай самый узкий тип, в который данные точно влезут с запасом на годы вперёд. Число мест в зале уместится в SMALLINT UNSIGNED, номер билета в INT, а счётчик просмотров на большом сайте лучше сразу сделать BIGINT: переделывать колонку на живой таблице дороже, чем потратить четыре лишних байта.

Попробуй сам

Поменяй тип stock на SMALLINT UNSIGNED и вставь начос снова. Вставь строку с id 100 и посмотри, какой номер получит следующая. Проверь, что выдаст SELECT 7 / 2, 7 DIV 2;: обычное деление в MySQL даёт дробь, а DIV целую часть.

Проверь себя

Задание

Дальше

С числами разобрались. В следующем уроке берёмся за строки: чем VARCHAR отличается от CHAR и TEXT, почему длина в байтах не совпадает с длиной в буквах и как MySQL сравнивает «Ёжика» с «ежиком».

домашка

Домашка