Огромное тебе спасибо, SkyBoy!

Там еще било несколько вопросов 

• Тип int имеет предел в положительной зоне до числа 2147483647, т.е. фактически атрибут с типом INT может содержать 2147483647 записей, теперь вопрос, - что будет если мы добавим новую 2147483648ю запись, что сделает AUTO_INCREMENT в таком случае?
• Как в итоге, все таки, должен выглядеть SQL-запрос к вопросу по данной теме?
• Почему ты имена таблиц в SQL запросах пишеш в кавычках?

Заранее, спасибо, вот дотяну до 100 постов, вляплю тЯбе огромный +!


Это сообщение отредактировал(а) Volter - 17.4.2007, 23:11

скорее всего, будет переполнение.
но не часто бывают случаи с 2 миллиардами записей в таблице. Даже когда бывают  - можно наваять составные ключи, первая часть которого будет автоинкрементной, а вторая - обновляться триггером при вставке новой записи... или перейти к строковым ключам неограниченной длины(потенциально скорость ниже) или ещё чего. Возможно - пересмотреть структуру БД на предмет адекватности.
Если ты имеешь в виду проблему, что после очистки БД счетчик автоинкремента не сбрасывается, то такая конструкция:

но надо протестировать - такую конструкцию использовать не доводилось, могу ошибаться  Но конструкция для принудительной установки счетчика автоинкрементного поля точно есть   

Я имелл ввиду что хотелось бы чтобы AUTO_INCREMENT был более рационален в добавлении индекса.

Согласись, что если б, AUTO_INCREMENT добавлял не какоето число из таблицы + 1, а анализировал свободные места то весьма рациональнее былобы с точки зрения, экономии памяти и упорядоченности индексов.

Там еще било несколько вопросов 

• Как в итоге, все таки, должен выглядеть SQL-запрос к вопросу по данной теме?
• Почему ты имена таблиц в SQL запросах пишеш в кавычках, эт что синтаксис новый какой?

Вот дотяну до 100 постов, вляплю тЯбе огромный +!

  

"затыкания" дырок вообще не должно быть по моему мнению.
приведенный мною запрос работает у меня на 5.2(выяснил-таки версию).
приведенный muzer'ом запрос должен работать и на более ранних версиях(только добавить "мой" union для "первой" записи)

и имена полей, и псевдонимы. только подчеркну - в обратных апострофах(что на одной кнопке с буквой "Ё" находится). А делаю это так потому, что в такой форме имена таблиц и полей никак не перепутаются с зарезервированными словами("SELECT group FROM select" будет тебе ругаться при выполнении, даже если у тебя есть таблица SELECT с полем, названным "group" ) и с функциями(можно использовать поле max и СУБД не будет путать это обращение с агрегирующей функцией).
А в кавычках пишутся только строковые литералы ;)

  

Все ЗАПИСАЛ, Все на ус намотал!

Респект SkyBoy!

видимо, объяснение про связанные таблицы ты не понял... 
смотри, есть таблица people:

есть таблица phones - номера телефонов

есть таблица people_phones, в которой хранятся соответствия между людьми и номерами телефонов(у нескольких людей могут быть одинаковый номер телефона - супруги, например; у одного человека может быть несколько телефонов - рабочий, домашний, мобильный...)

представь, что каким-то образом запись о человеке с номером 4 была удалена из таблицы people, а вот из people_phones записи, связанные с этим человеком, не удалили. Что произойдет, если "инетеллектуальный auto_increment" создаст нового человека не с idman, скажем, 25(потому что последнее ещё не использованное число), а со значением 4, потому что у нас так "окно"? Правильно, только что "созданный" человек сразу же "обретет" телефоны, к которым не имеет ни малейшего отношения. а представь, что такая бага вылезет не во время тестирования(когда данные набиваются "от балды"), а в процессе эксплуатации системы. И что - как ты отделишь потом "правильные" данные от "неправильных"? Все придется удалять? Ради сомнительного выигрыша в максимальном значении первичного ключа, получаешь очень неприятную проблему. Так смысл танцевать с бубном?

Что так агрессивно то?  

Я прекрасно понимаю связи таблиц, и во время удаления такого пользователя удалю и связи с ним.

Вот допустим:

people
+-------+------+
| idMan | SNSn |
+-------+------+
|  1    | ФИО  |
|  2    | ФИО  |
|  3    | ФИО  |
|  4    | ФИО  |
+-------+------+

phones
+----------+--------+
| idPhones | number |
+----------+--------+
|  1       | 464646 |
|  2       | 468964 |
|  3       | 777777 |
|  4       | 577567 |
|  5       | 675577 |
|  6       | 655666 |
+----------+--------+

