CODEWITHSUNDEEP
mysql
Adrian Tanase
Adrian Tanase

Reputation: 700

Adding new row ID in table

I have 3 options of adding a new UNIQUE id (primary_key) to one of my particular tables.

  1. Create AUTO_INCREMENT field.
  2. SELECT id FROM table ORDER BY id DESC LIMIT 1
  3. SELECT MAX(id) FROM table

I'm currently using option 2, as people might delete rows from the table they access, so id's get freed, otherwise i'd be using option 1

What do you reccommend ?

Upvotes: 1

Views: 107

Answers (3)

srini
srini

Reputation: 383

Create a DB Sequence and use it instead, that will give you much more convinence even in multiple threads access scenarios and distributed environments and clustered env as well.

Upvotes: 1

eggyal
eggyal

Reputation: 125865

You should always use option 1 (in order to avoid the race hazard of two simultaneous inserting clients reading the current maximum id before either has completed their insertion). AUTO_INCREMENT prevents this hazard from occurring through the use of locks that make the read & write operations atomic.

Upvotes: 3

Joe G Joseph
Joe G Joseph

Reputation: 24046

Option 1

AUTO_INCREMENT

which is The easiest way to implement and maintain

Upvotes: 1

Related Questions