AUTO_INCREMENT generates integer primary keys on INSERT—pair with LAST_INSERT_ID() in the same session.
Define and use
CREATE TABLE items (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL
);
INSERT INTO items (name) VALUES ('Cable');
SELECT LAST_INSERT_ID();Practice: Run on database practice in mysql client.
Composite keys
Only one AUTO_INCREMENT column per table; it must be indexed (usually PRIMARY KEY).
UUID alternative
Apps may use CHAR(36) UUID strings to avoid predictable ids—trade-off: larger indexes.
Important interview questions and answers
- Q: LAST_INSERT_ID scope?
A: Per connection session—safe in PHP PDO within same request. - Q: Gap in sequence?
A: Rollbacks and crashes can leave gaps—ids need not be contiguous.
Self-check
- How read id after INSERT?
- Are AUTO_INCREMENT values always consecutive?
Tip: Gaps in AUTO_INCREMENT sequence are normal after rollback.
Interview prep
- LAST_INSERT_ID?
Per-connection id from latest AUTO_INCREMENT insert.
- Gaps?
Normal after failed inserts or rollbacks.