people_phones
+-------+---------+
| idman | idphone |
+-------+---------+
|  1    | 3       |
|  1    | 6       |
|  2    | 1       |
|  3    | 2       |
|  3    | 4       |
|  3    | 5       |
|  4    | 2       |
+-------+---------+


Как видим, мы имеем 4 пользователей, 6 номеров телефонов.

Пользователь с idMan=1 имеет номероки 777777 и 655666
Пользователь с idMan=2 имеет номерок 464646
Пользователь с idMan=3 имеет номероки 468964, 577567 и 675577
Пользователь с idMan=4 имеет номерок такой же как и у пользователя idMan=3 т.е. 468964


Удаляем пользователя idMan=3 и связи с ним так:


people
+-------+------+
| idMan | SNSn |
+-------+------+
|  1    | ФИО  |
|  2    | ФИО  |
|  4    | ФИО  |
+-------+------+

phones
+----------+--------+
| idPhones | number |
+----------+--------+
|  1       | 464646 |
|  2       | 468964 |
|  3       | 777777 |
|  4       | 577567 |
|  5       | 675577 |
|  6       | 655666 |
+----------+--------+

people_phones
+-------+---------+
| idman | idphone |
+-------+---------+
|  1    | 3       |
|  1    | 6       |
|  2    | 1       |
|  4    | 2       |
+-------+---------+


Теперь добавляем пользователя с помощью, как ты там назвал мою идею ), - "инетеллектуальный auto_increment" )
И получаем это:

idMan=3 Это НОВЫЙ ПОЛЬЗОВАТЕЛЬ с идом 3 а не 5 как бы сделал AUTO_INCREMENT, и я знаю не надо было мне впервый раз говорить, - что если мы все удалим и создадим запись в people то AUTO_INCREMENT присвоил бы 5, не на столько я туп, я знаю что ведется отдельная таблица для ИНКРИМЕНТИРОВАНИЯ столбца )

people
+-------+------+
| idMan | SNSn |
+-------+------+
|  1    | ФИО  |
|  2    | ФИО  |
|  3    | ФИО  |  
|  4    | ФИО  |
+-------+------+

# Номерки теже, зачем их удалять )

phones
+----------+--------+
| idPhones | number |
+----------+--------+
|  1       | 464646 |
|  2       | 468964 |
|  3       | 777777 |
|  4       | 577567 |
|  5       | 675577 |
|  6       | 655666 |
+----------+--------+

# И связей старых нет для старого пользователем который у нас был под idMan=3

people_phones
+-------+---------+
| idman | idphone |
+-------+---------+
|  1    | 3       |
|  1    | 6       |
|  2    | 1       |
|  4    | 2       |
+-------+---------+

Я не прав?  

Это сообщение отредактировал(а) Volter - 18.4.2007, 00:14

К сведению собравшихся - есть ещё тип bigint. Если делать unsigned, то максимум - 18446744073709551615   Но это паранойя, заполнить unsigned int очень и очень сложно. Нужно понимать, что только очень грамотно сработанные скрипты, программы и архитектуры работают продолжительное время без серьёзных изменений. Т.е. фактически через n дней/недель/месяцев вы скажете TRUNCATE на своей таблице и auto_increment пойдёт с нуля либо вообще откажетесь от этой таблицы, разработав новое приложение 



Использование памяти, а точнее сказать дискового места, здесь совершенно не причём. Свободное место MySQL и так анализирует, и внутри своих файлов описанные вами правила пытается соблюдать. Что же касается значений автоинкремента, то как уже skyboy неоднократно сказал, они должны быть строго уникальны, это гарантия правильности содержащихся в бд данных. При частых удалениях из таблицы, полезно иногда выполнять запрос OPTIMIZE TABLE table_name - он как раз перестраивает таблицу таким образом, чтобы данные и индекс были упорядочены и не занимали лишнего места. А вот запись новых строк с ранее удалёнными значениями автоинкремента совершенно не гарантирует вам того же.

MUZER, не надо писАть лиш бы только писАть )

Знаем мы тут все про bigint unsigned!



И эт мы тож знаем, просто задайтесь вопросом, красивого подхода к коду, не просто написать, а красиво написать!



Да, а если вы заранее непродумали ТРИГЕРЫ(как мне стало о них известно от SkyBoy, вечная слава ему!) на удаление или изменение данных и у вас есть Вторичные(Внешние) ключи на таблицу в которой Вы провели OPTIMIZE TABLE? Или вы выполнили  OPTIMIZE TABLE а не поправили Вторичные ключи в других таблицах ссылающиеся на данную? Этож верный путь к ошибке!

  

