12 основных команд для вставки данных в MySQL с примерами

Следующая команда соединит нас с БД devdb, имя пользователя devuser и пароль mysecretpwd.

mysql -u devuser -pmysecretpwd devdb
mysql>

Мы будем вводить данные в таблицу employee. Структура таблицы приведена ниже:

mysql> desc employee;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | int(11)     | NO   | PRI | NULL    | auto_increment |
| name   | varchar(20) | YES  |     | NULL    |                |
| dept   | varchar(10) | YES  |     | NULL    |                |
| salary | int(10)     | YES  |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+

Пример базового использования команды INSERT

Следующая команда добавит новую запись в таблицу. После ключевого слово VALUES указываются значения столбцов таблицы:

INSERT INTO employee VALUES(100, 'Thomas', 'Sales', 5000);

Используйте команду SELECT для проверки введенных данных.

SELECT * FROM employee;

Вставка данных только для определенных столбцов

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

INSERT INTO employee(id, name) VALUES(200, 'Jason');

Мы не указали значения полей dept и salary. В итоге, мы получили NULL в этих столбцах. Обратите внимание, это не строковое значение “NULL”, это значение, указывающие на то, что ячейка пуста.

mysql> SELECT * FROM employee;
+-----+--------+-------+--------+
| id  | name   | dept  | salary |
+-----+--------+-------+--------+
| 100 | Thomas | Sales |   5000 |
| 200 | Jason  | NULL  |   NULL |
+-----+--------+-------+--------+
2 rows in set (0.00 sec)

Пример Insert Set

Вместо ключевого слова values вы можете использовать слово SET для установки значения нужному полю.

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

mysql> INSERT INTO employee SET id=300, name='Mayla';
mysql> select * from employee;
+-----+--------+-------+--------+
| id  | name   | dept  | salary |
+-----+--------+-------+--------+
| 100 | Thomas | Sales |   5000 |
| 200 | Jason  | NULL  |   NULL |
| 300 | Mayla  | NULL  |   NULL |
+-----+--------+-------+--------+

Вставка значений на основе значений из другой таблицы

В этом примере мы используем INSERT...SELECT метод, для выбора значений из другой таблицы.

Следующая команда копирует значения из таблицы contractor в employee.

INSERT INTO employee SELECT * FROM contractor;

Также вы можете комбинировать условия WHERE, для выбора определенных полей.

INSERT INTO employee SELECT * FROM contractor WHERE salary >= 7000;

Внимание: Если вы работаете с БД Oracle, то используйте синтаксис insert into employee AS select * from contractor. MySQL не поддерживает ключевого слова AS в данном контексте.

Ввод выбранных столбцов из другой таблицы

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

Следующая команда выбирает поля id и name всех записей из таблицы contractor и вводит их в таблицу employee.

INSERT INTO employee(id,name) SELECT id,name FROM contractor;

Также допускается использование условий WHERE.

INSERT INTO employee(id,name) SELECT id,name FROM contractor WHERE salary >= 7000;

Обратите внимание, если запись с указанным ID уже существует, вы получит ошибку. ERROR 1062 (23000): Duplicate entry ‘100’ for key ‘PRIMARY’

Ввод записей в определенную секцию

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

В следующем примере данные вводятся в раздел p1.

INSERT INTO employee PARTITION (p1) VALUES(100,'Thomas','Sales',5000);

Обратите внимание, что запись с этим ID уже существует в указанном разделе. Поэтому мы получим соответствующую ошибку.

ERROR 1729 (HY000): Found a row not matching the given partition set

Внимание: Этот пример отработает только в MySQL 5.6 и выше.

Вставка записей в несколько разделов таблицы

Вы можете при помощи одной команды вставить записи сразу в несколько разделов таблицы. Следующий запрос вставит данные в разделы p1 и p2.

INSERT INTO employee PARTITION (p1, p2) VALUES(100,'Thomas','Sales',5000), (200,'Jason','Technology',5500);

Обратите внимание, если по какой-либо причине одна из записей вызовет ошибку при обработке, то весь запрос будет отменен.

Снова, пример отработает только в MySQL 5.6 и выше.

Игнорирование ошибок при вводе данных

Если по каким-либо причинам вам необходимо пропускать ошибки при вводе данных, то вы можете использовать ключевое слово IGNORE.

К примеру, следующая команда вызовет ошибку, так как запись с таким ID уже существует.

mysql> INSERT INTO employee VALUES(100,'Thomas','Sales',5000);
ERROR 1062 (23000): Duplicate entry '100' for key 'PRIMARY'

