MySQL
ВСТАВИТЬ
Поиск…
Синтаксис
INSERT [LOW_PRIORITY | ЗАДЕРЖАННЫЕ | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [PARTITION (имя_пользователя, ...)] [(col_name, ...)] {VALUES | VALUE} ({expr | DEFAULT}, ...), (...), ... [ON DUPLICATE KEY UPDATE col_name = expr [, col_name = expr] ...]
INSERT [LOW_PRIORITY | ЗАДЕРЖАННЫЕ | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [PARTITION (имя_пользователя, ...)] SET col_name = {expr | DEFAULT}, ... [ON DUPLICATE KEY UPDATE col_name = expr [, col_name = expr] ...]
INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [PARTITION (имя_раздела, ...)] [(col_name, ...)] SELECT ... [ON DUPLICATE KEY UPDATE col_name = expr [, col_name = expr] ...]
Выражение expr может ссылаться на любой столбец, который был установлен ранее в списке значений. Например, вы можете сделать это, потому что значение для col2 относится к col1, который ранее был назначен:
INSERT INTO tbl_name (col1, col2) VALUES (15, col1 * 2);Операторы INSERT, использующие синтаксис VALUES, могут вставлять несколько строк. Для этого включите несколько списков значений столбцов, каждый из которых заключен в круглые скобки и разделен запятыми. Пример:
INSERT INTO tbl_name (a, b, c) ЗНАЧЕНИЯ (1,2,3), (4,5,6), (7,8,9);Список значений для каждой строки должен быть заключен в круглые скобки. Следующий оператор является незаконным, поскольку количество значений в списке не соответствует числу имен столбцов:
INSERT INTO tbl_name (a, b, c) ЗНАЧЕНИЯ (1,2,3,4,5,6,7,8,9);INSERT ... SELECT Синтаксис
INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [PARTITION (имя_пользователя, ...)] [(col_name, ...)] SELECT ... [ON DUPLICATE KEY UPDATE col_name = expr, ...]С помощью INSERT ... SELECT вы можете быстро вставить много строк в таблицу из одной или многих таблиц. Например:
INSERT INTO tbl_temp2 (fld_id) SELECT tbl_temp1.fld_order_id FROM tbl_temp1 WHERE tbl_temp1.fld_order_id> 100;
замечания
Основная вставка
INSERT INTO `table_name` (`field_one`, `field_two`) VALUES ('value_one', 'value_two');
В этом тривиальном примере table_name , где должны быть добавлены данные, field_one и field_two - это поля для установки данных, а value_one и value_two - данные, которые нужно делать против field_one и field_two соответственно.
Рекомендуется перечислить поля, в которые вы вставляете данные, в свой код, как если бы таблица была изменена, а новые столбцы добавлены, ваша вставка будет разбита, если они не будут там
INSERT, ON DUPLICATE KEY UPDATE
INSERT INTO `table_name`
(`index_field`, `other_field_1`, `other_field_2`)
VALUES
('index_value', 'insert_value', 'other_value')
ON DUPLICATE KEY UPDATE
`other_field_1` = 'update_value',
`other_field_2` = VALUES(`other_field_2`);
Это будет INSERT в table_name указанными значениями, но если уникальный ключ уже существует, он обновит other_field_1 чтобы получить новое значение.
Иногда при обновлении по дубликатному ключу полезно использовать VALUES() , чтобы получить исходное значение, которое было передано в INSERT вместо того, чтобы напрямую устанавливать значение. Таким образом, вы можете установить разные значения с помощью INSERT и UPDATE . См. Пример выше, где other_field_1 установлен в insert_value на INSERT или update_value в UPDATE то время как other_field_2 всегда имеет значение other_value .
Важным для вставки в обновлении повторяющихся ключей (IODKU) является схема, содержащая уникальный ключ, который будет сигнализировать о дублировании конфликта. Этот уникальный ключ может быть Первичным ключом или нет. Это может быть уникальный ключ в одном столбце или многостолбцовый (составной ключ).
Вставка нескольких строк
INSERT INTO `my_table` (`field_1`, `field_2`) VALUES
('data_1', 'data_2'),
('data_1', 'data_3'),
('data_4', 'data_5');
Это простой способ добавить несколько строк одновременно с помощью одной INSERT .
Такой тип «пакетной» вставки намного быстрее, чем вставка строк один за другим. Как правило, вставка 100 строк в одну пакетную вставку таким образом в 10 раз быстрее, чем вставка их отдельно.
Игнорирование существующих строк
При импорте больших наборов данных может быть предпочтительным при определенных обстоятельствах пропускать строки, которые обычно приводят к сбою запроса из-за ограничения столбца, например дублирования первичных ключей. Это можно сделать, используя INSERT IGNORE .
Рассмотрим следующую примерную базу данных:
SELECT * FROM `people`;
--- Produces:
+----+------+
| id | name |
+----+------+
| 1 | john |
| 2 | anna |
+----+------+
INSERT IGNORE INTO `people` (`id`, `name`) VALUES
('2', 'anna'), --- Without the IGNORE keyword, this record would produce an error
('3', 'mike');
SELECT * FROM `people`;
--- Produces:
+----+--------+
| id | name |
+----+--------+
| 1 | john |
| 2 | anna |
| 3 | mike |
+----+--------+
Важно помнить, что INSERT IGNORE также тихо пропустит и другие ошибки, вот что говорит официальная документация Mysql:
Преобразования данных, которые будут приводить к ошибкам, прерывают утверждение, если IGNORE не указано. С помощью IGNORE недопустимые значения корректируются до ближайших значений и> вставлены; появляются предупреждения, но утверждение не прерывается.
Примечание. - Раздел ниже добавлен для полноты, но не считается лучшей практикой (это не удастся, например, если в таблицу был добавлен другой столбец).
Если вы укажете значение соответствующего столбца для всех столбцов в таблице, вы можете игнорировать список столбцов в INSERT следующим образом:
INSERT INTO `my_table` VALUES
('data_1', 'data_2'),
('data_1', 'data_3'),
('data_4', 'data_5');
INSERT SELECT (Вставка данных из другой таблицы)
Это основной способ вставки данных из другой таблицы с помощью инструкции SELECT.
INSERT INTO `tableA` (`field_one`, `field_two`)
SELECT `tableB`.`field_one`, `tableB`.`field_two`
FROM `tableB`
WHERE `tableB`.clmn <> 'someValue'
ORDER BY `tableB`.`sorting_clmn`;
Вы можете SELECT * FROM , но тогда tableA и tableB должны иметь соответствующее количество столбцов и соответствующие типы данных.
Столбцы с AUTO_INCREMENT рассматриваются как в предложении INSERT с предложением VALUES .
Этот синтаксис позволяет легко заполнять (временные) таблицы данными из других таблиц, тем более, когда данные должны быть отфильтрованы по вставке.
INSERT с AUTO_INCREMENT + LAST_INSERT_ID ()
Когда таблица имеет AUTO_INCREMENT PRIMARY KEY , AUTO_INCREMENT , обычно один не вставляется в этот столбец. Вместо этого укажите все остальные столбцы, а затем спросите, что такое новый идентификатор.
CREATE TABLE t (
id SMALLINT UNSIGNED AUTO_INCREMENT NOT NULL,
this ...,
that ...,
PRIMARY KEY(id) );
INSERT INTO t (this, that) VALUES (..., ...);
SELECT LAST_INSERT_ID() INTO @id;
INSERT INTO another_table (..., t_id, ...) VALUES (..., @id, ...);
Обратите внимание, что LAST_INSERT_ID() привязан к сеансу, поэтому даже если несколько подключений вставляются в одну и ту же таблицу, каждый получает свой собственный идентификатор.
У вашего клиентского API, вероятно, есть альтернативный способ получения LAST_INSERT_ID() без фактического выполнения SELECT и передачи значения обратно клиенту вместо того, чтобы оставить его в @variable внутри MySQL. Обычно это предпочтительнее.
Более длинный, более подробный, пример
«Нормальным» использованием IODKU является запуск «дублирующего ключа» на основе некоторого ключа UNIQUE , а не главного ключа AUTO_INCREMENT PRIMARY KEY . Это демонстрирует следующее. Обратите внимание, что он не содержит id в INSERT.
Настройка для следующих примеров:
CREATE TABLE iodku (
id INT AUTO_INCREMENT NOT NULL,
name VARCHAR(99) NOT NULL,
misc INT NOT NULL,
PRIMARY KEY(id),
UNIQUE(name)
) ENGINE=InnoDB;
INSERT INTO iodku (name, misc)
VALUES
('Leslie', 123),
('Sally', 456);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
+----+--------+------+
| id | name | misc |
+----+--------+------+
| 1 | Leslie | 123 |
| 2 | Sally | 456 |
+----+--------+------+
Случай IODKU, выполняющий «обновление» и LAST_INSERT_ID() извлекает соответствующий id :
INSERT INTO iodku (name, misc)
VALUES
('Sally', 3333) -- should update
ON DUPLICATE KEY UPDATE -- `name` will trigger "duplicate key"
id = LAST_INSERT_ID(id),
misc = VALUES(misc);
SELECT LAST_INSERT_ID(); -- picking up existing value
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 2 |
+------------------+
Случай, когда IODKU выполняет «вставку», и LAST_INSERT_ID() извлекает новый id :
INSERT INTO iodku (name, misc)
VALUES
('Dana', 789) -- Should insert
ON DUPLICATE KEY UPDATE
id = LAST_INSERT_ID(id),
misc = VALUES(misc);
SELECT LAST_INSERT_ID(); -- picking up new value
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 3 |
+------------------+
Результирующее содержимое таблицы:
SELECT * FROM iodku;
+----+--------+------+
| id | name | misc |
+----+--------+------+
| 1 | Leslie | 123 |
| 2 | Sally | 3333 | -- IODKU changed this
| 3 | Dana | 789 | -- IODKU added this
+----+--------+------+
Потерянные идентификаторы AUTO_INCREMENT
Несколько функций «вставки» могут «сжечь» идентификаторы. Вот пример использования InnoDB (другие Двигатели могут работать по-другому):
CREATE TABLE Burn (
id SMALLINT UNSIGNED AUTO_INCREMENT NOT NULL,
name VARCHAR(99) NOT NULL,
PRIMARY KEY(id),
UNIQUE(name)
) ENGINE=InnoDB;
INSERT IGNORE INTO Burn (name) VALUES ('first'), ('second');
SELECT LAST_INSERT_ID(); -- 1
SELECT * FROM Burn ORDER BY id;
+----+--------+
| 1 | first |
| 2 | second |
+----+--------+
INSERT IGNORE INTO Burn (name) VALUES ('second'); -- dup 'IGNOREd', but id=3 is burned
SELECT LAST_INSERT_ID(); -- Still "1" -- can't trust in this situation
SELECT * FROM Burn ORDER BY id;
+----+--------+
| 1 | first |
| 2 | second |
+----+--------+
INSERT IGNORE INTO Burn (name) VALUES ('third');
SELECT LAST_INSERT_ID(); -- now "4"
SELECT * FROM Burn ORDER BY id; -- note that id=3 was skipped over
+----+--------+
| 1 | first |
| 2 | second |
| 4 | third | -- notice that id=3 has been 'burned'
+----+--------+
Подумайте об этом (примерно) следующим образом: сначала вставка смотрит, сколько строк может быть вставлено. Затем возьмите столько значений из auto_increment для этой таблицы. Наконец, вставьте строки, используя идентификаторы по мере необходимости, и сжигая любые оставшиеся следы.
Единственный раз, когда оставшаяся часть восстанавливается, - это если система выключена и перезапущена. При перезапуске выполняется MAX(id) . Это может повторно использовать идентификаторы, которые были сожжены или которые были высвобождены с помощью DELETEs с наивысшим id (s).
По существу любой аромат INSERT (включая REPLACE , который является DELETE + INSERT ) может сжечь идентификаторы. В InnoDB глобальная (не сессия!) Переменная innodb_autoinc_lock_mode может использоваться для управления некоторыми из того, что происходит.
Когда «нормализация» длинных строк в AUTO INCREMENT id , сжигание может легко произойти. Это может привести к переполнению размера INT вы выбрали.