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

----+-------------+--------------+-----------
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
----+-------------+--------------+-----------

我想你应该修理一下你的桌子，它的设计真的很糟糕。应该是这样的

table
--------------------------------------------
record_id | firstname | lastname | gender

，然后你可以使用一个UNIQUE INDEX(名字，姓氏，性别)来控制，如果你想保持它只在你的数据库。

[编辑]

由于你不能改变数据库的设计，它使用EAV结构，你可以使用下面的查询来实现你正在寻找的。这个查询将返回是否有基于你的条件的记录。

SELECT a.recordvalue, b.recordvalue, c.recordvalue
FROM rec_eav a
INNER JOIN rec_eav b ON (b.recordid = a.recordid AND b.recordname = 'lastname' )
INNER JOIN rec_eav c ON (c.recordid = a.recordid AND c.recordname = 'gender' )
WHERE a.recordname = 'firstname' 
    AND a.recordvalue = 'John'
    AND b.recordvalue = 'Doe'
    AND c.recordvalue = 'Male' 

我的问题是，当我要在表中插入一个新记录时，我需要检查是否有任何重复的记录。

这里的"记录"与正常的数据库记录(=一行)有所不同。

这意味着不同记录的任何字段都可以在任何时候复制，但是对于一条记录具有相同值的所有字段都不能复制。从前面的例子中，记录值为"John Doe Female"、"Jane Doe Male"、"Sherlock Doe Male"等都是可以的，但是再次插入"John Doe Male"是不期望的，它会给出一个错误。

因此，在您的情况下，几个记录的组合必须是唯一的。

如何用PHP轻松实现这一点?

你可以先SELECT，看看你是否已经得到了要插入的数据的结果，正如Yadav在他的回答中建议的那样。但是这将导致一个可能的TOCTTOU问题——为了避免这个问题，你必须将SELECT和INSERT封装到一个事务中。

另一种方法是使用INSERT…SELECT语句。SELECT语句必须以这样的方式制定:只有在没有已经找到时才返回要插入的值——计算匹配的记录并使用HAVING来过滤那些给出正确记录数量的记录(在您的示例中为三个)才能做到这一点。

您将需要这样的内容:

SELECT a.recordid
FROM test a
JOIN test b ON ( b.recordname = 'firstname' AND b.recordvalue = 'John' AND b.recordid = a.recordid)
JOIN test c ON ( c.recordname = 'lastname' AND c.recordvalue = 'Doe' AND c.recordid = a.recordid)
WHERE (a.recordname = 'gender' AND a.recordvalue = 'female')

如果结果是空的，那么你可以继续(就像在我的例子中，如果你改变性别值为男性，它将返回一个记录，因此这个组合已经存在)

如果你要使用一个EAV模型来存储数据，那么考虑这样的结构:

eav_hell(entity*,attribute*,value) 
* = (component of) PRIMRY KEY

对于丢弃的可怜的数据类型也要考虑一下。

执行如下命令:

select count(*) countmatch
from datatable
where concat(recordname,':',recordvalue) in
      ('firstname:John', 'lastname:Doe', 'gender:Male')
group by recordid
order by countmatch desc
limit 1

—如果返回的countmatch值为3，则拒绝新的John Doe Male记录，否则将其添加到表中。

编辑:如果名字、姓氏和性别一起唯一地标识一条记录，但其他属性可能是稀疏和/或任意填充的，那么您可能需要考虑混合模式-可能像这样:

KeyTable
--------
firstname
lastname
gender
recordid

-在名字、姓氏和性别的组合上有一个唯一索引，在recordid上有一个单独的唯一索引。

DataTable
---------

-和现在一样，在recordid上有一个外键。请注意，名字、姓氏和性别属性应该而不是存储在该表中。

当尝试添加名字，姓氏和性别的"新"组合时，尝试将它们添加到KeyTable中-如果插入被DBMS拒绝，则意味着该记录已经存在。

在这个场景中，存储在DataTable上的recordname/recordvalue组合应该始终包含一个链接到KeyTable上的有效recordid的recordid。

您可以尝试下面的查询，如果返回答案，则您已经有类似的数据。如果不是，它将返回0行:

select recordid from records 
where recordvalue in ('John', 'Doe', 'Male')
group by recordid
having count(recordid) = 3

编辑:以上查询可能会给出多个结果，正如@Xavjer所解释的那样，试试这个:

select * from 
(
select 
max(case when recordname="firstname" then recordvalue end) as firstname,
max(case when recordname="lastname" then recordvalue end) as lastname,
max(case when recordname="gender" then recordvalue end) as gender,
recordid
from records
group by recordid
) as record
where firstname='doe'
and lastname='john'
and gender='male'

感谢大家的资助。总之，我找到了解决问题的办法。这很棘手，我不知道它的效率如何。所以我把它贴在这里征求专家的意见。谢谢。

我的问题是-我有一个EAV表，其中单个实体具有多个属性和相应的值。我需要检查是否有人提交了一个表单与记录的一些值，所有这些都已经在数据库中作为另一个记录。例如，如果John Doe Male已经在 recordid001下的表中，那么用户不能将John Doe Male保存为另一条记录，而是会给用户一条消息"记录已经存在!"。

我执行了一些查询-

foreach($submitted_values as $name => $value){
    $r = mysql_query("SELECT DISTINCT `recordid` FROM `records` WHERE `recordname` = '$name' AND `recordvalue` = '$value'");                
    while($row  = mysql_fetch_assoc($r)){
        $assoc[$name][] = $row['recordid']; 
    }
}

所以在$assoc中，我得到了一个用字段名索引的二维数组，并与每个匹配的recordid数组相关联。

然后我检查了$assoc数组中是否有任何匹配的值，如果我得到一个非空数组，我确信在单个记录下提交的值有一个或多个重复。

这个过程还帮助我告诉用户有一些部分匹配的值。这对我来说非常有效。

再次感谢大家浪费了你宝贵的时间。