将PHP数组中的唯一值插入到SQL表[duplicate]中

Possible Duplicate:
How to 'insert if not exists' in MySQL?

可能的重复:如何在MySQL中“插入如果不存在”?

There is SQL table:

SQL表:

CREATE TABLE IF NOT EXISTS `MyTable` (
  `id` smallint(6) NOT NULL AUTO_INCREMENT,
  `MyVar` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

And there are two PHP arrays arr1[]=array("abc","bcd") and arr2[]=array("abc","cde").

有两个PHP数组arr1[]=array(“abc”、“bcd”)和arr2[]=array(“abc”、“cde”)。

Let's say I have saved arr1[] values to SQL table. Now let's suppose I need to save arr2[] values to the same SQL table. Which SQL INSERT query do I need to write in order to avoid duplicate saving of "abc" entry? The result must be:

假设我将arr1[]值保存到SQL表中。现在假设我需要将arr2[]值保存到同一个SQL表中。为了避免“abc”条目的重复保存，我需要编写哪些SQL INSERT查询?结果必须是:

MyTable:
1  |  abc
2  |  bcd
3  |  cde

but NOT:

而不是:

MyTable:
1  |  abc
2  |  bcd
3  |  abc
4  |  cde

UPDATE: Maybe the MyTable should be created/defined in such a way that duplicate entries are ignored?

更新:也许应该以忽略重复条目的方式创建/定义MyTable ?

3 个解决方案

#1

Make the MyVar as UNIQUE in your table.

使MyVar在表中是唯一的。

Like this:

是这样的:

CREATE TABLE IF NOT EXISTS `MyTable` (
  `id` smallint(6) NOT NULL AUTO_INCREMENT,
  `MyVar` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `myvaridx` (`MyVar`)
);

or, if you're unable to recreate the table, alter it using

或者，如果无法重新创建表，可以使用它进行修改

ALTER TABLE `request`
ADD UNIQUE INDEX `myvaridx` (`MyVar`)

#2

There are 3 possible solutions: using INSERT IGNORE, REPLACE, or INSERT ... ON DUPLICATE KEY UPDATE. Check this article.

有三种可能的解决方案:使用INSERT IGNORE、REPLACE或INSERT…在重复键更新。本文检查。

You can also in memory intersect array-s and then insert just unique values if that solution fits for you.

你也可以在内存中插入arrays -s，如果这个解决方案适合你，就插入唯一的值。

#3

Using SQL for such task would be wasteful.

在此类任务中使用SQL将是一种浪费。

I suggest to use using the "array_merge" function to merge the arrays and then "array_unique" function to handle the duplication.

我建议使用“array_merge”函数合并数组，然后使用“array_unique”函数处理重复。

Then insert the unique values to the database.

然后向数据库插入唯一的值。

#1