mysql - MySQL 中的记录自动排序

Question

我有一个 MySQL 表，其中包含属于公司和个人(客户)的电话号码。每个客户可以有多个电话号码，并且他们通常希望这些电话号码按特定顺序列出，因此该表有一个 sequence 字段，用于根据每个客户的偏好对他们的电话号码进行排序。这些字段是:

• parent_id - 字符
• 序列 - tinyint
• area_code - 字符串
• 数字 - 字符串

主键是 parent_id 和 sequence 的组合。

到目前为止一切顺利。当我想在顺序中向上移动一个数字或删除一个数字时，“问题”就出现了。

当向上移动号码时，我的做法(使用 PHP)是将记录的序列更改为 0(客户的第一个电话号码通常为 1，我为此使用 0)，然后将前一个数字的 sequence 加 1，然后将原始记录的 sequence 设置为比原来少 1。换句话说，将序列与前面的电话号码交换，但需要三个查询。

删除电话号码时，我会删除该记录，然后我必须重新排序该客户的剩余电话号码，以免序列丢失。这取决于客户有多少个电话号码，但可以是多个查询。

我想知道是否有更有效的方法。

提前致谢!

Answer 1

UPDATE 可以同时影响多条记录；例如:

1. 将序列 #4 移至 #2:

   UPDATE my_table SET sequence = CASE
     WHEN sequence = 4 THEN 2
     WHEN sequence < 4 AND sequence >= 2 THEN sequence + 1
   END WHERE parent_id = ? AND sequence BETWEEN 2 AND 4;
   

2. 删除序列 #3

   DELETE FROM my_table WHERE parent_id = ? AND sequence = 3;
   
   UPDATE my_table
   SET    sequence = sequence - 1
   WHERE  parent_id = ? AND sequence > 3;
   

但是，如果 sequence 可能会以这种方式发生更改，则在使用 (parent_id,equence) 作为 PK 时应非常谨慎。定义一个完全合成的 PK 可能会更好。