Версия для печати темы Нажмите сюда для просмотра этой темы в оригинальном формате

Форум программистов > PHP: Базы Данных > Упорядочивание auto_increment поля

Автор: Kremnik 28.3.2009, 00:21

Проблема такая: Есть поле id, стоящее с auto_increment. Например: Код 1. Data 1 2. Data 2 3. Data 3 ... 100. Data 100 Но если я удаляю запись с, например, id=2 Код 1. Data 1 3. Data 3 ... 100. Data 100 а потом вставляю новую запись, то она вставляется не в конец, как должна, а на место удалённой записи, т.е.: Код 1. Data 1 101. Data 101 3. Data 3 ... 100. Data 100 В чём может быть проблема и как её решить?

Автор: ksnk 28.3.2009, 00:32

У таблицы есть параметр - AUTO_INCREMENТ. Его можно установить при создании таблицы и http://www.mysql.ru/docs/man/SET_OPTION.html Код SET INSERT_ID=# ALTER TABLE t2 MODIFY ... AUTO_INCREMENТ Судя по всему, при создании таблицы он не был установлен, вот и выбирался первый попавшийся в "дырку" параметр.

Автор: bars80080 28.3.2009, 00:47

да нет, он же говорит, что авто_инкремент есть, просто запись с id=101 становится на место удалённой. но для упорядоченной выборки существует сортировка, т.е. достаточно выбирать  Код select * from table order by id а какое положение занимает физически строчка в БД роли не играет

Автор: Kremnik 28.3.2009, 00:57

В смысле не играет? Если у меня например таблица комментариев, и надо выбрать 10 последних. Перед этим последний комментарий встал на место удалённого под номером 2. Т.е. надо всегда сортировать получаеся?

Автор: ksnk 28.3.2009, 01:04

bars80080, у ТАБЛИЦЫ есть такой параметр. У таблицы может быть только одно автоинкрементное поле, так что параметр принадлежит именно таблице. Он задается при создании таблицы Код CREATE TABLE tab (   `id` int(10) NOT NULL auto_increment, ... ) ENGINE=MyISAM AUTO_INCREMENT=4664  ; либо его можно поменять, в том числе и при существующем поле. Код SET INSERT_ID=4664 ; ALTER TABLE tab MODIFY `id` int(10) NOT NULL auto_increment ; Следующая вставленная запись будет с `id` 4665

Автор: StachelDraht 28.3.2009, 01:08

может дело в первичном ключе?

Автор: Kremnik 28.3.2009, 01:21

Нет, точно не первичный ключ, уже пробовал.  Что то я не понял про установление ai на отдельное поле таблицы...т.е. надо сначала создать поле, а потом уже к нему отдельно ai добавлять? Если так, то уже пробовал, не получается. Но не сортировать же каждый раз перед запросом...

Автор: ksnk 28.3.2009, 01:39

O! Чего-то в той документации криво написано... :-( Вот так - заработало... Код ALTER TABLE tab AUTO_INCREMENT = 4456;

Автор: Kremnik 28.3.2009, 01:44

Извините, но что значит  Код auto_increment = номер ?

Автор: IZ@TOP 28.3.2009, 02:22

Цитата(Kremnik @  28.3.2009,  02:44 ) Извините, но что значит  Выделить всёкод SQL 1:      auto_increment = номер ?  Это значит, что следующее значение поля auto_inrement будет "номер+1".

Автор: Kremnik 28.3.2009, 14:31

Ок, спасибо. Но всё же, это же, получается, никак не автоматизировать. Т.е. так и так при удалении записи, а потом при вставке новой записи она будет вставать на место удалённой. Т.е. единственный способ нормально сортировать записи по ai-полю - каждый раз перед основным запросом отправлять запрос на сортировку по ai-полю. Только проблема в том, что у меня в базе, например, ~3000 записей. Тяжеловато каналу пользователя будет

Автор: bars80080 28.3.2009, 20:58

Цитата(Kremnik @  28.3.2009,  13:31 ) каждый раз перед основным запросом отправлять запрос на сортировку по ai-полю. Только проблема в том, что у меня в базе, например, ~3000 записей. Тяжеловато каналу пользователя будет в смысле? причём здесь канал? написал один запрос, а они в БД сами отсортируются и выйдут одной выборкой. тут хоть миллион записей

Автор: Kremnik 29.3.2009, 00:33

Представим действия пользователя: 1) Заходит на страницу статьи 2) Смотрит к ней комментарии 3) Пишет свой 4) После него другой пользователь добавляет ещё один комментарий 5) Первый пользователь удаляет свой комментарий и пишет новый 6) Комментарий встаёт не на последнее место, а на место его предыдущего комментария 7) Вывод комментариев и пользователь замечает, что его комментарий встал не последним В этой время действия сервера: 1) Выборка статьи 2) SELECT * FROM comments ORDER BY id 3) Вставка записи в таблицу с комментариями (INSERT INTO comments (1,2,3) VALUES (a,b,c) 4) Вставка другого комментария другим пользователем (INSERT...) 5) DROP...; INSERT... 6-7) SELECT * FROM comments ORDER BY id А вы предлагаете перед скриптом в начале скрипта сделать выборку всех комментариев и отсортировать их по id и при этом сделать это незаметно (в смысле скорости загрузки страницы) для пользователя. А теперь представим, что у меня 100000 комментариев.