Полный бред.

К Вашему сведению, красиво написать можно и глупость ;).


Каким образом? Проясните, если не затруднит, для тугодумов вроде меня...

сам-то - чего агрессивный такой?  насчет "smart auto_increment": я не говорю, что обязательно будут грабли, что карма вынудит совершить ошибку при удалении и т.д.. и т.п., я только хотел заметить, что граблей при таком подходе хватает. А пользы(кроме "красивости" для человека) я, чесно говоря, не вижу. Если можешь - приведи пример, пожалуйста, когда без конструкции такой обойтись просто нельзя. Или очень сложно.
К слову о триггерах. У них есть несколько(на мой взгляд) существенных недостатков:
1. только два на каждое действие(в MySql версии 5.1) - так что при разработке в процессе коррекции логии возможно придется часто менять содержимое, что чревато внесением ошибок. Да и триггеры, описанные разработчиками двух скриптов на одну таблицу будут конфликтовать - придется разруливать вручную. Мне больше нравится механизм в InteRBase/FireBird, где возможных триггеров намного больше, а порядок задаешь самостоятельно.
2. скорость работы ниже чем у foreign keys. Но порою foreign keys не позволяет реализовать кое-что, что вполне работает на триггерах(на языке крутится пример удаления из таблицы, моделирующей дерево, родителя вместе с потомками, но я не помню, где происходили события - на InteRBase или на MYSQL).
Так что сильно увлекаться триггерами, пожалуй, не стОит   

НЕТ, не путь. А если и путь - то к светлому коммунизму, а не к ошибке. Как мне подсказывает ман, optimize table - это перестройка индекса(переупорядочивание) и физическое перемещение данных, чтоб избежать из кластеризации("разлития" по всему файлу-контейнеру). Т.е. все значения(в т.ч. и автоинкрементные) и их типы(логика) остаются неизменными - меняется только хранение этой логики в памяти для ускорения доступа. И, возможно, уменьшения размера(возможно, имеется в виду записи индексов, отсуствующие в таблице, которые можно удалить - неспроста семантика optimizre такоВА, что сначала вызывается repair!).

skyboy, я тебе очень благодарен, за твою лояльность по отношению ко мне.

Огромное тебе спасибо!

Ты терпелив к вопросам человека который взялся за SQL, просто ОГРОМНОЕ тебе спасиб!



Великая глупость заключается не в том, что по незнанию задают глупость, а в том чтобы смеяться над тем кто задает эту глупость по незнанию.
Norbert Wiener

Добавлено через 2 минуты и 19 секунд


Жизнь тоже глупость по своей сути, если разобраться в деталях, но живем же.    

а что мешает использовать unsigned int - 4294967295 или даже unsigned bigint - 18446744073709551615?
http://dev.mysql.com/doc/refman/5.1/en/numeric-types.html

выбор типа и размера поля определяется при анализе задачи. если вам не хватает unsigned bigint стоит задуматься о смене субд или пересмотреть структуру данных

Это сообщение отредактировал(а) Glip - 18.4.2007, 09:41

2All
Вот, елы-палы, нашли занятие. Маленькое упражнение в арифметике: пусть есть 100 юзеров, работают 24 часа в сутки 365 дней в году, вставляют по 10 записей в минуту. Вопрос - когда иссякнет AUTO_INCREMENT у int / bigint?
А попытки заполнять "дыры" у этих полей - верный способ заиметь проблемы потому, что если эту "дыру" вычислять, то любые 2 юзера получат одно и то же значение "дыры", пока ее кто-то не заполнит. И соответственно "заполнят" ее по очереди. А с учетом commit/rollback все становится еще печальней. И никакие "специальные" поля от этого не спасут по причинам, аналогичным тем, что возникают у FoxPro, например, при попытке соорудить на нем многопользовательское приложение.

Volter, 
Вместо "фантазии на вольную тему" рекомендую почитать классиков (от SQL), а не  изобретать велосипед с квадратными колесами, считая что он красив. Тем более что новичкам это просто предписано: изучать предмет по докам, а не заниматься изобретательством.

Добавлено через 2 минуты и 14 секунд
muzer, 

Исключительно верно подмечено.

Добавлено через 6 минут и 20 секунд
2All:
Если кто-то таки желает избавиться от "дыр", то единственное решение - отрубить всех юзеров на время этой процедуры и накатать соответствующие скрипты, чтобы сам сервер перемещал на "дыру" следующую за ней запись и т.д. Привда при больших таблицах - успеете уйти к окончанию работы скрипта на пенсию .