What is AUTO_INCREMENT in MySQL?
MySQL AUTO_INCREMENT: AUTO_INCREMENT is a feature in MySQL that automatically generates unique sequential numbers for a column, usually a primary key.
🔹 Why Use AUTO_INCREMENT?
✔ Ensures each row has a unique identifier (Primary Key).
✔ Simplifies inserting new records without manually specifying an ID.
✔ Eliminates the need for complex logic to check existing IDs.
🔹 Best Practices for AUTO_INCREMENT
✅ Use AUTO_INCREMENT with the INT data type.
✅ Use UNSIGNED to allow only positive numbers.
✅ Choose a sufficiently large data type to handle future records.
How to Use AUTO_INCREMENT in MySQL
🔹 Basic Syntax:
sql
CREATE TABLE categories (
category_id INT AUTO_INCREMENT,
category_name VARCHAR(150) DEFAULT NULL,
remarks VARCHAR(500) DEFAULT NULL,
PRIMARY KEY (category_id)
);
Explanation:
- category_id is an AUTO_INCREMENT field.
- MySQL automatically assigns an increasing value each time a row is inserted.
- No need to specify category_id manually during insert operations.
Inserting Data into an AUTO_INCREMENT Table
sql
INSERT INTO categories (category_name) VALUES (‘Cartoons’);
MySQL automatically generates a unique category_id for this new row.
Example Table Data After Inserts
| category_id | category_name | remarks |
| 1 | Comedy | Movies with humor |
| 2 | Romantic | Love stories |
| 3 | Epic | Ancient story movies |
| 4 | Horror | NULL |
| 5 | Science Fiction | NULL |
| 6 | Thriller | NULL |
| 7 | Action | NULL |
| 8 | Romantic Comedy | NULL |
| 9 | Cartoons | NULL |
Retrieving the Last Inserted ID
MySQL AUTO_INCREMENT: You can retrieve the most recent AUTO_INCREMENT value using:
sql
SELECT LAST_INSERT_ID();
This returns the category_id of the last inserted row.
Customizing AUTO_INCREMENT Values
🔹 Starting AUTO_INCREMENT from a Different Number
By default, AUTO_INCREMENT starts at 1. To change this:
sql
ALTER TABLE categories AUTO_INCREMENT = 100;
Now, the next record will have category_id = 100.
What Happens When a Row is Deleted?
- MySQL AUTO_INCREMENT: If a row is deleted, its AUTO_INCREMENT ID is not reused.
- MySQL continues incrementing from the highest existing value.
Summary
✔MySQL AUTO_INCREMENT: MySQL AUTO_INCREMENT: AUTO_INCREMENT generates unique numbers for primary keys.
✔ It ensures uniqueness and simplifies database inserts.
✔ Always use UNSIGNED INT for AUTO_INCREMENT fields.
✔ Use LAST_INSERT_ID() to retrieve the most recently inserted ID.
✔ Deleted IDs are not reused, and numbering continues sequentially.
✔ You can modify the starting value using AUTO_INCREMENT = X.
Next Steps: Try creating your own MySQL table with AUTO_INCREMENT and practice inserting records!
