Як в MySQL вставити, якщо не існує?

Я почав з гуглення і знайшов цю статтю, яка розповідає про м'ютексні таблиці.

У мене є таблиця з ~14 мільйонами записів. Якщо я хочу додати більше даних у тому ж форматі, чи є спосіб переконатися, що запис, який я хочу вставити, вже не існує без використання пари запитів (тобто один запит для перевірки, а інший для вставки, якщо результуючий набір порожній)?

Чи гарантує "унікальне" обмеження на поле, що вставка не вдасться, якщо воно вже існує?

Здається, що з просто обмеженням, коли я видаю вставку через php, скрипт скрипить.

warren
Edited question 27-го березня 2018 в 9:00
Програмування
sql php mysql performance primary-key
1-го вересня 2009 в 8:56
18 погляди
Рішення

use `INSERT IGNORE INTO table

див.

також існує синтаксис INSERT ... ON DUPLICATE KEY UPDATE, пояснення можна знайти на dev.mysql.com

Пост з bogdan.org.ua згідно з Google's webcache:

18 жовтня 2007 року

Для початку: станом на останню версію MyScore.ua; Для початку: починаючи з останньої версії MySQL, синтаксис, представлений в заголовку, не є можливий. Але є кілька дуже простих способів досягти того, що очікується очікується, використовуючи існуючий функціонал. Існує кілька простих способів досягти того, що очікується, використовуючи існуючий функціонал; Існує 3 можливих рішення: з використанням INSERT IGNORE, REPLACE або INSERT ... ON DUPLICATE KEY UPDATE. Пояснюємо; Уявімо, що у нас є таблиця: ..; CREATE TABLE transcripts ( ensembl_transcript_id varchar(20) NOT NULL, transcript_chrom_start int(10) unsigned NOT NULL, transcript_chrom_end int(10) unsigned NOT NULL, PRIMARY KEY (ensembl_transcript_id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

"Включення

І так далі; Тепер уявімо, що у нас є автоматичний конвеєр, який імпортує стенограми мета-даних з Ensembl, і що з різних причин конвеєр може обірватися на будь-якому кроці виконання. Таким чином, нам потрібно забезпечити дві дві речі: 1) багаторазові виконання конвеєра не зруйнують нашу базу даних, і 2) повторні виконання не призведуть до загибелі через помилку "дублікату первинного ключа". Як це зробити? Спосіб 1: використання REPLACE REPLACE; Все дуже просто:

REPLACE INTO transcripts; > REPLACE INTO transcripts SET ensembl_transcript_id = 'ENSORGT00000000001', transcript_chrom_start = 12345, transcript_chrom_end = 12678;

Якщо запис закінчився; Якщо запис існує, то він буде перезаписаний, якщо ще не існує не існує, то він буде створений. Однак, використання цього методу не є ефективним для нашого випадку: нам не потрібно перезаписувати існуючі записи, достатньо просто пропустити їх. Вирішуємо проблему; Спосіб 2: використання INSERT IGNORE Також дуже простий:

INSERT IGNORE INTO transcripts; > INSERT IGNORE INTO transcripts; > SET enscripts SET ensembl_transcript_id = 'ENSORGT00000000001', transcript_chrom_start = 12345, transcript_chrom_end = 12678;

Тут, якщо 'ensembl_transcript_id' вже присутній в базі даних базі даних, то він буде мовчки пропущений (проігнорований). (Якщо бути більш точним, ось цитата з довідника по MySQL: "Якщо ви використовуєте ключове слово IGNORE ключове слово IGNORE, помилки, що виникають при виконанні інструкції INSERT замість цього розглядаються як попередження. Наприклад, без IGNORE рядок, який дублює існуючий в таблиці УНІКАЛЬНИЙ індекс або значення PRIMARY KEY викликає помилку повторення ключа і виконання оператора переривається."). Якщо запис запис ще не існує, то він буде створений. Другий спосіб; Цей другий спосіб має кілька потенційних недоліків, серед яких не відміна запиту в разі виникнення будь-якої іншої проблеми (див. інструкцію). Таким чином, його слід використовувати в тому випадку, якщо попередньо було проведено тестування без ключа ключового слова IGNORE. Ігнорувати; Існує ще один варіант: використовувати INSERT ... ON DUPLICATE KEY UPDATE синтаксис, а в частині UPDATE просто нічого не робити, зробити якусь безглузду (порожню) операцію, на кшталт обчислення 0+0 (Джеффрей пропонує робити так присвоєння id=id, щоб механізм оптимізації MySQL ігнорував цю операцію). Перевагою цього методу є те, що він ігнорує тільки повторювані ключові події, а на інших помилках продовжує виконання. Наостанок зазначимо, що в цьому методі не можна ігнорувати повторювані ключові події; Наостанок: цей пост був натхненний Xaprb. Я б також порадив ознайомитися з іншим його постом про написання гнучких SQL-запитів.

Michel de Ruiter
Edited answer 16-го травня 2019 в 2:20
Коментарі (16)

on duplicate key update або insert ignore можуть бути життєздатними рішеннями для MySQL.

**Приклад оновлення on duplicate key update на основі mysql.com

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

UPDATE table SET c=c+1 WHERE a=1;

Приклад вставити ігнорувати на основі mysql.com

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [(col_name,...)]
    {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

Або:

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    SET col_name={expr | DEFAULT}, ...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

Або:

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [(col_name,...)]
    SELECT ...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]
Sruit A.Suk
Edited answer 16-го вересня 2015 в 5:36
Коментарі (0)

Будь-яке просте обмеження повинно виконувати роботу, якщо виняток є прийнятним. Приклади

  • первинний ключ, якщо не сурогатний
  • унікальне обмеження на стовпець
  • унікальне обмеження для декількох стовпців

Вибачте, це здається оманливо простим. Я знаю, що це виглядає погано на фоні посилання, яким ви поділилися з нами ;-(

Але я все одно даю цю відповідь, тому що вона, здається, відповідає вашим потребам. (Якщо ні, то це може спонукати Вас до оновлення Ваших вимог, що також було б "Доброю справою" (ТМ)).

Відредаговано: Якщо вставка порушить обмеження унікальності бази даних, буде згенеровано виключення на рівні бази даних, що передається драйвером. Це, безумовно, зупинить ваш скрипт з помилкою. У PHP повинна бути можливість вирішити цей випадок ...

KLE
Edited answer 1-го вересня 2009 в 4:15
Коментарі (4)