mysql++防止重复输入,php / mysql防止多列重复输入-优快云博客

该博客探讨了如何在MySQL中通过设置多字段唯一键来避免表格中出现重复数据。作者提到，通常情况下，重复数据是多个字段组合的结果，而非单一字段。通过ALTER TABLE命令添加一个覆盖多个字段的唯一索引可以实现这一目标。如果尝试插入的数据违反了这个唯一约束，INSERT IGNORE语句将不会引发错误，而是会忽略掉该插入操作。这样可以确保如followers和followed这类组合的唯一性。

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.

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.

解决方案

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.

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

ALTER TABLE table ADD UNIQUE KEY (colA,colB)

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.