Mysql insert ignore error

MySQL INSERT IGNORE Statement

Summary: in this tutorial, you will learn how to use the MySQL INSERT IGNORE statement to insert data into a table.

Introduction to MySQL INSERT IGNORE statement

When you use the INSERT statement to add multiple rows to a table and if an error occurs during the processing, MySQL terminates the statement and returns an error. As the result, no rows are inserted into the table.

However, if you use the INSERT IGNORE statement, the rows with invalid data that cause the error are ignored and the rows with valid data are inserted into the table.

The syntax of the INSERT IGNORE statement is as follows:

Note that the IGNORE clause is an extension of MySQL to the SQL standard.

MySQL INSERT IGNORE example

We will create a new table called subscribers for the demonstration.

The UNIQUE constraint ensures that no duplicate email exists in the email column.

The following statement inserts a new row into the subscribers table:

It worked as expected.

Let’s execute another statement that inserts two rows into the subscribers table:

It returns an error.

As indicated in the error message, the email john.doe@gmail.com violates the UNIQUE constraint.

However, if you use the INSERT IGNORE statement instead.

MySQL returned a message indicating that one row was inserted and the other row was ignored.

To find the detail of the warning, you can use the SHOW WARNINGS command as shown below:

In conclusion, when you use the INSERT IGNORE statement, instead of issuing an error, MySQL issued a warning in case an error occurs.

If you query data from subscribers table, you will find that only one row was actually inserted and the row that causes the error was not.

MySQL INSERT IGNORE and STRICT mode

When the strict mode is on, MySQL returns an error and aborts the INSERT statement if you try to insert invalid values into a table.

However, if you use the INSERT IGNORE statement, MySQL will issue a warning instead of an error. In addition, it will try to adjust the values to make them valid before adding the value to the table.

Consider the following example.

First, we create a new table named tokens :

In this table, the column s accepts only string whose lengths are less than or equal to six.

Second, insert a string whose length is seven into the tokens table.

MySQL issued the following error because the strict mode is on.

Third, use the INSERT IGNORE statement to insert the same string.

MySQL truncated data before inserting it into the tokens table. In addition, it issues a warning.

In this tutorial, you have learned how to use the MySQL INSERT IGNORE statement to insert rows into a table and ignore error for rows that cause errors.

Источник

Mysql insert on error ignore

INSERT inserts new rows into an existing table. The INSERT . VALUES , INSERT . VALUES ROW() , and INSERT . SET forms of the statement insert rows based on explicitly specified values. The INSERT . SELECT form inserts rows selected from another table or tables. You can also use INSERT . TABLE in MySQL 8.0.19 and later to insert rows from a single table. INSERT with an ON DUPLICATE KEY UPDATE clause enables existing rows to be updated if a row to be inserted would cause a duplicate value in a UNIQUE index or PRIMARY KEY . In MySQL 8.0.19 and later, a row alias with one or more optional column aliases can be used with ON DUPLICATE KEY UPDATE to refer to the row to be inserted.

In MySQL 8.0, the DELAYED keyword is accepted but ignored by the server. For the reasons for this, see Section 13.2.7.3, “INSERT DELAYED Statement”,

Inserting into a table requires the INSERT privilege for the table. If the ON DUPLICATE KEY UPDATE clause is used and a duplicate key causes an UPDATE to be performed instead, the statement requires the UPDATE privilege for the columns to be updated. For columns that are read but not modified you need only the SELECT privilege (such as for a column referenced only on the right hand side of an col_name = expr assignment in an ON DUPLICATE KEY UPDATE clause).

When inserting into a partitioned table, you can control which partitions and subpartitions accept new rows. The PARTITION clause takes a list of the comma-separated names of one or more partitions or subpartitions (or both) of the table. If any of the rows to be inserted by a given INSERT statement do not match one of the partitions listed, the INSERT statement fails with the error Found a row not matching the given partition set . For more information and examples, see Section 24.5, “Partition Selection”.

