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

Question

I decided back when I was coding to have different tables for each type of content. Now I am stuck solving this. Basically my notification system ranks the newest content by its timestamp currently. This is inaccurate however because there is a small chance that someone would submit content at the same time as another person, and incorrect ranking would occur.

Now if I had all my content in a single table, I would simply rank it by an auto-incrementing variable. Is there a way to implement this auto-increment integer across multiple tables (e.g. When something is inserted into table1, id=0, something is inserted into table2, id=1). Or do I have to recode all my stuff into a single table.

NOTE:

The reason I have content in multiple tables is because its organized and it would reduce load stress. I don't really care about the organization anymore, because I can just access the data through a GUI I coded, I'm just wondering about the load stress.

EDIT:

I'm using PHP 5 with MySQL.

Answer 1

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.