如果不存在相同的整行，则执行MySQL INSERT [英] MySQL INSERT if a identical entire row doesn't exist

问题描述

我有一个包含10列的表格，我必须从CSV文件中添加很多行.当然，我不能添加两个相同的行，因此如果整个行确实存在，则我需要一条SQL语句来忽略该命令.仅当所有字段都相同时，才必须忽略INSERT.两行可能具有相同的 field1 或 field2 ，但并非所有字段都相同.

I have a table with 10 columns and I have to add many, many rows from a CSV file. Of course, I must not add two identical rows, so I need a SQL statement that ignore the command if the entire row does exists. The INSERT must be ignored only if all fields are identical. Two rows may have identical field1 or field2, but not all fields identical.

我尝试了INSERT IGNORE，但是它不起作用.没有将任何列设置为UNIQUE，因为仅当整行相同时，才必须忽略INSERT onyl.

I tried INSERT IGNORE but it doesn't work. No column is set as UNIQUE, as the INSERT must be ignored onyl only if the entire row is identical.

您对此有什么解决方案?谢谢！

What solution do you have for this? Thanks!

推荐答案

在所有列上创建组合索引，然后根据需要创建INSERT IGNORE或REPLACE INTO.

Create a combined index on all columns, then INSERT IGNORE or REPLACE INTO according to your needs.

从文档:

如果使用IGNORE关键字，则在执行INSERT语句时发生的错误将被视为警告.例如，如果没有IGNORE，则复制表中现有的 UNIQUE索引或PRIMARY KEY值的行会导致重复键错误，并且该语句将中止.使用IGNORE，仍不会插入该行，但是不会发出错误.

If you use the IGNORE keyword, errors that occur while executing the INSERT statement are treated as warnings instead. 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 still is not inserted, but no error is issued.

这篇关于如果不存在相同的整行，则执行MySQL INSERT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！