Почему ID в БД идут не по порядку?

Question

[hrvasiliy]

Есть таблица в БД с 2-мя полями: ID (AI + PK) и NAME (INDEX UNIQUE). При добавлении первой партии записей в поле NAME все ID расставляются как надо, по порядку. После добавления второй партии записей (Есть повторы, но для этого я и установил INDEX UNIQUE) ID уже расставляются не по порядку: 98, 99, 115, 175 и т.д. Подскажите, пожалуйста, в чем же причина? Можно, конечно, делать проверку на уникальность того или иного поля в коде, но для чего тогда INDEX UNIQUE...

Comments

[Алексей Уколов]

Если вы полагаетесь на последовательность ID в своем коде, значит вы где-то что-то делаете не так.

[hrvasiliy]

Алексей Уколов: create table mytable(id bigint auto_increment primary key,name varchar(32) unique);

insert ignore into mytable(name) values('aaa'),('bbb'),('ccc'),('aaa'), ('ddd'),('eee'),('bbb'),('fff');

insert ignore into mytable(name) values('aaa'),('bbb'),('ccc'),('aaa'), ('ddd'),('eee'),('bbb'),('ggg');

select * from mytable;

s010.radikal.ru/i311/1502/01/4f1f8b89d8e9.png Что тут может быть не так?

[Алексей Уколов]

Чем вам мешают дыры в последовательности ID?

[hrvasiliy]

Алексей Уколов: Люблю когда все аккуратно, как минимум :)

[hrvasiliy]

Алексей Уколов: Я так понял тут 2 пути: 1. Создавать временную таблицу. 2. Делать запрос в коде на существование и записывать, если не существует. Так?

[Алексей Уколов]

3. Забить и заняться тем, что действительно имеет значение

Answer 1

[Тагир Валеев]

Я не наблюдаю эффекта ни с myisam, ни с innodb (версия сервера 5.0.45-community-nt MySQL Community Edition):

mysql> create table mytable(id bigint auto_increment primary key,name varchar(32) unique);
Query OK, 0 rows affected (0.01 sec)

mysql> insert ignore into mytable(name) values('aaa'),('bbb'),('ccc'),('aaa'),
mysql> ('ddd'),('eee'),('bbb'),('fff');
Query OK, 6 rows affected (0.02 sec)
Records: 8  Duplicates: 2  Warnings: 0

mysql> insert ignore into mytable(name) values('aaa'),('bbb'),('ccc'),('aaa'),
mysql> ('ddd'),('eee'),('bbb'),('ggg');
Query OK, 1 row affected (0.00 sec)
Records: 8  Duplicates: 7  Warnings: 0

mysql> select * from mytable;
+----+------+
| id | name |
+----+------+
|  1 | aaa  |
|  2 | bbb  |
|  3 | ccc  |
|  4 | ddd  |
|  5 | eee  |
|  6 | fff  |
|  7 | ggg  |
+----+------+
7 rows in set (0.00 sec)

Аналогично при вставке из файла:

C:\>type test.txt
aaaa
bbbb
bbbb
cccc
dddd
bbbb
aaaa
eeee

mysql> create table mytable(id bigint auto_increment primary key,name varchar(32) unique);
Query OK, 0 rows affected (0.00 sec)

mysql> load data local infile 'c:\\test.txt' ignore into table mytable(name);
Query OK, 5 rows affected (0.00 sec)
Records: 8  Deleted: 0  Skipped: 3  Warnings: 0

mysql> select * from mytable;
+----+------+
| id | name |
+----+------+
|  1 | aaaa |
|  2 | bbbb |
|  3 | cccc |
|  4 | dddd |
|  5 | eeee |
+----+------+
5 rows in set (0.00 sec)

Какая у вас версия MySQL и как конкретно вы вставляете?

Comments

[hrvasiliy]