tbl_name is the table into which rows should be inserted. Specify the columns for which the statement provides values as follows:

Provide a parenthesized list of comma-separated column names following the table name. In this case, a value for each named column must be provided by the VALUES list, VALUES ROW() list, or SELECT statement. For the INSERT TABLE form, the number of columns in the source table must match the number of columns to be inserted.

If you do not specify a list of column names for INSERT . VALUES or INSERT . SELECT , values for every column in the table must be provided by the VALUES list, SELECT statement, or TABLE statement. If you do not know the order of the columns in the table, use DESCRIBE tbl_name to find out.

A SET clause indicates columns explicitly by name, together with the value to assign each one.

Column values can be given in several ways:

If strict SQL mode is not enabled, any column not explicitly given a value is set to its default (explicit or implicit) value. For example, if you specify a column list that does not name all the columns in the table, unnamed columns are set to their default values. Default value assignment is described in Section 11.6, “Data Type Default Values”. See also Section 1.6.3.3, “Enforced Constraints on Invalid Data”.

If strict SQL mode is enabled, an INSERT statement generates an error if it does not specify an explicit value for every column that has no default value. See Section 5.1.11, “Server SQL Modes”.

If both the column list and the VALUES list are empty, INSERT creates a row with each column set to its default value:

If strict mode is not enabled, MySQL uses the implicit default value for any column that has no explicitly defined default. If strict mode is enabled, an error occurs if any column has no default value.

Use the keyword DEFAULT to set a column explicitly to its default value. This makes it easier to write INSERT statements that assign values to all but a few columns, because it enables you to avoid writing an incomplete VALUES list that does not include a value for each column in the table. Otherwise, you must provide the list of column names corresponding to each value in the VALUES list.

If a generated column is inserted into explicitly, the only permitted value is DEFAULT . For information about generated columns, see Section 13.1.20.8, “CREATE TABLE and Generated Columns”.

In expressions, you can use DEFAULT( col_name ) to produce the default value for column col_name .

Type conversion of an expression expr that provides a column value might occur if the expression data type does not match the column data type. Conversion of a given value can result in different inserted values depending on the column type. For example, inserting the string ‘1999.0e-2’ into an INT , FLOAT , DECIMAL(10,6) , or YEAR column inserts the value 1999 , 19.9921 , 19.992100 , or 1999 , respectively. The value stored in the INT and YEAR columns is 1999 because the string-to-number conversion looks only at as much of the initial part of the string as may be considered a valid integer or year. For the FLOAT and DECIMAL columns, the string-to-number conversion considers the entire string a valid numeric value.

An expression expr can refer to any column that was set earlier in a value list. For example, you can do this because the value for col2 refers to col1 , which has previously been assigned:

But the following is not legal, because the value for col1 refers to col2 , which is assigned after col1 :

An exception occurs for columns that contain AUTO_INCREMENT values. Because AUTO_INCREMENT values are generated after other value assignments, any reference to an AUTO_INCREMENT column in the assignment returns a 0 .

INSERT statements that use VALUES syntax can insert multiple rows. To do this, include multiple lists of comma-separated column values, with lists enclosed within parentheses and separated by commas. Example:

Each values list must contain exactly as many values as are to be inserted per row. The following statement is invalid because it contains one list of nine values, rather than three lists of three values each:

VALUE is a synonym for VALUES in this context. Neither implies anything about the number of values lists, nor about the number of values per list. Either may be used whether there is a single values list or multiple lists, and regardless of the number of values per list.

INSERT statements using VALUES ROW() syntax can also insert multiple rows. In this case, each value list must be contained within a ROW() (row constructor), like this:

The affected-rows value for an INSERT can be obtained using the ROW_COUNT() SQL function or the mysql_affected_rows() C API function. See Section 12.16, “Information Functions”, and mysql_affected_rows().

If you use INSERT . VALUES or INSERT . VALUES ROW() with multiple value lists, or INSERT . SELECT or INSERT . TABLE , the statement returns an information string in this format:

