MySQL: INSERT/REPLACE добавление данных в таблицу

Инструкция INSERT вставляет новые строки в существующую таблицу. Формы инструкции INSERT ... VALUES и INSERT ... SET вставляют строки на основе явно указанных значений. Форма INSERT ... SELECT вставляет строки, выбранные из другой таблицы или таблиц.

Содержание:

Синтаксис MySQL-инструкции INSERT;
- Комментарии к синтаксису INSERT;
INSERT и уникальный индекс записи;
Использование оператора SET;
Использование ключевого слова IGNORE;
Использование выражений expr при добавлении данных;
Добавление данных, выбранных из других таблиц;

Синтаксис MySQL-инструкции `INSERT`:

-- ВАРИАНТ 1 (использование оператора `VALUES`)
INSERT [IGNORE] INTO tbl_name (col1, [, col2] ...) VALUES (val, [, expr] ...)
-- ВАРИАНТ 2 (использование оператора `SET`)
INSERT [IGNORE] INTO tbl_name SET col1 = val, col2 = expr , ... 
-- ВАРИАНТ 3 (на основе запроса `SELECT`)
INSERT [IGNORE] INTO tbl_name (col1, [, col2] ...) SELECT col1, col2, ... FROM other_table
-- ВАРИАНТ 4 (`REPLACE` вместо `INSERT`)
REPLACE INTO tbl_name (col1, [, col2] ...) VALUES (val, [, expr] ...)
REPLACE INTO tbl_name SET col1 = val, col2 = expr , ... 
REPLACE INTO tbl_name (col1, [, col2] ...) SELECT col1, col2, ... FROM other_table

Комментарии к синтаксису `INSERT`:

Вариант 1 и 2: если при добавлении записи указаны НЕ ВСЕ колонки таблицы, то для пропущенных столбцов вставляются значения по умолчанию DEFAULT, которое определяется при создании таблицы. Если при создании таблицы значение DEFAULT не определено, то вставляется значение NULL.
Вариант 2: оператор SET явно указывает столбцы по имени вместе со значением, которое нужно присвоить каждому из них.
Вариант 1 и 3: Каждый список значений VALUES () или оператор SELECT ... (если значения добавляются из другой таблицы) должен содержать ровно столько значений, сколько указано столбцов для вставки INSERT INTO tbl_name (). Также позволяет вставить сразу несколько строк/записей за один запрос.
Вариант 1 и 3: если не указан список столбцов для INSERT INTO tbl_name ... VALUES или INSERT INTO tbl_name ... SELECT, то в списке VALUES () или в результате работы оператора SELECT должны быть определены величины для всех столбцов таблицы. Значение для столбцов вставляются по порядку, как они расположены в таблице. Если порядок столбцов неизвестен, то для его получения можно использовать MySQL-команду DESCRIBE tbl_name.
Вариант 4: инструкция REPLACE работает точно так же, как INSERT (это расширение MySQL для стандарта SQL), за исключением того, что если старая строка в таблице имеет то же значение, что и новая строка для колонок PRIMARY KEY или индекса UNIQUE, то перед вставкой новой строки - старая строка удаляется. Возможно, что в случае дублирующего ключа механизм хранения может выполнить REPLACE как обновление, а не как удаление и вставку, но семантика остается той же.

`INSERT` и уникальный индекс записи.

Для создания уникального индекса id при добавлении новых строк, лучше использовать атрибут AUTO_INCREMENT, который определяется при создании таблицы:

CREATE TABLE animals (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL DEFAULT '',
     PRIMARY KEY (id)
);
-- добавление сразу нескольких 
-- записей без указания `id`
INSERT INTO animals (name) VALUES ('dog'), ('cat');
-- добавление записи с указанием `id`
INSERT INTO animals (id, name) VALUES (50, 'penguin');
-- эквивалентно добавлению записи без указания `id`
INSERT INTO animals (id, name) VALUES(NULL, 'mouse');
-- смотрим результаты 
SELECT * FROM animals;
+-----+-----------+
| id  | name      |
+-----+-----------+
|   1 | dog       |
|   2 | cat       |
|  50 | penguin   |
|  51 | mouse     |
+-----+-----------+

