👉What is AUTO_INCREMENT in MySQL?
MySQL AUTO_INCREMENT: AUTO_INCREMENT is a MySQL feature used to automatically generate sequential numeric values when inserting new rows into a table. It is commonly used for primary keys to ensure uniqueness without manual input.
Why Use AUTO_INCREMENT?
- Eliminates the need to manually generate unique IDs.
- Ensures primary key values are always unique.
- Simplifies database design by handling ID generation automatically.
- Works only with numeric data types (INT, BIGINT, etc.).
👉Syntax: How to Use AUTO_INCREMENT in MySQL
MySQL AUTO_INCREMENT: To define an AUTO_INCREMENT column, use the following syntax:
sql
CREATE TABLE categories (
category_id INT AUTO_INCREMENT,
category_name VARCHAR(150),
remarks VARCHAR(500),
PRIMARY KEY (category_id)
);
🔹 Here, category_id will automatically get a unique number whenever a new record is inserted.
Default Behavior of AUTO_INCREMENT
- The first value starts at 1 by default.
- Each new row increments the value by 1.
👉Example: Inserting Data with AUTO_INCREMENT
MySQL AUTO_INCREMENT: When inserting a new record, you don’t need to specify the primary key:
sql
INSERT INTO categories (category_name) VALUES (‘Cartoons’);
🔹 MySQL automatically generates a unique category_id for the new record.
Viewing the Inserted Data
sql
SELECT * FROM categories;
| category_id | category_name | remarks |
| 1 | Comedy | Movies with humor |
| 2 | Romantic | Love stories |
| 3 | Epic | Ancient stories |
| 4 | Horror | NULL |
| 5 | Science Fiction | NULL |
| 6 | Thriller | NULL |
| 7 | Action | NULL |
| 8 | Romantic Comedy | NULL |
| 9 | Cartoons | NULL |
👉How to Get the Last Inserted AUTO_INCREMENT Value
MySQL AUTO_INCREMENT: To retrieve the last inserted AUTO_INCREMENT value, use:
sql
SELECT LAST_INSERT_ID();
🔹 This returns the last AUTO_INCREMENT ID generated in the current session.
👉Changing AUTO_INCREMENT Starting Value
By default, MySQL starts at 1, but you can change it using:
sql
ALTER TABLE categories AUTO_INCREMENT = 100;
🔹 Now, new records will start from 100 instead of 1.
👉Key Points About AUTO_INCREMENT
✅ AUTO_INCREMENT is used for generating unique numeric IDs automatically.
✅ Works only with INT, BIGINT, and other numeric data types.
✅ Does not reuse deleted IDs; the sequence keeps increasing.
✅ The starting value can be changed using AUTO_INCREMENT = X.
✅ It is recommended to use the UNSIGNED attribute for positive-only values.
By using AUTO_INCREMENT, you can simplify database management and ensure that primary keys remain unique without additional logic.
