ansaurus

Question

Generate int primary key manually in mysql

Answer 1

A:

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 ...

davek 2009-10-31 19:13:52

Answer 2

+2 A:

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.

Learning 2009-10-31 19:15:14

Hello.I don't want to use autoincrement because i think it is not safe enough because after inserting I want to get the current id. For example:Insert into TABLENAME(id) Values(null);SELECT last_insert_id() as lastId;If I use this aproach even if it is on a transaction I dont know what could happen if after the first insert there is another insert with autoincrement but in another table. Then I execute SELECT last_insert_id() as lastId; which will be the value of lastId? the last id from the first table I inserted or the second one?

Enrique 2009-10-31 19:25:06

I think you need to read http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id

Alex JL 2009-10-31 19:30:45

Agree with Code Duck.You're worried that LAST_INSERT_ID() will be affected by other insert operations. However, if you're performing a simple INSERT INTO TABLE; type command, it doesn't matter if other inserts are happening on other connections, as LAST_INSERT_ID() is connection-specific.> The ID that was generated is maintained in the server on a per-connection basis.So as long as you're doing simple inserts, just use LAST_INSERT_ID(). An exception would be if you were using stored procedures or some other feature that would cause secondary inserts, but you're probably not.

Rocketmonkeys 2009-10-31 19:40:01

Thank you very much. I didnt know last_insert_id() was specific to one connection.

Enrique 2009-10-31 19:49:01

Answer 3

+3 A:

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.

Ramon 2009-10-31 19:22:01

+1 just what I meant, but far better expressed!

davek 2009-10-31 19:25:42

As an aside, you can get much better performance if you fetch new keys in bulk. Adapt the above queries to increment by, say, 1000 instead of by 1 each time, then you know that no-one else can ever have the 1000 key values following `next_value`. You then keep a counter in memory and increment it by 1 each time you use a key. When it gets to the original value + 1000 you must fetch another 1000 keys from the database. This is the way that e.g. Hibernate's serial key generator works.

Ramon 2009-10-31 19:35:03

ansaurus

tags:

views:

answers:

Generate int primary key manually in mysql

related questions