Запретить автоматическое добавление в двойную вставку MySQL

Используя MySQL 5.1.49, я пытаюсь внедрить систему тегов, у меня есть проблема с таблицей с двумя столбцами: id(autoincrement) , tag(unique varchar) (InnoDB)

При использовании запроса INSERT IGNORE INTO tablename SET tag="whatever" , значение auto increment id увеличивается, даже если вставка была проигнорирована.

Обычно это не проблема, но я ожидаю много возможных попыток вставить дубликаты для этой конкретной таблицы, что означает, что мое следующее значение для поля id новой строки будет слишком сильно переходить.

Например, я столкнулся с таблицей с тремя строками, но с плохой id

 1 | test 8 | testtext 678 | testtextt 

Кроме того, если я не выполняю INSERT IGNORE и просто выполняю INSERT INTO и обрабатываю ошибку, поле автоматического увеличения увеличивается, поэтому следующая истинная вставка по-прежнему является неправильным автоматическим приращением.

Есть ли способ остановить автоинкремент, если есть попытка повторной строки INSERT ?

Как я понимаю для MySQL 4.1, это значение не будет увеличиваться, но последнее, что я хочу сделать, – это либо сделать много предложений SELECT заранее, чтобы проверить, существуют ли tags, или, что еще хуже, понизить мою версию MySQL.

Вы можете изменить свой INSERT таким образом:

 INSERT INTO tablename (tag) SELECT $tag FROM tablename WHERE NOT EXISTS( SELECT tag FROM tablename WHERE tag = $tag ) LIMIT 1 

Где $tag – тег (правильно цитируемый или как заполнитель, конечно), который вы хотите добавить, если он еще не существует. Такой подход даже не вызовет INSERT (и последующие потери автоинкремента), если тег уже существует. Вероятно, вы могли бы получить более хороший SQL, чем тот, но выше это должно было сделать трюк.

Если ваша таблица правильно проиндексирована, то дополнительная проверка SELECT для проверки существования будет быстрой, и в любом случае базе данных придется выполнить эту проверку.

Однако этот подход не будет работать для первого тега. Вы можете засеять таблицу тегов тегом, который, по вашему мнению, всегда будет использоваться, или вы можете сделать отдельную проверку для пустой таблицы.

Документация MySQL для версии 5.5 гласит:

 "If you use INSERT IGNORE and the row is ignored, the AUTO_INCREMENT counter is **not** incremented and LAST_INSERT_ID() returns 0, which reflects that no row was inserted." 

Ссылка: http://dev.mysql.com/doc/refman/5.5/en/information-functions.html#function_last-insertid

Поскольку версия 5.1 InnoDB имеет настраиваемую блокировку автоинкремента. См. Также http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html#innodb-auto-inc

Обход проблемы: используйте параметр innodb_autoinc_lock_mode = 0 (традиционный).

Я только что нашел этот камень …

http://www.timrosenblatt.com/blog/2008/03/21/insert-where-not-exists/

 INSERT INTO [table name] SELECT '[value1]', '[value2]' FROM DUAL WHERE NOT EXISTS( SELECT [column1] FROM [same table name] WHERE [column1]='[value1]' AND [column2]='[value2]' LIMIT 1 ) 

Если affectedRows = 1, то он вставлен; иначе, если affectedRows = 0, был дубликат.

Я нашел mu слишком короткий ответ полезен, но ограничил, потому что он не делает вставок на пустой стол. Я нашел простую модификацию:

 INSERT INTO tablename (tag) SELECT $tag FROM (select 1) as a #this line is different from the other answer WHERE NOT EXISTS( SELECT tag FROM tablename WHERE tag = $tag ) LIMIT 1 

Замена таблицы в предложении from с помощью «поддельной» таблицы (select 1) as a разрешенной части для возврата записи, которая позволила вставить. Я запускаю mysql 5.5.37. Спасибо, му, за то, что он доставил мне большую часть пути …

Вы всегда можете добавить ON DUPLICATE KEY UPDATE Читайте здесь (не совсем, но решает вашу проблему).

Из комментариев, автор @ravi

Независимо от того, происходит ли приращение или нет, зависит от параметра innodb_autoinc_lock_mode. Если задано ненулевое значение, счетчик автоинкремента будет увеличиваться, даже если загорается клавиша ON DUPLICATE KEY

У меня была такая же проблема, но я не хотел использовать innodb_autoinc_lock_mode = 0, поскольку мне казалось, что я убиваю муху с помощью гаубицы.

Чтобы решить эту проблему, я закончил использование временной таблицы.

 create temporary table mytable_temp like mytable; 

Затем я вставил значения с помощью:

 insert into mytable_temp values (null,'valA'),(null,'valB'),(null,'valC'); 

