唯一约束，如何避免重复 [英] Unique constraint, how to avoid duplicates

问题描述

根据我之前的查询该帖子我有一个看起来像这样的表:

According to my previous Query that post i have a table that looks like this:

|| *nid* || *language* ||
|| 8 || Chinese ||
|| 8 || Portuguese ||
|| 8 || German |

其中"nid"和"language"具有唯一约束.

In which 'nid' and 'language' have a unique constraint.

通过这种设置，我如何确保在尝试插入新行时不会重复?

With this setup how can i make sure that the there wont be any duplicate when i try to insert a new row ?

已编辑

我猜我应该尝试进行以下查询:

I am guessing I should try to make a query such as:

SELECT * FROM lang WHERE nid = $nid AND language = $lang

如果此返回FALSE，那么我知道我现在可以插入我的数据了.这是正确的吗?

If this return FALSE then i know i can now insert my data. Is this correct ?

推荐答案

通过创建唯一键来强制执行唯一约束:

Enforce the unique constraint by creating a unique key:

ALTER TABLE the_table
ADD UNIQUE INDEX nid_language_unique (nid, language);

此约束禁止两行具有相同的nid和语言.

This constraint forbid two rows having the same nid and language.

任何试图违反约束的查询都会失败.

Any query attempting to violate the constraint will fail.

当您想忽略错误(并仍然中止查询)时，可以使用 UPDATE IGNORE :

As you want to ignore errors (and still abort the query), you can use INSERT IGNORE and UPDATE IGNORE:

INSERT IGNORE INTO the_table (nid, language) VALUES (8, 'Chinese')
/* row not inserted and no error */

这篇关于唯一约束，如何避免重复的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！