如何在SQL中为多个表实现全局递增ID？

You can create a table with auto increment id just to keep track of ids. Your program would do an insert on that table, get the id, use it as necessary.

Something along the lines of:

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;
}

Where seq_table is a table that you've to create just to get the ids. Make it a function so it can be used whenever you need. Every 10 ids generated I delete all generated ids, anyway you don't need them there. I don't delete every time since it would slow down. If another insert happen in the meantime and I delete 11 or more records, it doesn't affect the behaviour of this procedure. It's safe for the purpose it has to reach.

Even if the table is empty new ids will just keep on growing since you've declared id as auto-increment.

UPDATE: I want to clarify why the ID generation is not wrapped in a transaction and why it shouldn't.

If you generate an auto id and you rollback the transaction, the next auto id, will be incremented anyway. Excerpt from a MySQL bug report:

[...] this is not a bug but expected behavior that happens in every RDBMS we know. Generated values are not a part of transaction and they don't care about other statements.

Getting the ID with this procedure is perfectly thread safe. Your logic after the ID is obtained should be wrapped in a transaction, especially if you deal with multiple tables.

Getting a sequence in this way isn't a new concept, for instance, the code of metabase_mysql.php which is a stable DB access library has a method called GetSequenceNextValue() which is quite similar.

In a single table, you could have a field for the content type and clustered index that includes the content type field. This effectively keeps all of one content type in one place on the disc, and another content type in another place, etc. (It's actually organised into pages, but this physical organisation is still true.)

Assuming that each content type has the same fields, this would likely meet your needs and behave similarly to multiple tables. In some cases you may even find that, with appropriate indexes, a single table solution can be faster, more convenient and maintainable, etc. Such as trying to create global unique identifiers across all content types.

If you're unable to merge these back into a single table, you could create a central link table...

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
)

As you insert into the content tables, also insert into the link table to create your globally unique id.

More simply, but even more manually, just hold a single value somewhere in the database. Whenever you need a new id, use that centrally stored value and increment it by one. Be sure to wrap the increment and collection in a single transaction to stop race conditions. (This can be done in a number of ways, depending on your flavor of SQL.)

EDIT

A couple of MySQL example lines of code from the web...

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;

Personally, I'd actually store the value in a variable, commit the transaction, and then continue with my business logic. This would keep the locks on the tables to a minimum.

You could have a separate ID table, insert into that, and use the newly-inserted ID.

e.g.

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

In the script:

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

Add error checking etc. to taste.

The MySQL manual states:

The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client. This value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own. This behavior ensures that each client can retrieve its own ID without concern for the activity of other clients, and without the need for locks or transactions.

(Source) So I don't think concerns about ACID complians are a problem.

Your question, particularly the need for ID spanning over multiple tables, is clearly signalizing that your database design needs change. You should make one table for all content types (as a generalization), with autoincrementing ID. Then, for each particular content type, you can define other table (equivalent of inheritance in OOP) with extra fields, and foreign key pointing to the basic table.

In other words, you need something like inheritance in SQL.