После этого вы просто делаете другую вставку, но используйте «не в», чтобы игнорировать дубликаты.

 insert into mytable (myRow) select mytable_temp.myRow from mytable_temp where mytable_temp.myRow not in (select myRow from mytable); 

Я не тестировал это для производительности, но он выполняет эту работу и легко читается. Конечно, это было важно только потому, что я работал с данными, которые постоянно обновлялись, поэтому я не мог игнорировать пробелы.

Принятый ответ был полезен, однако при использовании его я столкнулся с проблемой, что в основном, если в вашей таблице не было записей, это не сработало бы, поскольку выбор использовался с использованием данной таблицы, поэтому вместо этого я придумал следующее, которое будет вставляться, даже если таблица пуста, вам также нужно, чтобы вы вставляли таблицу в 2 места и вставляя переменные в 1 место, чтобы не ошибиться.

 INSERT INTO database_name.table_name (a,b,c,d) SELECT i.* FROM (SELECT $a AS a, $b AS b, $c AS c, $d AS d /*variables (properly escaped) to insert*/ ) i LEFT JOIN database_name.table_name o ON ia = oa AND ib = ob /*condition to not insert for*/ WHERE oa IS NULL LIMIT 1 /*Not needed as can only ever be one, just being sure*/ 

Надеюсь, вы найдете ее полезной

Я просто добавляю дополнительную инструкцию после запроса на вставку / обновление: ALTER TABLE table_name AUTO_INCREMENT = 1 Затем он автоматически выбирает самый высокий идентификатор первого ключа плюс 1.

Старый запрос, который увеличивает число автоматического увеличения:

 INSERT INTO [my_table_name] (column1, column2, column3, column4) VALUES ('[col_value1]', '[col_value2]', '[col_value3]', '[col_value4]'); 

Новый запрос, чтобы проверить, существует ли значение для 1 столбца:

 INSERT INTO [my_table_name] (column1, column2, column3, column4) SELECT '[col_value1]', '[col_value2]', '[col_value3]', '[col_value4]' FROM DUAL WHERE NOT EXISTS( SELECT [column1] FROM [my_table_name] WHERE [column1]='[col_value1]' LIMIT 1 ); 

Новый запрос для проверки наличия значений для двух или более столбцов:

 INSERT INTO [my_table_name] (column1, column2, column3, column4) SELECT '[col_value1]', '[col_value2]', '[col_value3]', '[col_value4]' FROM DUAL WHERE NOT EXISTS( SELECT [column1], [column2] FROM [my_table_name] WHERE [column1]='[col_value1]' AND [column2]='[col_value2]' LIMIT 1 ); 

Когда $mysqli->affected_rows возвращает 1, он был вставлен, иначе был дубликат.

  • Load_File не работает
  • Удаленный сервер Mysql с правами на WMI
  • Преобразовать временную метку Unix в человеко-читаемую дату с использованием MySQL
  • Java + Tomcat, Умирающее соединение с базой данных?
  • предоставить удаленный доступ к базе данных MySQL с любого IP-адреса
  • SELECT * FROM из нескольких таблиц. MySQL
  • Сравнение полнотекстовой поисковой системы - Lucene, Sphinx, Postgresql, MySQL?
  • Как избежать зарезервированных слов, используемых в качестве имен столбцов? MySQL / Создать таблицу
  • ДО и DONT для индексов
  • Как исправить ошибки «Неверное строковое значение»?
  • Как я могу перебирать все строки таблицы? (Баз данных)
  • Interesting Posts

    Как выбрать кросс-энтропийную потерю в тензорном streamе?

    Имеет ли код состояния HTTP значение 0?

    Ошибка при запуске приложения для Android. Не удалось инициализировать эмуляцию OpenglES, используйте «-gpu off», чтобы отключить ее

    VBA – обновление других ячеек с помощью пользовательской функции

    Где можно загрузить JSTL jar

    AngularJS. Когда выбрано атрибут ng-model, ng-selected не работает

    Ошибка HTTP 403.14 – Запрещено Веб-сервер настроен так, чтобы не отображать содержимое

    Телевизионные записи WTV и DVR-MS, следует ли я де-чередовать или нет?

    Atikmdag.sys + 127f8 BSOD с драйвером режима ядра ATI Radeon

    Преобразование строки в C ++ в верхний регистр

    Автоматическое повторное подключение SSH-соединений с определенным сеансом «экрана»

    Изменение локали в самом приложении

    Будет ли MacBook Pro 2010 без SATA III выиграть от SSD с пропускной способностью 6 Гбит / с?

    Обработка тайм-аута сеанса в вызовах ajax

    Как найти размер int ?

    Давайте будем гением компьютера.