如何跟踪辅助索引id

Question

有一个由多个用户共享的表。基本表格结构如下：

unique_id | user_id  | users_index_id | data_1 | data_2 etc etc

在id字段类型为int和unique_id为主键的情况下，自动递增。

这些数据将类似于：

unique_id | user_id | users_index_id
1 | 234 | 1
2 | 234 | 2
3 | 234 | 3
4 | 234 | 4
5 | 732 | 1
6 | 732 | 2
7 | 234 | 5
8 | 732 | 3

如何跟踪“users_index_id”，以使其“自动增量”专门用于user_id？

任何帮助都将不胜感激。因为我已经在寻找答案，但我不确定我是否使用了正确的术语来找到我需要的东西。

Answer 1

唯一一致地做到这一点的方法是使用“前插入”和“更新前”触发器。MySQL不直接支持这种语法。您可以在存储过程中包装对表的所有更改，并将逻辑放在其中，或者在执行insert时使用非常小心的逻辑。

insert into table(user_id, users_index_id)
    select user_id, count(*) + 1
    from table
    where user_id = param_user_id;

但是，如果您执行delete或某些更新，这将不会使事情有序。

在查询时，您可能会发现计算users_index_id比在数据库中更方便。您可以使用子查询(可能对表上的正确索引没有问题)或使用变量(这可能更快，但不能放到视图中)来完成这一任务。

如果您在table(user_id, unique_id)上有一个索引，那么下面的查询应该运行得很好：

select t.*,
       (select count(*) from table t2 where t2.user_id = t.user_id and t2.unique_id <= t.unique_id
       ) as users_index_id
from table t;

您将需要非深海性能的索引。