使用单个SQL查询测试列的顺序性

Question

我有一个表，其中包含一组顺序数据集，如下所示：

ID set_ID some_column n

1 'set-1' 'aaaaaaaaaa' 1

2 'set-1' 'bbbbbbbbbb' 2

3 'set-1' 'cccccccccc' 3

4 'set-2' 'dddddddddd' 1

5 'set-2' 'eeeeeeeeee' 2

6 'set-3' 'ffffffffff' 2

7 'set-3' 'gggggggggg' 1

在对这些行进行几种类型的修改的事务结束时，我希望确保在单个集合中，"n“的所有值仍然是连续的(否则是回滚)。根据PK，它们不需要是相同的顺序，只是顺序，像1-2-3或3-1-2，但不像1-3-4或1-2-3-3-4。

由于在单个集合中可能有数千行的事实，我倾向于在数据库中这样做，以避免在做了一些小的改变之后仅仅为了验证而获取数据的开销。

另外还有并发性的问题。在InnoDB (可重复读取)中锁定的工作方式(据我所知)是，如果我在"n“上有一个索引，那么InnoDB也会锁定值之间的”间隙“。如果我将set_ID和n组合到一个索引中，会不会消除出现幻象行的问题？

在我看来这是个常见的问题。有什么好主意吗？

谢谢!

注意:使用MySQL + InnoDB

Answer 1

查找包含max - min + 1 > count的序列

IF EXISTS (SELECT set_ID
             FROM mytable
            GROUP BY set_ID
           HAVING MAX(n) - MIN(n) + 1 > COUNT(n)
          )
    ROLLBACK

如果序列必须从1开始，请执行以下操作：

IF EXISTS (SELECT set_ID
             FROM mytable
            GROUP BY set_ID
           HAVING MIN(n) = 1 AND MAX(n) > COUNT(n)
          )
    ROLLBACK

您还需要避免重复的序列号。但这可以通过在set_ID和n上创建一个唯一的密钥来实现。

Answer 2

试试这个：

IF EXISTS (SELECT set_ID
               FROM mytable
               GROUP BY set_ID
               HAVING MIN(n) = 1 AND MAX(n) <> COUNT(DISTINCT n)
          )
    ROLLBACK

在SQL Server上工作(我没有MySql来试用)：

DECLARE @YourTable table (ID int, set_ID char(5), some_column char(10),n int)
INSERT @YourTable VALUES (1, 'set-1' ,'aaaaaaaaaa' ,1)
INSERT @YourTable VALUES (2, 'set-1' ,'bbbbbbbbbb' ,2)
INSERT @YourTable VALUES (3, 'set-1' ,'cccccccccc' ,3)
INSERT @YourTable VALUES (4, 'set-2' ,'dddddddddd' ,1)
INSERT @YourTable VALUES (5, 'set-2' ,'eeeeeeeeee' ,2)
INSERT @YourTable VALUES (6, 'set-3' ,'ffffffffff' ,2)
INSERT @YourTable VALUES (7, 'set-3' ,'gggggggggg' ,1)
INSERT @YourTable VALUES (8, 'set-3' ,'ffffffffff' ,4)
INSERT @YourTable VALUES (9, 'set-3' ,'ffffffffff' ,4)

--this will list all "bad" sets
SELECT set_ID
    FROM @YourTable
    GROUP BY set_ID
    HAVING MIN(n) = 1 AND MAX(n) <> COUNT(DISTINCT n)

输出：

set_ID
------
set-3