mysql - 如何在 EAV 表和另一个 MySQL 中进行搜索

Question

我尝试使用 OR 语句进行查询以查找 EAV 表 keywords 和 documents 表中的记录，但它给出了重复的结果:

SELECT d.id, d.name, d.created, d.updated 
FROM documents d 
INNER JOIN permissions p ON (d.wfid = p.wfId AND d.docid = p.docId AND p.userid = 1)
INNER JOIN keywords k0 ON (k0.document_id = d.id AND (k0.value IN('Pérez, Gabriel', 
  'DOC1') OR d.name IN ('Pérez, Gabriel', 'DOC1') ))
ORDER BY d.id DESC;

+------+------+---------------------+---------------------+
| id   | name | created             | updated             |
+------+------+---------------------+---------------------+
| 5267 | DOC1 | 2014-03-07 11:41:23 | 2014-02-25 12:39:10 |
| 5267 | DOC1 | 2014-03-07 11:41:23 | 2014-02-25 12:39:10 |
| 5267 | DOC1 | 2014-03-07 11:41:23 | 2014-02-25 12:39:10 |
| 5266 | DOC1 | 2014-03-07 11:41:23 | 2014-03-06 01:35:16 |
| 5266 | DOC1 | 2014-03-07 11:41:23 | 2014-03-06 01:35:16 |
| 5266 | DOC1 | 2014-03-07 11:41:23 | 2014-03-06 01:35:16 |
| 5265 | DOC1 | 2014-03-07 11:41:23 | 2014-02-25 12:38:37 |
| 5265 | DOC1 | 2014-03-07 11:41:23 | 2014-02-25 12:38:37 |
| 5265 | DOC1 | 2014-03-07 11:41:23 | 2014-02-25 12:38:37 |
| 5264 | DOC1 | 2014-03-07 11:41:23 | 2014-02-25 12:38:25 |
| 5264 | DOC1 | 2014-03-07 11:41:23 | 2014-02-25 12:38:25 |
| 5264 | DOC1 | 2014-03-07 11:41:23 | 2014-02-25 12:38:25 |
| 5263 | DOC1 | 2014-04-07 11:23:00 | 2014-03-20 09:54:30 |
| 5263 | DOC1 | 2014-04-07 11:23:00 | 2014-03-20 09:54:30 |
| 5263 | DOC1 | 2014-04-07 11:23:00 | 2014-03-20 09:54:30 |
+------+------+---------------------+---------------------+
15 rows in set (0.00 sec)

如果我使用SELECT DISTINCT

+------+-----------------------------+---------------------+---------------------+
| id   | name                        | created             | updated             |
+------+-----------------------------+---------------------+---------------------+
| 5355 | Documento - Digitalización  | 2014-03-31 12:57:51 | 2014-03-31 12:57:51 |
| 5354 | Documento - Digitalización  | 2014-03-28 11:14:11 | 2014-03-28 11:14:11 |
| 5267 | DOC1                        | 2014-03-07 11:41:23 | 2014-02-25 12:39:10 |
| 5266 | DOC1                        | 2014-03-07 11:41:23 | 2014-03-06 01:35:16 |
| 5265 | DOC1                        | 2014-03-07 11:41:23 | 2014-02-25 12:38:37 |
| 5264 | DOC1                        | 2014-03-07 11:41:23 | 2014-02-25 12:38:25 |
| 5263 | DOC1                        | 2014-04-07 11:23:00 | 2014-03-20 09:54:30 |
+------+-----------------------------+---------------------+---------------------+
7 rows in set (0.00 sec)

然后我就得到了我想要的。

我的问题是:

1)为什么第一行是重复的？

2)使用 SELECT DISTINCT 不好还是有助于提高性能？

3) 我的查询写得不好吗？我需要在文档表和关键字表中搜索输入的值，以查看是否存在匹配项(如 Google 搜索)

mysql> describe documents;
+---------+-----------+------+-----+---------------------+-----------------------------+
| Field   | Type      | Null | Key | Default             | Extra                       |
+---------+-----------+------+-----+---------------------+-----------------------------+
| id      | int(11)   | NO   | PRI | NULL                | auto_increment              |
| name    | char(100) | YES  |     | NULL                |                             |
| wfid    | char(50)  | YES  |     | NULL                |                             |
| docid   | char(50)  | YES  |     | NULL                |                             |
| created | timestamp | NO   |     | 0000-00-00 00:00:00 |                             |
| updated | timestamp | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
+---------+-----------+------+-----+---------------------+-----------------------------+
6 rows in set (0.00 sec)

mysql> describe keywords;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| document_id | int(11)      | NO   | MUL | NULL    |                |
| keyword     | char(50)     | NO   |     | NULL    |                |
| value       | varchar(250) | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

Answer 1

很难从您的问题中辨别关键字表的确切形式，因此这个答案可能不正确。

但是，通常可以这样访问关键字(名称/值对)表，以仅检索一个关键字的值。

 SELECT d.id, d.name, d.created, d.updated, 
        k0.value AS doc1_value,
        k1.value AS doc2_value 
   FROM documents AS d
   LEFT JOIN keywords AS k0 ON k0.document_id = d.id AND k0.name = 'DOC1'
   LEFT JOIN keywords AS k1 ON k1.document_id = d.id AND k1.name = 'DOC2'

这将为每个文档检索名称为 DOC1 和 DOC2 的关键字属性的值。使用 LEFT JOIN 来处理特定文档可能缺少关键字值的情况。

您是否正在寻找具有包含特定值的属性的文档？在这种情况下，您可能需要执行以下操作:

 SELECT d.id, d.name, d.created, d.updated, 
        k0.value AS doc1_value,
        k1.value AS doc2_value 
   FROM documents AS d
   JOIN keywords AS sk ON sk.document_id = d.id AND sk.value = 'Pérez, Gabriel'
   LEFT JOIN keywords AS k0 ON k0.document_id = d.id AND k0.name = 'DOC1'
   LEFT JOIN keywords AS k1 ON k1.document_id = d.id AND k1.name = 'DOC2'

请注意使用 JOIN 而不是 LEFT JOIN 来排除不匹配的行。