If you are using the C API, the information string can be obtained by invoking the mysql_info() function. See mysql_info().

Records indicates the number of rows processed by the statement. (This is not necessarily the number of rows actually inserted because Duplicates can be nonzero.) Duplicates indicates the number of rows that could not be inserted because they would duplicate some existing unique index value. Warnings indicates the number of attempts to insert column values that were problematic in some way. Warnings can occur under any of the following conditions:

Inserting NULL into a column that has been declared NOT NULL . For multiple-row INSERT statements or INSERT INTO . SELECT statements, the column is set to the implicit default value for the column data type. This is 0 for numeric types, the empty string ( » ) for string types, and the “ zero ” value for date and time types. INSERT INTO . SELECT statements are handled the same way as multiple-row inserts because the server does not examine the result set from the SELECT to see whether it returns a single row. (For a single-row INSERT , no warning occurs when NULL is inserted into a NOT NULL column. Instead, the statement fails with an error.)

Setting a numeric column to a value that lies outside the column range. The value is clipped to the closest endpoint of the range.

Assigning a value such as ‘10.34 a’ to a numeric column. The trailing nonnumeric text is stripped off and the remaining numeric part is inserted. If the string value has no leading numeric part, the column is set to 0 .

Inserting a string into a string column ( CHAR , VARCHAR , TEXT , or BLOB ) that exceeds the column maximum length. The value is truncated to the column maximum length.

Inserting a value into a date or time column that is illegal for the data type. The column is set to the appropriate zero value for the type.

For INSERT examples involving AUTO_INCREMENT column values, see Section 3.6.9, “Using AUTO_INCREMENT”.

If INSERT inserts a row into a table that has an AUTO_INCREMENT column, you can find the value used for that column by using the LAST_INSERT_ID() SQL function or the mysql_insert_id() C API function.

These two functions do not always behave identically. The behavior of INSERT statements with respect to AUTO_INCREMENT columns is discussed further in Section 12.16, “Information Functions”, and mysql_insert_id().

The INSERT statement supports the following modifiers:

If you use the LOW_PRIORITY modifier, execution of the INSERT is delayed until no other clients are reading from the table. This includes other clients that began reading while existing clients are reading, and while the INSERT LOW_PRIORITY statement is waiting. It is possible, therefore, for a client that issues an INSERT LOW_PRIORITY statement to wait for a very long time.

LOW_PRIORITY affects only storage engines that use only table-level locking (such as MyISAM , MEMORY , and MERGE ).

LOW_PRIORITY should normally not be used with MyISAM tables because doing so disables concurrent inserts. See Section 8.11.3, “Concurrent Inserts”.

If you specify HIGH_PRIORITY , it overrides the effect of the —low-priority-updates option if the server was started with that option. It also causes concurrent inserts not to be used. See SectionВ 8.11.3, “Concurrent Inserts”.

HIGH_PRIORITY affects only storage engines that use only table-level locking (such as MyISAM , MEMORY , and MERGE ).

If you use the IGNORE modifier, ignorable errors that occur while executing the INSERT statement are ignored. For example, without IGNORE , a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted. With IGNORE , the row is discarded and no error occurs. Ignored errors generate warnings instead.

IGNORE has a similar effect on inserts into partitioned tables where no partition matching a given value is found. Without IGNORE , such INSERT statements are aborted with an error. When INSERT IGNORE is used, the insert operation fails silently for rows containing the unmatched value, but inserts rows that are matched. For an example, see Section 24.2.2, “LIST Partitioning”.

Data conversions that would trigger errors abort the statement if IGNORE is not specified. With IGNORE , invalid values are adjusted to the closest values and inserted; warnings are produced but the statement does not abort. You can determine with the mysql_info() C API function how many rows were actually inserted into the table.

You can use REPLACE instead of INSERT to overwrite old rows. REPLACE is the counterpart to INSERT IGNORE in the treatment of new rows that contain unique key values that duplicate old rows: The new rows replace the old rows rather than being discarded. See Section 13.2.12, “REPLACE Statement”.

