mysql - 插入表 - 丢弃键可以为空的重复项

Question

我有一个程序，用户可以创建和编辑任务。每个任务可以分配 0-n 个问题。

当用户编辑任务时，他们会看到一个问题列表(之前创建的)，他们可以 (1) 删除或 (2) 编辑。他们还可以 (3) 将新问题添加到问题列表中。

每个问题都必须与每个分配的成员相关，以便可以跟踪他们的进度。这是在 task_data_table 中完成的。

task_data_table
============================================================================
| Field             | Type         | Null | Key | Default | Extra          |
============================================================================
| id                | int(11)      | no   | PRI |  Null   | auto_increment |
| completion_date   | timestamp    | yes  |     |         |                |
| answer            | varchar(255) | yes  |     |         |                |
| task_id           | int(11)      | yes  | MUL |         |                |
| question_id       | int(11)      | yes  | MUL |         |                |
| member_id         | int(11)      | yes  | MUL |         |                |
----------------------------------------------------------------------------

表格的内容可能如下所示:

=========================================================================
| id | completion_date     | answer | task_id | question_id | member_id |
=========================================================================
| 1  | 2016-05-13 11:00:00 | NULL   | 20      | NULL        | 2         |
| 2  | 2016-05-13 11:00:00 | yes    | 20      | 8           | 2         |
| 3  | 2016-05-13 11:00:00 | no     | 20      | 9           | 2         |
-------------------------------------------------------------------------

上面的第一个条目的 question_id 等于 NULL，因为它用于整个任务，而其他条目则用于特定问题。此外，每个问题对于一项任务来说都是唯一的。

<小时/>

我正在尝试通过减少提交更改时对数据库进行的循环量和不必要的检查来优化一些后端逻辑和查询时间。我尝试这样做的一种方法是给 task_id 与 question_id 组合一个 UNIQUE 键，这样我就不必检查如果在执行 INSERT INTO 之前，所述成员和问题的 task_data 已存在，则数据库。所以我这样做了:

ALTER TABLE task_data_table ADD UNIQUE unique_index (question_id, student_member_id);

但是，我刚刚从文档中发现:

a UNIQUE index permits multiple NULL values for columns that can contain NULL

我已经研究了解决此问题的方法，但只找到了使用 0 作为默认值而不是 NULL 的建议。不幸的是，这是不可能的，因为 task_id 和 question_id 都是外键。

我确实尝试了一些使用 IF NOT EXISTS 或 NOT IN 找到的查询，但它们不起作用。我该如何解决这个问题？

Answer 1

其他数据库对此有更好的机制。在 MySQL 中，最好的解决方案可能是向 questions 添加一个假行。

该行的 ID 应该为 0 或 -1。然后您可以使用外键约束和唯一约束来获得您想要的行为。

如果您依赖于列中的 NULL 值，则确实需要小心。解决这个问题的一种方法是使用 View :

create view v_task_data_table as
    select . . .,
           (case when question_id = -1 then NULL else question_id end) as question_id
    from task_data_table;

然后，任何需要 NULL 值的代码都可以通过使用 View 来获得它。