如何检查mysql表中多行中与每行中唯一数据关联的值

Question

I'm developing a record management application using php. I've designed a table named records with fields id, recordname, recordvalue, recordid. For example if a record consists of 3 data firstname, lastname, gender. 3 data (John Doe Male, Jane Doe Female and Sherlcok Holmes Male) is inserted in the table as below-

----+-------------+--------------+-----------
id  | recordname  | recordvalue  | recordid
----+-------------+--------------+-----------
1   | firstname   | John         | 001
2   | lastname    | Doe          | 001
3   | gender      | Male         | 001
4   | firstname   | Jane         | 002  
5   | lastname    | Doe          | 002
6   | gender      | Female       | 002
7   | firstname   | Sherlock     | 003
8   | lastname    | Holmes       | 003
9   | gender      | Male         | 003

----+-------------+--------------+-----------

my question is when i'm going to insert a new record in the table i need to check if there is any duplication of the record. That means any field for different records can be duplicated anytime but all the fields having same values for a record can't be duplicated. From previous example records having value John Doe Female, Jane Doe Male, Sherlock Doe Male etc are OK but again inserting John Doe Male is not expected and it'll give an error.

How can i achieve this easily with PHP?

Thanks in advance.

Answer 1

my question is when i'm going to insert a new record in the table i need to check if there is any duplication of the record.

Your “records” are something different than a normal database record (= one row) here.

That means any field for different records can be duplicated anytime but all the fields having same values for a record can't be duplicated. From previous example records having value John Doe Female, Jane Doe Male, Sherlock Doe Male etc are OK but again inserting John Doe Male is not expected and it'll give an error.

So the combination of several records has to be unique in your case.

How can i achieve this easily with PHP?

You could SELECT first and see if you get a result for the data to be inserted already, as Yadav suggested in his answer. But that will result in a possible TOCTTOU problem – to avoid that, you will have to encapsulate SELECT and INSERT into a transaction.

The other way would be to use an INSERT … SELECT statement. The SELECT statement must be formulated in a way that it only returns values to insert if it does not find them already – counting the matching records and using HAVING to filter for only those that give the right number of records (three in your example) would do that.