If you specify ON DUPLICATE KEY UPDATE , and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY , an UPDATE of the old row occurs. The affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values. If you specify the CLIENT_FOUND_ROWS flag to the mysql_real_connect() C API function when connecting to mysqld , the affected-rows value is 1 (not 0) if an existing row is set to its current values. See Section 13.2.7.2, “INSERT . ON DUPLICATE KEY UPDATE Statement”.

INSERT DELAYED was deprecated in MySQL 5.6, and is scheduled for eventual removal. In MySQL 8.0, the DELAYED modifier is accepted but ignored. Use INSERT (without DELAYED ) instead. See Section 13.2.7.3, “INSERT DELAYED Statement”.

Источник

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

Задача

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

Создать индекс на несколько столбцов можно так:

ALTER TABLE `db_name`.`table_name` ADD UNIQUE `name_index` (`app`, `email`, `other_field`);

Где db_name — имя вашей базы данных, table_name — имя таблицы в базе, name_index — название индекса, `app`, `email`, `other_field` — группируемые поля в уникальный индекс.

Решение

Один из способов – просто игнорировать ошибку, другой – использовать предложение INSERT IGNORE или REPLACE, каждое из которых изменяет поведение MySQL в отношении обработки повторений. 

Обсуждение

По умолчанию MySQL генерирует ошибку при вставке записи, дублирующей существующий уникальный ключ. Например, если таблица person содержит уникальный индекс для столбцов last_name и first_name, то вы увидите следующее:

mysql> INSERT INTO person (last_name, first_name)
-> VALUES('X1','Y1');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO person (last_name, first_name)
-> VALUES('X1','Y1');
ERROR 1062 at line 1: Duplicate entry 'X1-Y1' for key 1

Если вы интерактивно запускаете предложения из программы mysql, то можете просто сказать: «Понял, не сработало», игнорировать ошибку и продолжать работу.

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

Если вы хотите предотвратить появление ошибки, то, вероятно, подумываете о решении задачи обработки дубликатов с помощью двух запросов: запустите SELECT, чтобы определить, есть ли уже такая запись, а затем – INSERT, если записи еще нет. Но на самом деле ничего не получится. Другое клиентское приложение может вставить такую же запись в промежуток между вашими SELECT и INSERT, и тогда опять-таки сгенерируется ошибка. Чтобы это не произошло, можно заключить два предложения в транзакцию или заблокировать таблицы, но тогда вместо двух предложений у вас появится четыре. MySQL предлагает два решения задачи обработки дубликатов, каждое из которых состоит из единственного предложения:

Первый cпособ: Используйте предложение INSERT IGNORE вместо INSERT.

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

mysql> INSERT IGNORE INTO person (last_name, first_name)
-> VALUES('X2','Y2');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT IGNORE INTO person (last_name, first_name)
-> VALUES('X2','Y2');
Query OK, 0 rows affected (0.00 sec)

Значение счетчика строк показывает, была запись вставлена или проигнорирована.

В программе вы можете получить это значение, используя функцию подсчета обработанных строк, имеющуюся в вашем API.

Второй способ: Используйте предложение REPLACE вместо INSERT.

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

mysql> REPLACE INTO person (last_name, first_name)
-> VALUES('X3','Y3');
Query OK, 1 row affected (0.00 sec)
mysql> REPLACE INTO person (last_name, first_name)
-> VALUES('X3','Y3');
Query OK, 2 rows affected (0.00 sec)

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

Выбор INSERT IGNORE или REPLACE зависит от того, какое поведение для вас предпочтительно. INSERT IGNORE хранит первую из множества повторяющихся записей и удаляет остальные. REPLACE хранит последний из дубликатов и удаляет все остальные. Предложение INSERT IGNORE эффективнее, чем REPLACE, так как дубликаты не вставляются в таблицу. То есть его лучше применять, когда вы просто хотите убедиться в том, что копия указанной записи содержится в таблице.

