mysql - 如果重复，则插入唯一复合键的一列的增量值

Question

我正在将数据从数据库传输到另一个数据库。原始表具有以下结构(其中关键联系人为 bool 值):

+------------+-----------+-------------+
| contact_id | school_id | key_contact |
+------------+-----------+-------------+
| 1          | 1         | 1           |
| 2          | 2         | 0           |
| 3          | 2         | 1           |
| 4          | 2         | 1           |
| 5          | 3         | 0           |
+------------+-----------+-------------+

我想将其变成以下形状，其中 display_order 是带有 school_id 的复合唯一索引:

+------------+-----------+---------------+
| contact_id | school_id | display_order |
+------------+-----------+---------------+
| 1          | 1         | 1             |
| 2          | 2         | 3             |
| 3          | 2         | 1             |
| 4          | 2         | 2             |
| 5          | 3         | 1             |
+------------+-----------+---------------+

逻辑是，如果 key_contact = 1 则 display_order 应为 1，除非 contact_id 具有相同的 school_id 已经有一个值为 1 的 display_order，那么它应该插入下一个增量可用的值，该值对于 school_id 是唯一的。然后，我想插入 key_contact = 0 的所有联系人，并分配对 school_id 唯一的下一个可用 key_contact 整数。

我为两列创建了唯一索引:

UNIQUE KEY `contact_school_display_order_school_id_unique` (`display_order`,`school_id`)

我尝试过使用 INSERT... ON DUPLICATE KEY UPDATE 但我真的想要一些东西，可以在重复键上将插入值的值增加到唯一的下一个整数school_id。

任何指示将不胜感激。

Answer 1

从表 contacts1 迁移到 contacts2

insert into contacts2
    select 
        c1.contact_id,
        c1.school_id,
        count(c2.contact_id) as display_order
    from contacts1 c1
    join contacts1 c2
        on  c2.school_id = c1.school_id
        and (
            c2.key_contact > c1.key_contact
            or
            c2.key_contact = c1.key_contact and c2.contact_id <= c1.contact_id
        )
    group by c1.contact_id, c1.school_id
    order by c1.contact_id;

sqlfiddle demo

说明

关键逻辑在连接条件:

            c2.key_contact > c1.key_contact
            or
            c2.key_contact = c1.key_contact and c2.contact_id <= c1.contact_id

此条件定义与 c1 中的行相比，c2 中的行对于给定学校具有更高或相同的联系优先级。 (仅当 contact_id 相同时，优先级才相等。)在 SELECT 部分，我们计算 c1 中每个 contact_id 具有更高或相同优先级的所有行(按 c1.contact_id 分组)。

            c2.key_contact > c1.key_contact

仅当 c1.key_contact=0 和 c2.key_contact=1 时才为真。因此，通过此条件找到的 c2 中的每一行都具有更高的优先级，并且将被计数。

            c2.key_contact = c1.key_contact and c2.contact_id <= c1.contact_id

如果 key_contact 相等，那么我们只计算 contact_id 较小或相等的行。

这会产生与按如下方式对表格进行排序相同的结果:

select *
from contacts1 c1
order by c1.school_id, c1.key_contact desc, c1.contact_id

然后枚举每所学校的行。