两列主键，根据第二列的值自动增加列

Question

我在为火鸟编写sql时遇到了问题。我想达到的目标:

table_id   database_id     other_columns
1          1
2          1
3          1
1          2
2          2

其中table_id为自动递增部分，database_id为第二部分。

基本上像这个MySQL解决方案，但使用Firebird:Mysql双列主键自动递增

如何创建表以及如何插入表?

Answer 1

这在Firebird中并不像在MySQL中那么简单。如果事先知道database_id的数量，您可以为每个id分配一个序列，并在触发器中使用它，但是对于大量的id，这很快就会变得笨拙。

我的回答的其余部分假设使用Firebird 2.5(我已经用Firebird 2.5.2 Update 1进行了测试)。

如果我们只有database_id s 1和2，我们可以创建两个序列:

CREATE SEQUENCE multisequence_1;
CREATE SEQUENCE multisequence_1;

当使用没有序列的id时，需要一个异常:

CREATE OR ALTER EXCEPTION no_sequence 'No corresponding sequence found';

然后可以使用以下触发器:

CREATE OR ALTER TRIGGER multisequence_BI FOR multisequence
   ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
   IF (NEW.database_id = 1) THEN
       NEW.table_id = NEXT VALUE FOR multisequence_1;
   ELSE IF (NEW.database_id = 2) THEN
       NEW.table_id = NEXT VALUE FOR multisequence_2;
   ELSE 
       EXCEPTION no_sequence;
END

如您所见，这将很快导致大量IF/ELSE语句。这可以通过使用EXECUTE STATEMENT和为下一个序列值动态生成的查询来简化。如果不能提前控制database_id值(及其序列)的数量，这将无法工作。

您可以尝试使用如下所示的动态查询来解决这个问题。这可能有它自己的问题(特别是如果有大量的插入)，因为EXECUTE STATEMENT有一些开销，它也可能导致问题，因为使用动态DDL(例如元数据表上的锁/更新冲突)。

CREATE OR ALTER TRIGGER multisequence_BI FOR multisequence
   ACTIVE BEFORE INSERT POSITION 0
AS
   DECLARE new_id INTEGER;
   DECLARE get_sequence VARCHAR(255);
BEGIN
    get_sequence = 'SELECT NEXT VALUE FOR multisequence_' || NEW.database_id || 
         ' FROM RDB$DATABASE';
    BEGIN
        EXECUTE STATEMENT get_sequence INTO :new_id;
        WHEN SQLCODE -104 DO
        BEGIN
            EXECUTE STATEMENT 
                'CREATE SEQUENCE multisequence_' || NEW.database_id 
                WITH AUTONOMOUS TRANSACTION;
            EXECUTE STATEMENT get_sequence INTO :new_id;
        END
    END
    NEW.table_id = new_id;
END

这段代码仍然容易受到多个事务试图创建相同序列的影响。在(试图)创建序列的语句之后添加WHEN ANY DO，可能允许您使用该序列，但它也可能导致诸如锁冲突之类的虚假错误。还要注意，不鼓励在EXECUTE STATEMENT中使用DDL(请参阅文档中的警告)。

在生产环境中使用此解决方案之前，我强烈建议在负载下彻底测试此解决方案!

请注意，WITH AUTONOMOUS TRANSACTION子句在技术上不是创建序列所必需的，但是需要它来确保序列对其他事务也是可见的(并且在回滚原始事务时不会被删除)。

还要注意单个Firebird数据库中序列(或:生成器)的最大数量:+/- 32758，参见Firebird生成器指南:一个数据库中有多少个生成器可用

Answer 2

"table_id"字段实际上是一个简单的基于记录插入顺序的行编号，并由"database_id"分区。第一个"database_id"foo得到一个"table_id"为1，第二个foo为2，第一个栏为1，第二个栏为2，等等。

如果你知道每个"database_id"的行插入顺序，这可以动态计算。传统的自动增量列应用于所有行，可以提供这种排序。这样计算就可以隐藏在VIEW后面，你的应用程序就不会知道了。

分区行号很容易用SQL窗口函数表示，如果我没记错的话，它在Firebird 3中得到支持:

,,,,SELECT ROW_NUMBER() OVER (PARTITION BY "database_id" ORDER BY auto-increment-column ) AS "table_id"

对于Firebird 2，您可以自己计算，对于每个不同的"database_id"，它前面有多少行:

   SELECT COUNT(b.auto_inc_id) + 1 AS table_id,
          a.database_id
     FROM tbl a
LEFT JOIN tbl b
          ON a.database_id = b.database_id AND b.auto_inc_id < a.auto_inc_id
 GROUP BY 2