С другой стороны, REPLACE больше подходит для таблиц, в которых может потребоваться обновление других столбцов, не входящих в ключ. Предположим, что у вас есть таблица users, используемая в веб-приложении для хранения адресов электронной почты и паролей, в которой email является ключом:

CREATE TABLE users
(
email CHAR(60) NOT NULL,
password CHAR(20) BINARY NOT NULL,
PRIMARY KEY (email)
);

Как создавать записи для новых пользователей и изменять пароли для существующих? Без REPLACE, создание нового пользователя и изменение пароля существующего, обрабатывались бы по-разному. Стандартный алгоритм мог бы быть таким:

1. Запустить SELECT, чтобы проверить, существует ли уже запись с указанным значением email.
2. Если такой записи нет, добавить новую при помощи INSERT.
3. Если запись существует, обновить ее при помощи UPDATE.

Все это можно выполнить внутри транзакции или заблокировав таблицы, чтобы запретить другим пользователям изменять таблицы в течение того времени, пока вы с ними работаете. Применив REPLACE, вы можете свести оба случая к одному предложению:

REPLACE INTO users (email,password) VALUES(адрес,пароль);

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

Источник: 
https://oooportal.ru/?cat=arti…

« Все статьи

Sometimes you may need to prevent duplicate insert while adding rows to MySQL table. You can easily do this using INSERT IGNORE statement. In this article, we will look at how to avoid inserting duplicate records in MySQL.

Here are the steps to avoid inserting duplicate records in MySQL. There are two parts to keep in mind. First, you need to create a UNIQUE column index for your table. Next, you need to insert data using INSERT IGNORE to avoid duplicate records.

Let us say you have the following table sales(id, order_date, amount)

mysql> create table sales(
          id int, 
          order_date date, 
          amount int
          );

Also read : How to Get Multiple Counts in MySQL

1. Create Unique Index

We add a UNIQUE index for id column using ALTER TABLE statement.

Here is the syntax to create UNIQUE index in MySQL.

alter table table_name ADD UNIQUE INDEX(
        column_name1, 
        column_name2,
        ...
        column_nameN
        );

Here is the SQL query to add UNIQUE index to id column in sales table.

mysql> alter table sales ADD UNIQUE INDEX(id);

Also read : How to Convert datetime to UTC in MySQL

2. Insert data using INSERT IGNORE

Next, we will insert data using INSERT IGNORE.

Before we proceed, let us look at what is INSERT IGNORE in MySQL and how does INSERT IGNORE work.

What is INSERT IGNORE?

INSERT IGNORE is a command that forces MySQL to ignore errors when you insert data into a MySQL table.

Also read : How to Get Current Date and Time in MySQL

How does INSERT IGNORE work?

In our case, we have already created a UNIQUE index for id column. If we use INSERT statement to add data with duplicate rows, MySQL will throw an error and stop query execution after the first duplicate row.

Instead, when we use INSERT IGNORE, MySQL will silently discard the insertion of duplicate row without generating an error, and continue query execution.

So if a record does not contain duplicate data, MySQL will insert it as usual. If it is a duplicate record, then MySQL will simply ignore it.

Also read : How to Group By Month in MySQL

Here is the SQL query to insert data without duplicates in sales table.

mysql> mysql> insert ignore into sales(id,order_date,amount)
     values(1, '2021-01-01', 250),
     (1, '2021-01-01', 250),
     (2, '2021-01-02', 200),
     (3, '2021-01-03', 150),
     (2, '2021-01-02', 200);
Query OK, 3 rows affected (0.10 sec)
Records: 5  Duplicates: 2  Warnings: 0

mysql> select * from sales;
+------+------------+--------+
| id   | order_date | amount |
+------+------------+--------+
|    1 | 2021-01-01 |    250 |
|    2 | 2021-01-02 |    200 |
|    3 | 2021-01-03 |    150 |
+------+------------+--------+

As you can see, MySQL avoids inserting duplicate rows, without generating any error.

Need a reporting tool for MySQL? Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!