php - 如何跟踪二级索引 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;

但是，如果您执行删除 或一些更新，这将无法保持秩序。

您可能会发现在查询时计算 users_index_id 比在数据库中计算更方便。您可以使用子查询(对于表上的正确索引可能没问题)或使用变量(可能更快但不能放入 View 中)来执行此操作。

如果您在 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;

您将需要非糟糕性能的索引。