Использование оператора `SET`.

Использование оператора INSERT ... SET не позволяет добавить сразу несколько записей, как это делает оператор INSERT ... VALUES () (смотри пример выше) или INSERT ... SELECT.

Помним, что если при добавлении записи указаны НЕ ВСЕ колонки таблицы, то для пропущенных столбцов вставляются значения по умолчанию DEFAULT, которое определяется при создании таблицы.

-- добавление записи без указания `id`
INSERT INTO animals SET (name='rabbit')
-- добавление записи с указанием `id`
INSERT INTO animals SET (id=100, name='squirrel');

Использование ключевого слова `IGNORE`.

Если при добавлении записи уникальный индекс указывается/генерируется вручную, и запись с таким id уже существует, то операция добавлении возвращает ошибку целостности данных. Ключевое слово IGNORE, предотвращает возникновение ошибки при совпадении первичного ключа MySQL, при этом возвращает предупреждение "Query OK, 0 rows affected, 1 warning", а добавление данных не происходит.

-- добавление записи в таблицу, где 
-- уникальный индекс `id=5` уже существует 
INSERT IGNORE INTO tbl_name SET (id=5, col1='val1', col2='val2');
-- эквивалентный запрос с оператором `VALUES`
INSERT IGNORE INTO tbl_name (id, col1, col2) VALUES (5, 'val1', 'val2');
-- вместо ошибки запросы выдадут предупреждение
-- Query OK, 0 rows affected, 1 warning

Вместо сочетания инструкций INSERT IGNORE можно использовать инструкцию REPLACE. В этом случае, если для колонок PRIMARY KEY или индекса UNIQUE старая строка в таблице имеет то же значение, что и новая строка, то перед вставкой новой строки - старая строка удаляется.

-- добавление записи в таблицу, где 
-- уникальный индекс `id=5` уже существует 
REPLACE INTO tbl_name SET (id=5, col1='val1', col2='val2');
-- эквивалентный запрос с оператором `VALUES`
REPLACE INTO tbl_name (id, col1, col2) VALUES (5, 'val1', 'val2');

Использование выражений `expr` при добавлении данных.

Выражение expr (смотри синтаксис) может ссылаться на любой столбец, установленный ранее в списке значений.

INSERT INTO tbl_name (col1, col2) VALUES (15, col1*2);

Но следующий запрос недопустим, потому что значение col1 относится к col2, который присваивается после col1:

INSERT INTO tbl_name (col1, col2) VALUES (col2*2, 15);

Исключение возникает для столбцов, содержащих значения AUTO_INCREMENT. Поскольку значения AUTO_INCREMENT генерируются после других назначений значений, любая ссылка на столбец AUTO_INCREMENT в назначении возвращает 0.

Добавление данных, выбранных из других таблиц.

В качестве источника данных для добавления можно использовать другую таблицу. Каждый список возвращаемых значений оператора SELECT ... должен содержать ровно столько значений, сколько указано столбцов для вставки INSERT INTO tbl_name ....

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

Например, добавим из таблицы new_animals (подготовленную менеджерами) доставленных в зоомагазин новых зверьков (в данном случае таблица animals содержит два столбца id и name).

INSERT INTO animals (name)
SELECT name FROM new_animals

-- или без указания имен столбцов 
INSERT INTO animals 
SELECT id, name FROM new_animals
WHERE id NOT IN (SELECT id FROM animals);

MySQL: INSERT/REPLACE добавление данных в таблицу

Содержание:

Синтаксис MySQL-инструкции INSERT:

Комментарии к синтаксису INSERT:

INSERT и уникальный индекс записи.

Использование оператора SET.

Использование ключевого слова IGNORE.

Использование выражений expr при добавлении данных.