如何在SQL中实现多个表的全局递增id？

Question

当我编码时，我决定为每种类型的内容使用不同的表格。现在我被困在解决这个问题上。基本上，我的通知系统当前按时间戳对最新内容进行排名。然而，这是不准确的，因为某人与另一个人同时提交内容的可能性很小，并且会出现不正确的排名。

现在，如果我将所有内容放在一个表中，我只需通过自动递增变量对其进行排名即可。有没有办法在多个表中实现这种自动递增整数（例如，当将某些内容插入table1，id=0时，将某些内容插入table2，id=1 ）。或者我是否必须将所有内容重新编码到一个表中。

注意：

我在多个表中包含内容的原因是因为它是有组织的并且可以减少负载压力。我不再关心组织了，因为我可以通过我编码的 GUI 访问数据，我只是想知道负载压力。

编辑：

我正在使用 PHP 5 和 MySQL。

Answer 1

您的问题，特别是跨多个表的 ID 的需求，清楚地表明您的数据库设计需要更改。您应该为所有内容类型创建一张表（作为概括），并具有自动递增 ID。然后，对于每个特定的内容类型，您可以定义具有额外字段的其他表（相当于 OOP 中的继承）以及指向基本表的外键。

换句话说，您需要类似于SQL 中的继承 。

Answer 2

您可以创建一个具有自动增量 ID 的表来跟踪 ID。您的程序将在该表上执行插入操作，获取 id，并根据需要使用它。

大致如下：

function getNextId() {
    $res = mysql_query("INSERT INTO seq_table(id) VALUES (NULL)");
    $id = mysql_insert_id();
    if ($id % 10 == 0) {
        mysql_query("DELETE FROM seq_table");
    }
    return $id;
}

其中 seq_table 是您必须创建的表，只是为了获取 id。使其成为一个函数，以便在需要时可以使用它。每生成 10 个 id，我就会删除所有生成的 id，无论如何，您在那里不需要它们。我不会每次都删除，因为它会减慢速度。如果同时发生另一次插入并且我删除了 11 条或更多记录，则不会影响此过程的行为。对于它必须达到的目的而言，它是安全的。

即使表为空，新的 id 也会继续增长，因为您已将 id 声明为自动增量。

更新：我想澄清为什么 ID 生成没有包含在事务中以及为什么不应该包含在事务中。

如果您生成自动 ID 并回滚事务，则下一个自动 ID 无论如何都会递增。摘自 MySQL 错误报告：

[...] 这不是一个错误，而是我们知道的每个 RDBMS 中都会发生的预期行为。生成的值不是事务的一部分，也不关心其他语句。

使用此过程获取 ID 是完全线程安全的。获取 ID 后的逻辑应该包装在事务中，尤其是在处理多个表时。

以这种方式获取序列并不是一个新概念，例如 metabase_mysql.php 是一个稳定的数据库访问库，有一个名为 GetSequenceNextValue() 的方法，它非常相似。

Answer 3

在单个表中，您可以有一个内容类型字段和包含内容类型字段的聚集索引。这有效地将所有一种内容类型保留在光盘上的一个位置，并将另一种内容类型保留在另一个位置，等等。（它实际上组织成页面，但这种物理组织仍然如此。）

假设每种内容类型具有相同的字段，这可能会满足您的需求，并且行为与多个表类似。在某些情况下，您甚至可能会发现，通过适当的索引，单表解决方案可以更快、更方便且可维护等。例如尝试跨所有内容类型创建全局唯一标识符。

如果您无法将它们合并回单个表，您可以创建一个中央链接表...

CREATE TABLE content_link (
  id            INT IDENTITY(1,1),         -- MS SQL SERVER syntax
  content_type  INT,
  content_id    INT                        -- The id from the real table
)

在插入内容表时，也插入链接表以创建您的全球唯一 ID。

更简单，但更手动，只需在数据库中的某个位置保存一个值即可。每当您需要新的 ID 时，请使用该集中存储的值并将其加一。请务必将增量和集合包装在单个事务中以停止竞争条件。 （这可以通过多种方式完成，具体取决于您的 SQL 风格。）

编辑

来自网络的几行 MySQL 示例代码...

START TRANSACTION;
INSERT INTO foo (auto,text)
    VALUES(NULL,'text');         # generate ID by inserting NULL
INSERT INTO foo2 (id,text)
    VALUES(LAST_INSERT_ID(),'text');  # use ID in second table
COMMIT TRANSACTION;

就个人而言，我实际上会将值存储在变量中，提交事务，然后继续我的业务逻辑。这将使表上的锁保持在最低限度。

Answer 4

您可以有一个单独的 ID 表，将其插入其中，然后使用新插入的 ID。

例如

CREATE TABLE ids (INT UNSIGNED AUTO INCREMENT PRIMARY KEY, timeadded DATETIME);

在脚本中：

<?php
$r = mysql_query('INSERT INTO ids (timeadded) VALUES (NOW())');
$id = mysql_insert_id();
mysql_query("INSERT INTO someOtherTable (id, data) VALUES ('$id', '$data)");

添加错误检查等以适应口味。

MySQL 手册指出：

生成的 ID 保存在服务器上
每个连接的基础。这意味着返回的值
给定客户端的函数是生成的第一个 AUTO_INCRMENT 值
对于影响 AUTO_INCREMENT 列的最新语句
客户。该值不会受到其他客户端的影响，即使他们
生成自己的 AUTO_INCRMENT 值。这种行为确保
每个客户端都可以检索自己的 ID，而无需关心
其他客户端的活动，并且不需要锁或
交易。

（来源）所以我不要认为对 ACID 合规性的担忧是一个问题。