5.6.16.
$query = "INSERT IGNORE INTO `test_table8` (`name`) VALUES ('$name')";
$mysqli->query($query);

[hrvasiliy]

Пересмотрел, увидел... Тогда я вообще не понимаю :)

[Тагир Валеев]

hrvasiliy: таки нет:

mysql> select * from mytable;
+----+------+
| id | name |
+----+------+
|  1 | aaaa |
|  2 | bbbb |
|  3 | cccc |
|  4 | dddd |
|  5 | eeee |
+----+------+
5 rows in set (0.00 sec)

mysql> insert ignore into mytable(name) values('aaaa');
Query OK, 0 rows affected (0.00 sec)

mysql> insert ignore into mytable(name) values('ffff');
Query OK, 1 row affected (0.00 sec)

mysql> select * from mytable;
+----+------+
| id | name |
+----+------+
|  1 | aaaa |
|  2 | bbbb |
|  3 | cccc |
|  4 | dddd |
|  5 | eeee |
|  6 | ffff |
+----+------+
6 rows in set (0.00 sec)

Видимо, в 5.6 поменяли. Ну Writerim правильно пишет, вставьте сперва во временную таблицу, а потом перекиньте в основную, если вам принципиально:

mysql>create temporary table test(name varchar(32) unique);
// здесь ваши вставки
mysql> create table mytable(id bigint auto_increment primary key,name varchar(32) unique);
mysql>insert into mytable(name) select * from test;
mysql>drop table test;

[hrvasiliy]

Попробовал в точности, как у вас:
create table mytable(id bigint auto_increment primary key,name varchar(32) unique);

insert ignore into mytable(name) values('aaa'),('bbb'),('ccc'),('aaa'), ('ddd'),('eee'),('bbb'),('fff');

insert ignore into mytable(name) values('aaa'),('bbb'),('ccc'),('aaa'), ('ddd'),('eee'),('bbb'),('ggg');

select * from mytable;

s010.radikal.ru/i311/1502/01/4f1f8b89d8e9.png

[FanatPHP]

еще один любитель ID по порядку... Надо бы на тостере ценз вести. И всех тех, кто не понимает, что такое уникальный идентификатор - в read-only. Пусть читают, ума набираются.

[Тагир Валеев]

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

Answer 2

[FanatPHP]

Как и многие начинающие, ты путаешь понятия "уникальный" и "красивенький по порядку".

Answer 3

[Сергей Иванов]

У вас autoincrement меняется. уникальные\не уникальные записи сдвигают его. А на каком "AUTO INCREMENT" срабатывает правило UNIQUE то на таком и запишется



Таблица test

Как вариант можно использовать TEMPORARY TABLE для записи в нее с правильным порядком и затем перенести из нее обратно

Comments

[hrvasiliy]

Да, не пишется, но сдвигается после совпадения и ID увеличивается... Можно ли этого избежать методами MySQL? Или же только проверять в коде перед записью?

[FanatPHP]

Это НЕ НУЖНО

Answer 4

[mgyk]

Вставка множества записей через VALUES меняет автоинкремент

mysql> SELECT AUTO_INCREMENT FROM information_schema.tables where table_schema = DATABASE() and table_name = 'test3';
+----------------+
| AUTO_INCREMENT |
+----------------+
|             16 |
+----------------+
1 row in set (0.00 sec)

mysql> insert ignore into test3(name) VALUES ('xxx'),('aaa'),('fff');
Query OK, 0 rows affected (0.00 sec)
Records: 3  Duplicates: 3  Warnings: 0

mysql> SELECT AUTO_INCREMENT FROM information_schema.tables where table_schema = DATABASE() and table_name = 'test3';
+----------------+
| AUTO_INCREMENT |
+----------------+
|             19 |
+----------------+

Comments

[hrvasiliy]

Серьезно? Есть способ это решить?)

[mgyk]

Думаю, что нет. Задача ИД быть уникальным, а не по-порядку