php/mysql防止多列重复输入 [英] php/mysql prevent duplicate entries over multiple columns

问题描述

我想提出一种标准做法，以防止任何表在有问题的地方重复.在大多数情况下，重复项是变量的组合而不是一个.我的主键只是每个字段的唯一ID，因此我无法使用它们.我一直在做的是先查询表，然后查询所讨论的组合的行数是否为0，然后进行插入.但是，我已经读过，应该有可能在多个字段上设置一个唯一键以强制执行唯一性. INSERT IGNORE听起来很可能，但是，我需要在多个列上忽略它.

I would like to come up with a standard practice to prevent any tables from having duplicates where it matters. In most cases duplicates are a combination of variables rather than one. My primary keys are just the unique ids for each field so I cannot use them. What I have been doing is querying the table first and then if the number of rows for the combination in question is 0, making the insert. However, I have read it should be possible to set up a unique key over multiple fields to enforce uniqueness. INSERT IGNORE sounds like a good possibility, however, I would need it to ignore on more than one column.

例如，对于字段关注者和关注对象，一个表中可以有多个关注者和关注对象，但只能是两者的组合.

As an example, with the fields followers and followed, there can be multiple followers and followeds in a table but should only be one combination of both.

谁能建议语法先在多个字段上创建唯一键，然后再执行防止插入重复的SQL插入查询?非常感谢.

Can anyone suggest syntax first to create the unique keys over multiple fields and then to do a SQL insert query that prevents dupes? Many thanks.

推荐答案

您可以在这些列上创建一个多列索引并强制唯一性:请参见MySQL手册，网址为

You can simply create a multiple-column index on these columns and enforce uniqueness: see the MySQL manual at http://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html.

例如，在具有列id(唯一主键)，colA和colB的表中，运行:

For example, in a table with columns id (unique primary key), colA and colB, you run:

ALTER TABLE table ADD UNIQUE KEY (colA,colB)

就是这样:在这两个列中导致重复条目的所有INSERT现在都将返回MySQL错误，而不是进行遍历.如果您使用INSERT IGNORE，则如果执行它将违反此唯一约束，则不会引发MySQL错误，并且您的INSERT语句将被忽略.

This is it: any INSERTs leading to a duplicate entry in these two columns combined will now return a MySQL error instead of going through. If you use INSERT IGNORE, no MySQL error will be thrown if executing it would violate this unique constraint, and your INSERT statement would be quietly disregarded.

这篇关于php/mysql防止多列重复输入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！