Практическое применение RETURNING¶
После добавления строки в таблицу часто бывает нужно получить сгенерированный СУБД уникальный идентификатор этой строки (ID) или значение любого поля, которое имеет значение по умолчанию.
К примеру, имеется таблица клиентов
CREATE TABLE clients( id serial PRIMARY KEY, -- строка эквивалентна id int DEFAULT nextval('clients_id_seq') PRIMARY KEY name varchar NOT NULL );
Значение поля ID формируется СУБД автоматически, если оно не задано явно. Для нумерации клиентов используется последовательность clients_id_seq. Поэтому при создании нового клиента без явного указания его идентификатора новое значение ID будет сформировано запросом
SELECT nextval('clients_id_seq')
Таким образом запрос
INSERT INTO clients(name) VALUES('Denis');
реально трансформируется в запрос
INSERT INTO clients(id, name) VALUES(nextval('clients_id_seq'), 'Denis');
Если нужно получить автоматически сгенерированный ID, то у разработчиков есть два стандартных варианта:
-
Явно получить новый ID и вставить его в запрос
_new_id := nextval('clients_id_seq'); INSERT INTO clients(id, name) VALUES(_new_id, 'Denis');
INSERT INTO clients(name) VALUES(nextval('Denis'); _new_id := currval('clients_id_seq');
Оба варианта используют два запроса для одной бизнес-операции.
Но можно обойтись одним запросом, использовав RETURNING
db=# INSERT INTO clients(name) VALUES('Denis') RETURNING id; id ------- 10001 (1 row)
В этом случае запрос создаст нового клиента и в качестве результата вернет его ID.
Возвращать измененные значение полей может и UPDATE. Классический пример с изменение баланса счета в банке — надо уменьшить баланс клиента на 100.
Запрос будет таким.
UPDATE accounts SET balance = balance - 100 WHERE id = _id;
Чтобы получить обновленный баланс, надо сделать второй запрос
SELECT balance FROM accounts WHERE id = _id;
Если эти два запроса делать в различных транзакция или без соответствующих блокировок, то это может привести к беде — баланс клиента не будет соответствовать действительности из-за возможных конкурентных параллельных изменений одной и той же записи. Для предотвращения подобных случаев удобно использовать RETURNING. Запрос будет таким
db=# UPDATE accounts SET balance = balance - 100 WHERE id = _id RETURNING balance; balance ----------- 200.5 (1 row)
После выполнения изменения баланса запрос вернет в качестве своего результата обновленный баланс, при этом баланс будет корректным и это значение можно будет использовать в других вычислениях.
Поэтому RETURNING может не только уменьшить количество запросов к базе, но и сократить количество потенциальных проблем при обработке данных.
RETURNING может возвращать не только данные одной строки, но и все измененные/обработанные строки для UPDATE и DELETE.
Пример, когда RETURNING возвращает список удаленных записей.
db=# DELETE FROM customers RETURNING *; id | name ------+-------- 1950 | Hana 2709 | Maritza 9192 | Miya 6437 | Jamil .
Пользуйтесь с удовольствием!
PDO::lastInsertId
Возвращает ID последней вставленной строки или последнее значение от объекта последовательности, в зависимости от базового драйвера. Например, PDO_PGSQL требует задать имя объекта последовательности для параметра name .
Замечание:
В зависимости от драйвера PDO этот метод может вообще не выдать осмысленного результата, так как база данных может не поддерживать автоматического инкремента полей или последовательностей.
Список параметров
Имя объекта последовательности, который должен выдать ID.
Возвращаемые значения
Если объект последовательности для name не задан, PDO::lastInsertId() вернёт строку, представляющую ID последней добавленной в базу записи.
Если же объект последовательности для name задан, PDO::lastInsertId() вернёт строку, представляющую последнее значение, полученное от этого объекта.
Если PDO драйвер не поддерживает эту возможность, PDO::lastInsertId() запишет IM001 в SQLSTATE.
Ошибки
Выдаёт ошибку уровня E_WARNING , если атрибуту PDO::ATTR_ERRMODE установлено значение PDO::ERRMODE_WARNING .
Выбрасывает исключение PDOException , если атрибуту PDO::ATTR_ERRMODE установлено значение PDO::ERRMODE_EXCEPTION .
User Contributed Notes 24 notes
11 years ago
Remember, if you use a transaction you should use lastInsertId BEFORE you commit
otherwise it will return 0
5 years ago
To save time for some of you.
When using MySQL or MariaDB while inserting multiple rows in a single query (INSERT INTO table (a,b,c) VALUES (1,2,3), (2,3,4), . ) to a table with auto_increment column, PDO::lastInsertId does NOT return the autogenerated id of the last row. Instead, the FIRST generated id is returned. This may very well be explained by taking a look at MySQL and MariaDB’s documentation.
Quotations from their respective documentations,
MySQL:
«With no argument, LAST_INSERT_ID() returns a BIGINT UNSIGNED (64-bit) value representing the first automatically generated value successfully inserted for an AUTO_INCREMENT column as a result of the most recently executed INSERT statement.»
MariaDB:
«LAST_INSERT_ID() (no arguments) returns the first automatically generated value successfully inserted for an AUTO_INCREMENT column as a result of the most recently executed INSERT statement.»
This is clearly not what lastInsertId’s own documentation states. Hopefully this will save someone from debugging the cause of id mismatch.
tl;dr (MySQL | Mariadb) + multi row insert + PDO::lastInsertId = first autogenerated id
Behaviour tested using MariaDB 10.2.6 32-bit, PHP 5.6.31 32-bit and mysqlnd 5.0.11 running on windows 7.
15 years ago
Beware of lastInsertId() when working with transactions in mysql. The following code returns 0 instead of the insert id.
try <
$dbh = new PDO ( ‘mysql:host=localhost;dbname=test’ , ‘username’ , ‘password’ );
$stmt = $dbh -> prepare ( «INSERT INTO test (name, email) VALUES(. )» );
try <
$dbh -> beginTransaction ();
$tmt -> execute ( array( ‘user’ , ‘user@example.com’ ));
$dbh -> commit ();
print $dbh -> lastInsertId ();
> catch( PDOExecption $e ) <
$dbh -> rollback ();
print «Error!: » . $e -> getMessage () . «» ;
>
> catch( PDOExecption $e ) <
print «Error!: » . $e -> getMessage () . «» ;
>
?>
When no exception is thrown, lastInsertId returns 0. However, if lastInsertId is called before calling commit, the right id is returned.
17 years ago
in case anyone was wondering
something like
$val = 5;
$sql = «REPLACE table (column) VALUES (:val)»;
$stmt = $dbh->prepare($sql);
$stmt->bindParam(‘:val’, $val, PDO::PARAM_INT);
$stmt->execute();
$lastId = $dbh->lastInsertId();
will return the last inserted id, whether the record was replaced or simply inserted
the REPLACE syntax, simply inserts, or deletes > inserts
so lastInsertId() still works
16 years ago
It should be mentioned that this function DOES NOT retrieve the ID (Primary key) of the row but it’s OID instead.
So if you use one of the latest PostgreSQL versions this function won’t help you unless you add OID to the table specifically when you create it.
12 years ago
If you’re accessing MSSQL/SQL Server 2008 R2 (or higher) from Linux via FreeTDS there’s a slightly neater way of getting the last insert ID than the solution(s) outlined below.
The specific SQL involved is outlined here:
So for example, with a table containing the two columns (product_id, product_name) where product_id is a uniqueidentifier or something similar you could do the following.
// Assume $dbh connection handle is already established
$sql = «INSERT INTO product (product_name) OUTPUT INSERTED.product_id VALUES (?)» ;
$sth = $dbh -> prepare ( $sql );
$sth -> execute (array( ‘widgets’ ));
$temp = $sth -> fetch ( PDO :: FETCH_ASSOC );
?>
Then $temp will contain an array like:
Array
(
[product_id] => E1DA1CB0-676A-4CD9-A22C-90C9D4E81914
)
Just be warned that there are some issues relating to how uniqueidentifier columns are handled by PDO_DBLIB/FreeTDS depending on the TDS version you choose that have only been fixed as of PHP 5.3.7.
Information regarding this and the patch can be found here:
12 years ago
I think I get a nice solution in Postgres to get the ID using the RETURNING that comes with Postgress since version 8.2. In the example below, I add to my insert clause the «returning» along with the primary key of my table, then after the execute, I do a fetch getting an array with the value of the last inserted id.
public function insert ( $employee )
$sqlQuery = «INSERT INTO employee(user_id,name,address,city) VALUES(:user_id,:name,:address,:city) RETURNING employee_id» ;
$statement = $this -> prepare ( $sqlQuery );
$a = «2002-03-11 12:01AM» ;
$statement -> bindParam ( «:user_id» , $employee -> getUserId (), PDO :: PARAM_INT );
$statement -> bindParam ( «:name» , $employee -> getName (), PDO :: PARAM_STR );
$statement -> bindParam ( «:address» , $employee -> getAddress (), PDO :: PARAM_STR );
$statement -> bindParam ( «:city» , $employee -> getCity (), PDO :: PARAM_STR );
$statement -> execute ();
$result = $statement -> fetch ( PDO :: FETCH_ASSOC );
return $result [ «employee_id» ];
5 years ago
On version 7.0.9 I’ve implemented lastInsertId without having to name the sequence for PostgreSQL (I’ve also have done it for 5.6.can’trememberthenumber, but I can’t find the PR).
Can someone update the documentation about it?
6 years ago
WARNING for PostgreSQL users! In response to the comment by ed at hicklinslade dot com, who wrote:
This does appear to function as expected. What is a little unclear to me is whether this simply returns the current value of the sequence; if it does, this isn’t a particularly reliable indicator as to the id of the record your code just inserted, especially if your site or application is especially high traffic.
.
NEVER ever use lastInsertId() with PostgreSQL sequences, ESPECIALLY when your application’s insert/update load is high. PostgreSQL sequences are non-transactional (a natural design feature to avoid exclusive locking which otherwise produces unacceptable performance). This means that any concurrent transaction incrementing the same sequence will render the value returned by lastInsertId() invalid with respect to the last insert by your transaction. Example:
Transaction 1 inserts with nextval(‘some_seq’) yielding 100;
Concurrent transaction 2 inserts with nextval(‘some_seq’) yielding 101;
Transaction 1 calls lastInsertId(), expecting 100, BUT GETS 101.
This PDO method is braindead for PostgreSQL, always use INSERT . RETURNING instead. Regards.
11 years ago
This function is now compatible with the newer MS SQL driver. http://msdn.microsoft.com/en-us/library/ff628155(v=sql.105)
6 years ago
About the connections created through classes
eg: db::SQL()->query();
then db::SQL()->lastInsertId();
it will create a new connection and will not return the last ID inserted. it is better to include a PDO connection file (or directly the logins) and work with it to get the last ID properly.
$db = new PDO(logins);
$db->query();
$db->lastInsertId();
1 year ago
MySQL/MariaDB users, be aware that although this function returns a string, leading zeroes are NOT preserved if your column has ZEROFILL property.
4 years ago
$dbh->commit();
print $dbh->lastInsertId();
The above will always return zero (0)
So it is important to call $dbh->lastInsertId(); before commiting transaction
the above should be modified as
print $dbh->lastInsertId();
$dbh->commit();
10 years ago
Easiest solution I’ve found for MSSQL to obtain the last inserted ID is
$STH = $DBH -> query ( «SELECT CAST(COALESCE(SCOPE_IDENTITY(), @@IDENTITY) AS int)» );
$STH -> execute ();
$result = $STH -> fetch ();
print $result [ 0 ];
?>
14 years ago
Workaround for the fact that MSSQL does not provide lastInsertId(). This is locale-independent by design.
$mixRc = false ;
try <
// Issue a compound command, 2nd part outputs the inserted Id
$strQuery =
‘INSERT INTO t1 (f1,f2) VALUES(v1,v2); SELECT @@IDENTITY AS mixLastId’ ;
// Yup, your eyes are ok, NOT exec but query.
$objSth = $objDb -> query ( $strQuery );
$mixRc = ( is_object ( $objSth ) and $objSth -> errorCode () == ‘00000’ );
>
catch ( PDOException $objException ) <
$pdoMsg = $objException -> getMessage ();
$pdoMsg = iconv ( «ISO-8859-1» , «UTF-8» , $pdoMsg );
$strMessage = ‘insertRecord: Failed ‘ .
$strQuery . ‘, Error Message: ‘ . $pdoMsg ;
doLog ( __FILE__ , __LINE__ , $strMessage );
throw new core_exception_database ( $strMessage );
>
if ( $mixRc === false ) return false ;
// The compound command delivers a multi-rowset statement handle
// Move past the first (invalid) rowset from the INSERT command
$objSth -> nextRowset ();
// Pick up the first row of the rowset from «SELECT @@IDENTITY»
$rowTd = $objSth -> fetch ( PDO :: FETCH_NUM );
if (! is_array ( $rowTd )) <
doLog ( __FILE__ , __LINE__ ,
‘insertRecord: $objSth->fetch() returns %s’ , gettype ( $rowTd ));
return false ;
>
$objSth -> closeCursor ();
$strLastRowId = trim ( $rowTd [ 0 ]); // trim() for trailing Nullbyte
// Integers are returned stringified, format depends on locale
// Generally ends with «,00» or «.00» — trim that off
$strLastRowId = preg_replace ( ‘/[,.]0+$/’ , » , $strLastRowId );
// Remove any remaining «.» or «,» for thousands
$strLastRowId = preg_replace ( ‘/[,.]/’ , » , $strLastRowId );
// A GUID, which contains no «,» or «.», will be left unchanged
return $strLastRowId ;
?>
7 years ago
beware when mixing auto-incremented and explicit IDs!
Given a fresh table «tbl», executing
insert into tbl values (0, ‘kaeptn blaubaer’); —auto increment (-> 1)
insert into tbl values (16, ‘pipi langstrumpf’); —explicit id (-> 16)
select LAST_INSERT_ID();
will return 1, which is not the value of the last insert. its the value from the last auto-increment insert!
Операции с данными
Для добавления данных применяется команда INSERT , которая имеет следующий формальный синтаксис:
INSERT INTO имя_таблицы (столбец1, столбец2, . столбецN) VALUES (значение1, значение2, . значениеN)
После INSERT INTO идет имя таблицы, затем в скобках указываются все столбцы через запятую, в которые надо добавлять данные. И в конце после слова VALUES в скобках перечисляются добавляемые значения.
Допустим, у нас в базе данных есть следующая таблица:
CREATE TABLE Products ( Id SERIAL PRIMARY KEY, ProductName VARCHAR(30) NOT NULL, Manufacturer VARCHAR(20) NOT NULL, ProductCount INTEGER DEFAULT 0, Price NUMERIC );
Добавим в нее одну строку с помощью команды INSERT:
INSERT INTO Products VALUES (1, 'Galaxy S9', 'Samsung', 4, 63000)
После удачного выполнения в pgAdmin в поле сообщений должно появиться сообщение «INSERT 0 1»:
Стоит учитывать, что значения для столбцов в скобках после ключевого слова VALUES передаются по порядку их объявления. Например, в выражении CREATE TABLE выше можно увидеть, что первым столбцом идет Id, поэтому этому столбцу передаетсячисло 1. Второй столбец называется ProductName, поэтому второе значение — строка «Galaxy S9» будет передано именно этому столбцу и так далее. То есть значения передаются столбцам следующим образом:
- Id: 1
- ProductName: ‘Galaxy S9’
- Manufacturer: ‘Samsung’
- ProductCount: 4
- Price: 63000
Также при вводе значений можно указать непосредственные столбцы, в которые будут добавляться значения:
INSERT INTO Products (ProductName, Price, Manufacturer) VALUES ('iPhone X', 71000, 'Apple');
Здесь значение указывается только для трех столбцов. Причем теперь значения передаются в порядке следования столбцов:
- ProductName: ‘iPhone X’
- Manufacturer: ‘Apple’
- Price: 71000
Для столбца Id значение будет генерироваться автоматически базой данных, так как он представляет тип Serial. То есть к значению из последней строки будет добавляться единица.
Для остальных столбцов будет добавляться значение по умолчанию, если задан атрибут DEFAULT (например, для столбца ProductCount), значение NULL. При этом неуказанные столбцы (за исключением тех, которые имеют тип Serial) должны допускать значение NULL или иметь атрибут DEFAULT.
Если конкретные столбцы не указываются, как в первом примере, тогда мы должны передать значения для всех столбцов в таблице.
Также мы можем добавить сразу несколько строк:
INSERT INTO Products (ProductName, Manufacturer, ProductCount, Price) VALUES ('iPhone 6', 'Apple', 3, 36000), ('Galaxy S8', 'Samsung', 2, 46000), ('Galaxy S8 Plus', 'Samsung', 1, 56000)
В данном случае в таблицу будут добавлены три строки.
Возвращение значений
Если мы добавляем значения только для части столбцов, то мы можем не знать, какие значения будут у других столбцов. Например, какое значени получит столбец Id у товара. С помощью оператора RETURNING мы можем получить это значение:
INSERT INTO Products (ProductName, Manufacturer, ProductCount, Price) VALUES('Desire 12', 'HTC', 8, 21000) RETURNING id;
Insert в MySQL — добавление данных в таблицу
В статье расскажем, для чего нужна команда INSERT в MySQL и покажем как ей пользоваться на десяти практических примерах.
Зачем нужна команда Insert
Команда INSERT используется для того, чтобы вставлять новые данные в таблицы. Общий и наиболее часто используемый синтаксис выглядит так:
INSERT into table_name [(column1, [, column2] . )] values (values_list) Минимальный набор обязательных параметров: название таблицы и список значений. Но часто также указывают список столбцов, в которые нужно вставить данные. Есть еще много других необязательных параметров и возможностей. Самую основную часть из них мы посмотрим в этой статье.
Создаем базу MySQL в облаке
Прежде чем начать работать с командой Insert, нам нужна база данных MySQL. Чтобы не заниматься долгой установкой и настройкой, мы создадим управляемую БД на платформе Selectel. Если сервер MySQL у вас уже установлен, можете сразу переходить к следующему разделу.
Заходим в личный кабинет, в разделе «Облачная платформа» переходим к «Базам данных». Нажимаем кнопку «Создать кластер».
На следующем экране выбираем параметры новой базы. Выбираем «СУБД» — MySQL. Далее необходимо выбрать конфигурацию сервера: нам будет достаточно 2 vCPU, 4 ГБ оперативной памяти и 32 ГБ диска. Обратите внимание на раздел «Сеть» — у вас должна быть выбрана публичная сеть, чтобы к базе данных можно было подключиться из интернета. Остальные параметры можно оставить по умолчанию.
Немного подождем, пока сервер создается. Когда он перейдет в статус ACTIVE, выберите его и зайдите в раздел настроек. Сначала перейдем на вкладку «Пользователи» и создадим нового пользователя, который сможет подключаться к базе данных. Не забудьте записать имя пользователя и пароль, они будут нужны для подключения.
Затем перейдем на следующую вкладку — «Базы данных». Нам нужно создать базу, в которой мы будем работать. Для этого нажмем кнопку «Создать базу данных». Запишите название созданной БД, оно нам будет нужно для подключения.
Когда база создастся, нужно дать нашему пользователю права на нее. На этой же вкладке нажмем кнопку «Добавить» и выберем недавно созданного пользователя.
Все, БД настроена и к ней можно подключаться. Чтобы узнать параметры подключения, перейдите на вкладку «Настройки» и там в самом низу в разделе «Подключение» указаны нужные параметры. Вы можете пользоваться консольным клиентом или любой графической утилитой. Мы будем показывать на примере консольного клиента, поэтому строка для подключения будет выглядеть вот так:
mysql --host=109.71.10.30 \ --port=6033 \ --user=chrystal \ --password \ --database=db После ввода этой команды консоль попросит нас ввести пароль. Вот и все, мы подключились к БД.
Структура таблиц для примеров
Все дальнейшие команды мы будем показывать на простом примере из трех таблиц. Допустим, мы — сеть фруктовых магазинов. У нас есть таблица со списком магазинов, таблица со списком товаров (фруктов) и таблица, в которой мы записываем какой фрукт в каком магазине продается и по какой цене.
С помощью команды Insert мы будем вставлять в эти таблицы новые данные разными способами. Итак, вот описание наших таблиц и начальные данные в них:
+----+------------------+--------------+ | id | name | address | +----+------------------+--------------+ | 1 | Магазин 1 | Адрес 1 | | 2 | Магазин 2 | Адрес 2 | | 3 | Магазин 3 | Адрес 3 | +----+------------------+--------------+ +----+------------------+ | id | name | +----+------------------+ | 1 | Яблоко | | 2 | Мандарин | | 3 | Банан | +----+------------------+ - sale, таблица наличия товаров и цены:
+----------+----------+-------+-------+ | store_id | fruit_id | price | count | +----------+----------+-------+-------+ | 1 | 3 | 60 | 20 | | 2 | 2 | 80 | 10 | | 3 | 1 | 120 | 12 | +----------+----------+-------+-------+ Пример 1: базовое использование команды INSERT
Начнем с самого простого использования команды INSERT в MySQL. Добавим новый фрукт в справочник фруктов. Команда будет выглядеть так:
INSERT INTO fruits VALUES (4, 'Апельсин'); Проверяем таблицу фруктов и видим, что появилась новая запись:
+----+------------------+ | id | name | +----+------------------+ | 1 | Яблоко | | 2 | Мандарин | | 3 | Банан | | 4 | Апельсин | +----+------------------+ Мы не указывали названия столбцов, в которые хотим вставить данные. Поэтому они вставлялись по порядку, как они расположены в таблице. Поэтому нам нужно было указывать значения для всех столбцов, которые есть в таблице, даже если мы не хотим их заполнять.
Но если в таблице много столбцов, нам может быть неудобно указывать много пустых значений. Поэтому существует другой способ записи, в котором нужно явно указывать названия столбцов.
INSERT INTO stores (id, name) VALUES (4, 'Магазин 4'); Обратите внимание, что в таблице stores у нас еще есть столбец с адресом, но мы его не указали. Посмотрим, что вставилось в таблицу:
+----+------------------+--------------+ | id | name | address | +----+------------------+--------------+ | 1 | Магазин 1 | Адрес 1 | | 2 | Магазин 2 | Адрес 2 | | 3 | Магазин 3 | Адрес 3 | | 4 | Магазин 4 | NULL | +----+------------------+--------------+ Мы видим указанные нами значения, а в поле address вставилось значение NULL.
Пример 2: вставка нескольких строк
За один запрос можно добавлять сразу несколько элементов. Например, мы хотим добавить три новых фрукта. Вместо того, чтобы писать три отдельных команды INSERT, мы воспользуемся вот таким способом записи:
INSERT INTO fruits VALUES (5, 'Киви'), (6, 'Виноград'), (7, 'Груша'); Посмотрим результат. Видим, что появилось сразу три новых записи:
+----+------------------+ | id | name | +----+------------------+ | 1 | Яблоко | | 2 | Мандарин | | 3 | Банан | | 4 | Апельсин | | 5 | Киви | | 6 | Виноград | | 7 | Груша | +----+------------------+ Пример 3: использование SET
В команде INSERT мы обычно указываем отдельно столбцы и отдельно значения. Но есть способ записи, похожий на команду UPDATE: указывать столбцы и их значения через символ равенства. Такой способ может быть удобен, когда в таблице много полей и значений. Так сразу видно, в какое поле какое значение подставляется.
INSERT INTO sale SET store_id=1, fruit_id=1, price=89, count=3; Проверим, что числа вставились именно в те столбцы, которые мы указали:
+----------+----------+--------+-------+ | store_id | fruit_id | price | count | +----------+----------+--------+-------+ | 1 | 3 | 60 | 20 | | 2 | 2 | 80 | 10 | | 3 | 1 | 120 | 12 | | 1 | 1 | 89 | 3 | +----------+----------+--------+-------+ Пример 4: вставка с выражением
Во всех предыдущих примерах мы указывали явное значение для столбца. Но в MySQL можно использовать арифметические выражения, например сумму, разность, умножение и так далее. Допустим, мы хотим добавить запись в таблицу sale о том что в магазине № 2 продаются апельсины по 137 рублей. Но потом мы решаем, что хотим сделать скидку 6%. Чтобы не высчитывать ее самим, мы можем использовать такой запрос:
INSERT INTO sale VALUES (2, 4, 137*0.94, 5); Мы умножаем 137 рублей на 0.94, это и есть скидка 6%. Проверим что у нас получилось:
+----------+----------+--------+-------+ | store_id | fruit_id | price | count | +----------+----------+--------+-------+ | 1 | 3 | 60 | 20 | | 2 | 2 | 80 | 10 | | 3 | 1 | 120 | 12 | | 1 | 1 | 89 | 3 | | 2 | 4 | 128.78 | 1 | +----------+----------+--------+-------+ Пример 5: вставка данных из другой таблицы
В качестве источника данных для вставки можно использовать другую таблицу. Предположим, у нас есть таблица fruits_new, где хранится список фруктов, которые мы заказали у поставщиков, но эти фрукты еще не продаются в наших магазинах. И вот наконец-то нам привезли два новых фрукта, которые мы так давно ждали. Но вместо того, чтобы переписывать эти названия в команду INSERT, мы можем просто использовать таблицу fruits_new и загружать данные из нее. Для этого нам понадобится оператор SELECT:
INSERT INTO fruits SELECT * FROM fruits_new WHERE >Обратите внимание, что мы не указываем названия столбцов: из какого взять и в какой вносить. Поэтому такой способ подойдет только если структура таблиц одинакова, или в таблице-источнике меньше столбцов. Чаще всего используется другой синтаксис, когда столбцы указаны явно:
INSERT INTO fruits (name, id) SELECT name, id FROM fruits_new WHERE >Проверим результат. После выполнения этих двух команд у нас должно появиться две новых строки:
+----+------------------+ | id | name | +----+------------------+ | 1 | Яблоко | | 2 | Мандарин | | 3 | Банан | | 4 | Апельсин | | 5 | Киви | | 6 | Виноград | | 7 | Груша | | 8 | Ананас | | 9 | Вишня | +----+------------------+ Пример 6: вставка значения по умолчанию
Обычно, если в MySQL для столбца не указано значение, то команда INSERT INTO подставляет NULL. Но если при создании таблицы у этого столбца было указано значение по умолчанию (default), тогда подставится именно оно. В нашей таблице sale у столбца count значение по-умолчанию равно единице. Проверим это, вставив новую запись без указания этого столбца:
INSERT INTO sale (store_id, fruit_id, price) VALUES(4, 7, 50); Проверяем результат. Мы не указывали значение для столбца count, но автоматически подставлилось значение 1.
+----------+----------+--------+-------+ | store_id | fruit_id | price | count | +----------+----------+--------+-------+ | 1 | 3 | 60 | 20 | | 2 | 2 | 80 | 10 | | 3 | 1 | 120 | 12 | | 1 | 1 | 89 | 3 | | 2 | 4 | 128.78 | 5 | | 4 | 7 | 50 | 1 | +----------+----------+--------+-------+ Пример 7: вставка или обновление при дубликате
В MySQL нельзя добавить строку в таблицу, у которой дублируется первичный ключ (primary key). В наших таблицах stores и fruits поле id — это первичный ключ. Поэтому если мы попробуем добавить в таблицу fruit значение с то MySQL выдаст ошибку.
Но существует похожая на INSERT команда — REPLACE, которая умеет перезаписывать значения. Она работает как INSERT и UPDATE одновременно: если в таблице еще нет записи с таким первичным ключом, команда создаст новую запись; а если уже есть — заменит ее.
REPLACE INTO fruits VALUES (1, 'Лимон'); Проверяем результат и видим, что вместо яблока у нас появился лимон с тем же идентификатором.
+----+------------------+ | id | name | +----+------------------+ | 1 | Лимон | | 2 | Мандарин | | 3 | Банан | | 4 | Апельсин | | 5 | Киви | | 6 | Виноград | | 7 | Груша | | 8 | Ананас | | 9 | Вишня | +----+------------------+ Пример 8: игнорирование ошибки при вставке
Продолжим предыдущий пример. Допустим, записи в нашу таблицу вставляет бэкенд-сервис приложения. Мы хотим, чтобы сервис попробовал вставить новую запись, но если такой первичный ключ уже существует — ничего не обновлял и просто шел выполнять другой код. Но при совпадении ключей MySQL выдаст ошибку, а код на бэкенде прервется и ее нужно будет обрабатывать в сервисе. Гораздо проще добавить к команде INSERT ключевое слово IGNORE, чтобы при совпадении первичного ключа MySQL не генерировал ошибку:
INSERT IGNORE INTO stores VALUES (1, 'Магазин 10', 'Адрес 10'); Обратите внимание на сообщение, которое выдает MySQL, оно будет примерно таким: Query OK, 0 rows affected, 1 warning. Это значит, что запрос выполнился успешно, при этом он не затронул ни одну строки и есть одно предупреждение. Проверим результат: видим, что новых магазинов не появилось, а текущие не изменились.
+----+------------------+--------------+ | id | name | address | +----+------------------+--------------+ | 1 | Магазин 1 | Адрес 1 | | 2 | Магазин 2 | Адрес 2 | | 3 | Магазин 3 | Адрес 3 | | 4 | Магазин 4 | NULL | +----+------------------+--------------+ Пример 9: вставка записи в определенные партиции
Если таблица разбита на партиции, то при вставке данных можно сразу указать, в какой именно раздел вставлять запись. Наша таблица sales разбита на несколько партиций согласно условию:
- p0 для записей, у которых count от 0 до 100
- p1 для записей, у которых count от 101 до 150.
Чтобы вставить данные в первую партицию, воспользуемся командой:
INSERT INTO sale PARTITION (p0) VALUES (1, 9, 130, 10); Кроме того, в одной команде мы можем вставлять данные сразу в несколько партиций. Это делается так:
INSERT INTO sale PARTITION (p0, p1) VALUES (4, 8, 90, 10), (3, 7, 50, 120); Пример 10: изменение приоритета
В некоторых движках таблиц MySQL (например, в MyISAM, MEMORY и MERGE) можно изменить приоритет вставки данных. Например, можно сделать так, что если таблицу считывает команда SELECT, то команда INSERT будет ждать ее завершения. Это делается с помощью ключевого слова LOW_PRIORITY:
INSERT LOW_PRIORITY INTO sale VALUES (1, 6, 40, 3); Если же нужно наоборот поднять приоритет, используется такая команда:
INSERT HIGH_PRIORITY INTO sale VALUES (1, 6, 40, 3); Но нужно иметь ввиду, что если к таблице активно идут SELECT-запросы, то команда INSERT с низким приоритетом может ждать своей очереди довольно долго.
Заключение
Мы рассмотрели команду INSERT и познакомились с десятью самыми основными способами ее использования. Вы узнали, как с помощью SQL команды можно добавить новую строку в таблицу или обновить существующую. Зная эти основы, вы можете начать писать INSERT-запросы для своей структуры БД.
ALTER TABLE — изменение таблицы в SQL
Работа с командой UPDATE — как обновить данные в таблице MySQL
Зарегистрируйтесь в панели управления
И уже через пару минут сможете арендовать сервер, развернуть базы данных или обеспечить быструю доставку контента.
Читайте также:
Инструкция
Как автоматизировать подготовку к собеседованиям с помощью Telegram-бота
29 сентября 2023
Инструкция
Как реализовать очередь в Redis
14 сентября 2023
Инструкция
Как генерировать истории с помощью ChatGPT и Telegram