mysql - EAV - 如果行不存在则添加具有空值的行

Question

我有下表在 EAV 模型中存储数据:

+-------------+---------+------+-----+---------+-------+
| Field       | Type    | Null | Key | Default | Extra |
+-------------+---------+------+-----+---------+-------+
| user_id     | int(11) | NO   | MUL | NULL    |       |
| question_id | int(11) | NO   | MUL | NULL    |       |
| answer      | blob    | YES  |     | NULL    |       |
+-------------+---------+------+-----+---------+-------+

用一张表来保存不同类型的问题:

+----------+---------+------+-----+---------+----------------+
| Field    | Type    | Null | Key | Default | Extra          |
+----------+---------+------+-----+---------+----------------+
| id       | int(11) | NO   | PRI | NULL    | auto_increment |
| question | blob    | YES  |     | NULL    |                |
+----------+---------+------+-----+---------+----------------+

以及用户表:

+------------------+--------------+------+-----+---------+----------------+
| Field            | Type         | Null | Key | Default | Extra          |
+------------------+--------------+------+-----+---------+----------------+
| id               | int(11)      | NO   | PRI | NULL    | auto_increment |
| user_name        | varchar(128) | YES  |     | NULL    |                |
+------------------+--------------+------+-----+---------+----------------+

我该如何编写查询，为每个用户当前没有行的每个 question_id 插入一个 answer 为空值的行？

例如，如果我有 question_ids 1,2,3,4 并且我的表存储数据如下所示:

+--------------+---------------+--------+
| user_id      | question_id   | answer |
+--------------+---------+-----+--------+
| 1            | 1             | example|
| 1            | 3             | example|
| 1            | 4             | example|
+--------------+---------+-----+--------+

我想插入一行，如下所示:

+--------------+---------------+--------+
| user_id      | question_id   | answer |
+--------------+---------+-----+--------+
| 1            | 2             | NULL   |
+--------------+---------+-----+--------+

我试过这样的:

INSERT INTO profile_answers
(
    user_id,
    question_id,
    answer
)

SELECT
    id,
    profile_answers.question_id,
    null
FROM users
LEFT JOIN profile_answers ON profile_answers.user_id = users.id
WHERE NOT EXISTS (
    SELECT answer 
    FROM profile_answers 
    WHERE user_id = id
    AND question_id IN (
        SELECT GROUP_CONCAT(id SEPARATOR ',') FROM profile_questions
    )
)

但我最终插入了 question id 为 0 的行。

Answer 1

我已经考虑过这个问题，但我找不到比在用户和问题之间使用笛卡尔积和过滤子查询更好的方法了:

SELECT u.id, q.id
FROM users u,
questions q
WHERE NOT EXISTS(
  SELECT *
  FROM profile_answers a
  WHERE a.question_id = q.id AND a.user_id = u.id
);

Demo