Generate int primary key manually in mysql

What is the best way to generate a manually int primary key with mysql?

I dont want to use the autoincrement feature.

I taught to create a table with one column of type int name开发者_如何学JAVAd sequence. So every time I'm going to insert a new record first check this column and use the value as the primary key. Then after the insert is successful increment the value of sequence.

Is this a good aproach?

Create a table with one int column of type InnoDB:

CREATE TABLE key_table ( next_value INT NOT NULL DEFAULT 0 ) ENGINE=InnoDB;

Then when you want to get a new value for the key you must start a transaction (using the API you are using) and then:

SELECT next_value FROM key_table FOR UPDATE

Then read the value returned, it is the value for your primary key. Then to increment the value while still in the same transaction execute:

UPDATE key_table SET next_value = next_value + 1;

Then commit your transaction.

This will ensure complete consistency if and only if you use transaction as described above and you use the InnoDB table type.

This approach works but you need to have a transaction which wraps these distinct atomic operations (looking up, inserting and incrementing)

However, could you elaborate on why autoincrement column is not good enough? It is safe and guaranteed database feature, so unless you have compelling reasons, I would stick with it.

I think that's very reasonable, as long as the value of the next sequence from the given table is delivered via a function or procedure (that itself updates the sequence table) to ensure uniqueness, and not via

select max(id) + 1 from ...