Автор: ksnk 29.3.2009, 01:49

Kremnik, Сейчас база "неправильная". Ее можно исправить, если сделать в PHPMyAdmin'е, во вкладке "SQL" вот такое Код SELECT MAX(`id`) INTO @tags FROM `MyTable`; SET @s = CONCAT("ALTER TABLE `MyTable` AUTO_INCREMENT=", @tags ); PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt; Общий смысл - ищем максимальное значение поля `id` и устанавливаем параметр автоинкремента в этой таблице в нужное значение. После этого все новые записи будут начинаться с индексов, которые еще не встречались в базе. Независимо от того, удалял юзер записи или нет. Возможно я перемудрил с SQL'ем, выглядит несколько гемороисто, но подставить в запрос значение переменной у меня не получилось. Во всяком случае - так работает

Автор: IZ@TOP 29.3.2009, 03:21

Цитата(Kremnik @  28.3.2009,  15:31 ) Ок, спасибо. Но всё же, это же, получается, никак не автоматизировать. Т.е. так и так при удалении записи, а потом при вставке новой записи она будет вставать на место удалённой. О_о это как такое может быть? Волшебство?    *спустя некоторое время дошло* попробуйте пересоздать таблицу используя приведенный ниже DDL напрямую через консоль. Не помню из-за чего такое бывает, возможно из-за указания специфичных свойств ключей. Цитата(ksnk @  29.3.2009,  02:49 ) Общий смысл - ищем максимальное значение поля `id` и устанавливаем параметр автоинкремента в этой таблице в нужное значение. После этого все новые записи будут начинаться с индексов, которые еще не встречались в базе. Независимо от того, удалял юзер записи или нет. о_О а зачем это надо? Извращенцы *нервно поглядывает в сторону постера*    Может, конечно, я не в теме, но самая простая таблица коментов и выборки из нее выглядят примерно так: Код CREATE TABLE `comment` (   `id` int(11) NOT NULL auto_increment,   `user_id` int(11) NOT NULL,   `post_id` int(11) NOT NULL,   `text` text,   `date` datetime default NULL,   PRIMARY KEY  (`id`),   KEY `sort` (`post_id`,`date`) ) user_id - идентификатор запостившего коммент. post_id - комментируемый объект (пост в блоге, например). Ключ sort, для выборки по post_id и сортировке по полю date. Если хотите сортировать по ID, просто измените DDL. Ключ двойной создан для того, чтобы для сортировки использовался индекс. Как выбрать записи и отсортировать Код SELECT * FROM comment WHERE post_id  = :post_id ORDER BY `id` DESC LIMIT 50, 10; EXPLAIN этого запроса будет примерно таким: Цитата mysql> EXPLAIN SELECT * FROM comment WHERE post_id  = 67357 ORDER BY `date` DESC LIMIT 50, 10; +----+-------------+---------+-------+---------------+------+---------+------+------+-------------+ | id | select_type | table   | type  | possible_keys | key  | key_len | ref  | rows | Extra       | +----+-------------+---------+-------+---------------+------+---------+------+------+-------------+ |  1 | SIMPLE      | comment | range | sort          | sort | 4       | NULL |  122 | Using where | +----+-------------+---------+-------+---------------+------+---------+------+------+-------------+ Если у вас есть необходимость удалять комментарии по полю user_id, создайте для этого соответствующий индекс. Примечание. Если не использовать LIMIT, filesort будет использоваться. Цитата mysql> EXPLAIN SELECT * FROM comment WHERE post_id  = 67357 ORDER BY `date` DESC; +----+-------------+---------+------+---------------+------+---------+------+------+-----------------------------+ | id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra                       | +----+-------------+---------+------+---------------+------+---------+------+------+-----------------------------+ |  1 | SIMPLE      | comment | ALL  | sort          | NULL | NULL    | NULL |   93 | Using where; Using filesort | +----+-------------+---------+------+---------------+------+---------+------+------+-----------------------------+

Powered by Invision Power Board (http://www.invisionboard.com)
© Invision Power Services (http://www.invisionpower.com)