CODEWITHSUNDEEP
mysqldatabaseauto-increment
anon271334
anon271334

Reputation:

How does MySQL Auto Increment work?

I was just creating a new table using MySQL Query Browser, and noticed there's a tick under Auto Increment Column. How does that work?

When adding to the database programatically, do I just add a number, and then the database automatically increments that number?

Everytime a NEW user registers on my site, I want their Customer ID (integer only) to auto increment, so I don't have to try and randomly generate a unique number.

Can this be done simply?

Thank you!

Upvotes: 14

Views: 21818

Answers (5)

trickwallett
trickwallett

Reputation: 2468

Yes. Auto_Increment columns work like they say on the tin. Tips

  • when INSERT - ing, use NULL or omit the column

  • Use LAST_INSERT_ID() (or API equivalents) to obtain the last generated value.

  • for security and business logic reasons, it's usually better form to not directly use a key value for a customer identifier. Consider using Hashed / randomised surrogate customer keys instead.

Ta

Upvotes: 2

Thilak
Thilak

Reputation: 590

1 more, You can insert your own value also (ie your random value).

Upvotes: 5

user525192
user525192

Reputation:

When you enable Auto Increment an ID will always get automatically added whenever a new record is made.. Example:

If you have 1 record with ID 1 in your table and you add a new record, the ID will automatically be 2.

Upvotes: 1

BoltClock
BoltClock

Reputation: 724452

Yes, that's the exact purpose of AUTO_INCREMENT. It looks at whatever is the current increment value for that table, and stores that value plus 1 for the new row that comes in, automatically. You can omit that field from your INSERT statements and MySQL will handle it for you for every new row that comes in, giving each row its own unique ID.

Upvotes: 1

Pekka
Pekka

Reputation: 449783

When adding to the database programatically, do I just add a number, and then the database automatically increments that number?

Yes, that's the way auto_increment works.

  • The value will be incremented for each new row

  • The value is unique, duplicates are not possible

  • If a row is deleted, the auto_increment column of that row will not be re-assigned.

  • The auto_increment value of the last inserted row can be accessed using the mySQL function LAST_INSERT_ID() but it must be called right after the insert query, in the same database connection

mySQL Reference

Upvotes: 17

Related Questions