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

Ответ 1

Вы можете изменить свой 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 для проверки существования будет быстрой, и в любом случае базе данных придется выполнить эту проверку.

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

Ответ 2

Документация 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-insert-id

Так как версия 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 (традиционный).

Ответ 3

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

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, был дубликат.

Ответ 4

Я нашел, что 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. Спасибо mu за то, что он доставил мне большую часть пути....

Ответ 5

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

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

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

Ответ 6

У меня была такая же проблема, но я не хотел использовать 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);

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

Ответ 7

Принятый ответ был полезен, однако при использовании его я столкнулся с проблемой, что в основном, если в вашей таблице не было записей, это не сработало бы, поскольку выбор использовался с использованием данной таблицы, поэтому вместо этого я придумал следующее: вставьте, даже если таблица пуста, вам также нужно только вставить таблицу в 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 i.a = o.a AND i.b = o.b /*condition to not insert for*/
WHERE
    o.a IS NULL
LIMIT 1 /*Not needed as can only ever be one, just being sure*/

Надеемся, что вы сочтете это полезным

Ответ 8

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

Ответ 9

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

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, он был вставлен, иначе был дубликат.