MySQL:查找数据库和数组中的值之间的存在性

Question

我想知道如何进行唯一查询来查找哪些值存在以及哪些值不存在。我解释一下。

我有什么

我有一个结构如下的数据库表:

+----+--------+-----------+-----------+
| id | action | button_id |   type    |
+----+--------+-----------+-----------+
|  1 |      1 |         1 | button    |
|  2 |      2 |         4 | button    |
|  3 |      1 |         2 | attribute |
+----+--------+-----------+-----------+

如您所见，一个 action 可以有多个 button_id 值。据您所知，一个 button_id 也可以分配给多个 action，但 button_id 只能有一个 type > 进行操作。

因此，button_id 1 也可以出现在 action 4 中，并为其设置了 type“属性”，但它不能重复使用另一个类型执行相同的操作。

问题

当我想要更新操作中的按钮时，问题就出现了。我收到一个 action 对象，其中包含一个按钮数组(在 PHP 中)，其结构如下(我用 JSON 结构编写):

"buttons":
    [
       {
          "id":"1",
          "type":"button"
       },
       {
          "id":"3",
          "type":"attribute"
       }
    ]

如您所见，ID 为 1 的按钮保持不变，但我有一个新按钮需要处理(ID 为 3 的按钮)，而 ID 为 2 的按钮不再存在。

我想要什么

我希望能够创建一个唯一的 MySQL 查询，它返回我收到的值中哪些值存在，哪些不存在，哪些值可能存在于数据库中，但不存在于数据库中大批。

总结:我想知道收到的数组中的按钮与数据库中存在的按钮之间的差异。

因此，作为示例，使用前面描述的接收到的数据和我们现在拥有的数据库，我期望收到如下内容:

+--------+-----------+--------+------------+
| action | button_id | exists | is_present |
+--------+-----------+--------+------------+
|      1 |         1 |      1 |          1 |
|      1 |         2 |      1 |          0 |
|      1 |         3 |      0 |          1 |
+--------+-----------+--------+------------+

有了这些信息，我就能够知道 ID 为 2 的按钮不再存在(因为它不存在于新数组中)，而 ID 为 3 的按钮是一个新按钮，因为它以前不存在，但存在于新数组中。

我尝试过的

我尝试过一些测试，但没有一个能够满足我的需求，而且不仅仅使用 MySQL 纯查询进行了测试。

例如，我尝试检查收到的每个按钮是否存在，但这将使我无法找到按钮是否已删除(因此它不存在于收到的数组中)。

检查但以数据库中的按钮作为引用具有相同的效果，因为我将能够检查哪些已更新或删除，但它会跳过那些新的且不存在于数据库中的按钮。

我尝试编写一些查询，进行 COUNT 查询和 GROUP BY button_id 等，但也没有运气。

(我不会写查询，因为它们都没有给我预期的结果，所以它们对你没有任何帮助)。

我认为之前解释过的任何组合都会比纯粹通过数据库查询慢得多，这就是我要求它的原因。

问题

是否有一个查询会返回类似于之前在“我想要什么”部分中解释的响应，因此它会仅调用 MySQL 服务器？

感谢大家的时间、回复以及对我可能发现的任何信息缺乏的耐心。

当然，如果您有任何疑问、问题或可能需要的信息，请发表评论，我会尽力更好地解释或添加它。

亲切的问候。

Answer 1

在单个查询中执行此操作会非常麻烦。这是一个不完全是您正在寻找的解决方案，但应该可以完成工作。

假设您的表格如下所示:

CREATE TABLE htmlComponent
(
  id int auto_increment primary key,
  action int,
  button_id int not null,
  type varchar(20),
  dtInserted datetime,
  dtUpdated datetime
);

CREATE UNIQUE INDEX buttonType ON htmlComponent(button_id, type);

现在我们需要根据您针对特定操作拥有的按钮/属性来更新表格。

-- Reset dtInserted and dtUpdated for action 1
UPDATE htmlComponent SET dtInserted = null, dtUpdated = null WHERE action=1;

-- INSERT or UPDATE according to the data inside the json structure
INSERT INTO htmlComponent (action, button_id, type, dtInserted)
VALUES
  (1, 1, 'button', NOW()),
  (1, 3, 'attribute', NOW())
ON DUPLICATE KEY UPDATE
  button_id = VALUES(button_id),
  type = VALUES(type),
  dtInserted = null,
  dtUpdated = NOW();

 -- Getting the result
SELECT * FROM htmlComponent where action=1;

您最终应该得到这个结果，这将使您可以轻松地找出哪些内容不再存在、哪些内容是新的以及哪些内容已更新。

+----+--------+-----------+-----------+----------------------------+----------------------------+
| ID | ACTION | BUTTON_ID | TYPE      | DTINSERTED                 | DTUPDATED                  |
+----+--------+-----------+-----------+----------------------------+----------------------------+
| 1  | 1      | 1         | button    | (null)                     | February, 09 2015 16:21:49 |
| 3  | 1      | 2         | attribute | (null)                     | (null)                     |
| 4  | 1      | 3         | attribute | February, 09 2015 16:21:49 | (null)                     |
+----+--------+-----------+-----------+----------------------------+----------------------------+

这是一个fiddle 。请注意，我必须将 UPDATE 和 INSERT 放在左侧面板中，因为查询面板中不允许 DML。