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.

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

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 手册 http://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html.

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屋！