Чтобы пропускать подобные ошибки вы можете обратиться в команде insert ignore. Помните, что запись не будет внесена в таблицу. Вы просто игнорируете возникающую ошибку.

mysql> INSERT IGNORE INTO employee VALUES(100,'Thomas','Sales',5000);
Query OK, 0 rows affected (0.00 sec)

Значение по-умолчанию

Если MySQL запущен в строгом режиме, то, не указав значения по-умолчанию для полей, мы получим ошибки.

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

Например, в таблице bonus оба поля не могут принимать значение NULL.

mysql> DESC bonus;
+--------+---------+------+-----+---------+-------+
| Field  | Type    | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| id     | int(11) | NO   |     | NULL    |       |
| amount | int(11) | NO   |     | NULL    |       |
+--------+---------+------+-----+---------+-------+

Введем значение только для id.

INSERT INTO bonus(id) VALUES(100);

При выборке данных, мы видим, что поле amount приняло значение 0.

SELECT * FROM bonus;
+-----+--------+
| id  | amount |
+-----+--------+
| 100 |      0 |
+-----+--------+

Если мы опустим оба поля при вводе данных, то они примут значение 0. Если поля не указаны при вводе, то они принимают значения по-умолчанию для таблицы.

INSERT INTO bonus VALUES();
mysql> select * from bonus;
+-----+--------+
| id  | amount |
+-----+--------+
|   0 |      0 |
+-----+--------+

Внимание: допускается применения ключевого слова DEFAULT. Результат выполнения запроса идентичен предыдущему.

INSERT INTO bonus VALUES(DEFAULT, DEFAULT);

Для строковых полей значение по-умолчанию — пустая строка. Обратите внимание, что для полей AUTO_INCREMENT значение по-умолчанию — увеличенное на единицу значение предыдущей записи.

Выражения в качестве значение в INSERT

В следующем примере, в качестве значения поля bonus мы указали 5000+id. В результате поле bonusбудет равно сумме размера бонуса и ID.

Допускается использование +, -, *, или любого другого корректного оператора MySQL. В следующем примере используется 50*2 в качестве значения ID. В итоге ID примет значение 100.

Вы также можете обращаться к значения других столбцов. Например, 5000+id для поля bonus. То есть мы используем значение столбца id (100) и добавляем его к 5000. Результирующее значение — 5100.

mysql> INSERT INTO employee VALUES(50*2, 'Thomas', 'Sales', 5000+id);
mysql> select * from employee;
+-----+--------+-------+--------+
| id  | name   | dept  | salary |
+-----+--------+-------+--------+
| 100 | Thomas | Sales |   5100 |
+-----+--------+-------+--------+

Изменение приоритета команды INSERT

При работе с движками, допускающими блокирование таблиц (MyISAM), допускается указывать приоритет команде INSERT.

Например, следующая команда откладывает операцию INSERT до тех пор, пока сервер не обработает все операции чтения над таблицей.

INSERT LOW_PRIORITY INTO employee VALUES(100, 'Thomas', 'Sales', 5000);

Также можно указывать высокий приоритет. Это операции полностью противоположна предыдущей.

INSERT HIGH_PRIORITY INTO employee VALUES(100, 'Thomas', 'Sales', 5000);

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

Обратите внимание, что команда очень похожа на INSERT...DELAY, которая считается устаревшей начиная с MySQL версии 5.6.6, поэтому стоит избегать её использования.

Обновления строки при повторении

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

mysql>  INSERT INTO employee VALUES(100,'Thomas','Sales',5000);
ERROR 1062 (23000): Duplicate entry '100' for key 'PRIMARY'

mysql> select * from employee;
+-----+--------+-------+--------+
| id  | name   | dept  | salary |
+-----+--------+-------+--------+
| 100 | Thomas | Sales |   5000 |
+-----+--------+-------+--------+

Но, вы можете обновить поля такой записи (при обнаружении дубликата) при помощи команды ON DUPLICATE KEY UPDATE.

В примере ниже мы обновляем значение поля salary, при возникновении дубликата записи, увеличив его на 500.

mysql> INSERT INTO employee VALUES(100,'Thomas','Sales',5000) on DUPLICATE KEY UPDATE salary=salary+500;
mysql> select * from employee;
+-----+--------+-------+--------+
| id  | name   | dept  | salary |
+-----+--------+-------+--------+
| 100 | Thomas | Sales |   5500 |
+-----+--------+-------+--------+

Обратите внимание, что, хотя в предыдущем примере мы добавляем только одну строку, мы все равно получаем в ответ 2 rows affected.