AUTO_INCREMENT в MySQL

Казалось бы, что может быть проще, чем автоинкремент в mysql. Но и с ним есть подводные камни и грабли, которые лучше знать и учитывать при проектировании баз данных.

Создадим тестовую табличку:

mysql> create table test_ai(
    ->     id int primary key not null auto_increment,
    ->     alias varchar(255) not null,
    ->     unique index (alias)
    -> );
Query OK, 0 rows affected (0.04 sec)

Главное, что в ней есть - это уникальный ключ, по полю alias

Вставим три записи

mysql> insert into test_ai(alias) values ('alias1'), ('alias2'), ('alias3');
Query OK, 3 rows affected (0.00 sec)

Посмотрим значение auto_increment в табличке

mysql> show create table test_ai\G
*************************** 1. row ***************************
       Table: test_ai
Create Table: CREATE TABLE `test_ai` (
  `id` int NOT NULL AUTO_INCREMENT,
  `alias` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `alias` (`alias`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Всё вроде верно. Мы вставили 3 записи и следующее значение id будет равно 4.

Теперь начинается интересное. Попробуем выполнить такую же вставку что и предыдущая.

mysql> insert into test_ai(alias) values ('alias1'), ('alias2'), ('alias3');
ERROR 1062 (23000): Duplicate entry 'alias1' for key 'test_ai.alias'

Ожидаемо, получим ошибку. Посмотрим опять на значение auto_increment

mysql> show create table test_ai\G
*************************** 1. row ***************************
       Table: test_ai
Create Table: CREATE TABLE `test_ai` (
  `id` int NOT NULL AUTO_INCREMENT,
  `alias` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `alias` (`alias`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)

7! То есть mysql сначала увеличивает счетчик, а потом вставляет. Ладно, подавим ошибку и еще раз вставим.

mysql> insert ignore into test_ai(alias) values ('alias1'), ('alias2'), ('alias3');
Query OK, 0 rows affected, 3 warnings (0.02 sec)
Records: 3  Duplicates: 3  Warnings: 3

И посмотрим.

mysql> show create table test_ai\G
*************************** 1. row ***************************
       Table: test_ai
Create Table: CREATE TABLE `test_ai` (
  `id` int NOT NULL AUTO_INCREMENT,
  `alias` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `alias` (`alias`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

10! То есть автоинкремент увеличивается в любом случае.

Попробуем replace.

mysql> replace into test_ai(alias) values ('alias1'), ('alias2'), ('alias3');
Query OK, 6 rows affected (0.00 sec)
Records: 3  Duplicates: 3  Warnings: 0

mysql> show create table test_ai\G
*************************** 1. row ***************************
       Table: test_ai
Create Table: CREATE TABLE `test_ai` (
  `id` int NOT NULL AUTO_INCREMENT,
  `alias` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `alias` (`alias`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

Replace с одним валидным значением.

mysql> replace into test_ai(alias) values ('alias1'), ('alias2'), ('alias3'), ('alias4');
Query OK, 7 rows affected (0.01 sec)
Records: 4  Duplicates: 3  Warnings: 0

mysql> show create table test_ai\G
*************************** 1. row ***************************
       Table: test_ai
Create Table: CREATE TABLE `test_ai` (
  `id` int NOT NULL AUTO_INCREMENT,
  `alias` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `alias` (`alias`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

При этом ID поменялись, что логично. Но про replace и его принцип работы, как-нибудь в другой раз.

mysql> select * from test_ai
    -> ;
+----+--------+
| id | alias  |
+----+--------+
| 13 | alias1 |
| 14 | alias2 |
| 15 | alias3 |
| 16 | alias4 |
+----+--------+

И еще раз сделаем replace.

mysql> replace into test_ai(alias) values ('alias1'), ('alias2'), ('alias3'), ('alias4');
Query OK, 8 rows affected (0.02 sec)
Records: 4  Duplicates: 4  Warnings: 0

mysql> select * from test_ai;
+----+--------+
| id | alias  |
+----+--------+
| 17 | alias1 |
| 18 | alias2 |
| 19 | alias3 |
| 20 | alias4 |
+----+--------+

Попробуем обработать ошибку вставки через 'on duplicate key update'

mysql> insert into test_ai(alias) values ('alias1'), ('alias2'), ('alias3') on duplicate key update id = id;
Query OK, 0 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> show create table test_ai\G
*************************** 1. row ***************************
       Table: test_ai
Create Table: CREATE TABLE `test_ai` (
  `id` int NOT NULL AUTO_INCREMENT,
  `alias` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `alias` (`alias`)
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Вывод: не зависит успешно или неуспешно вставлены данные, в любом случае auto_increment будет ползти вверх. А значит, при достаточных объёмах вставляемых данных (с любым результатом) auto_increment очень быстро может достичь максимального значения для первичного ключа. Кроме того, необходимо учитывать возможную неравномерность распределения значений первичного ключа, например, в случае если необходимости по нему итерироваться.

#mysql #оптимизация_запросов