How to insert data to MySQL with auto-incremented column(field)?

sql

auto-increment

mysql

insertion

byAlex Kataev·Oct 5, 2024

Insert data into a MySQL table with an auto-increment primary key by excluding that field in your statement:

/* 
   SQL gods demand tribute,
   but they're happy as long as you don't try to mess with auto_increment
*/
INSERT INTO users (username, email) VALUES ('john_doe', '[email protected]');

This command auto-fills the id with the next sequential number, taking a burden off your shoulders and preventing the wrath of confused SQL gods.

Step-by-step approach to successful insertions

Point 1: Know your INSERT syntax

This is the basic syntax for inserting data into a table with an AUTO_INCREMENT column:

/* 
   Even SQL, like human, gets confused if we put things in the wrong order.
   So let's not, OK?
*/
INSERT INTO table_name (column1, column2) VALUES (value1, value2);

Point 2: Usage of DEFAULT keyword

In some scenarios, we can called DEFAULT to activate AUTO_INCREMENT. It's like asking SQL to "do its thing":

/* 
   DEFAULT: An SQL's genie that readily grants the wish for an auto sequence.
   Aladdin would be jealous!
*/
INSERT INTO table_name (auto_increment_column, column1) VALUES (DEFAULT, value1);

But check your MySQL docs before, they might have hidden some caveats.

Point 3: Following the columns' order

SQL is like a headmaster, it likes when we follow the order. Align values with table's column sequence to keep the databases running smoothly.

Peeking under the hood: AUTO_INCREMENT

The glory of AUTO_INCREMENT lies in MySQL's internal counter that takes an increment hike with each insertion for a smoother ride.

Common scenarios to avoid hiccups in SQL

Resetting AUTO_INCREMENT

Reset the AUTO_INCREMENT value with a straight face, knowing that it might switch up all of your plan but remember, with great power comes great responsibility:

/* 
   When in doubt, go back to the start.
   But remember: Uncle Ben told Peter, "With great power comes great responsibility." 
*/
ALTER TABLE table_name AUTO_INCREMENT = 1;

Creating gaps intentionally

Here's how you create intentional gaps for MySQL-ean hide and seek:

/* 
   Skipping numbers? SQL finds it fun!
   Remember, SQL likes fun but doesn't like inconsistency.
*/
INSERT INTO table_name (auto_increment_column, column1) VALUES (4, 'Explicit Value');

Concurrency

This is how SQL ruins plans of replication during high concurrency, by deploying a ninja called table-level locks.

Advanced usage scenarios

Now that we have poured over the basics and some common issues, it's time to take a look at some advanced usage scenarios.

Multi-row insertion

If you ever wondered how to quickly insert multiple rows, here you go. Just remember to thank later:

/* 
   An SQL lifehack: Insert multiple rows at once and coffee breaks will be longer.
   Just between us, SQL loves coffee breaks.
*/
INSERT INTO table_name (column1, column2) VALUES
    (value1a, value2a),
    (value1b, value2b),
    ...

Combining AUTO_INCREMENT with static values

To combine auto-increment values with static keywords, use good ol' concatenation:

/*
   Can SQL combine static and dynamic values? 
   Of course! SQL is like a teenager;
   It can multitask better than you'd expect.
*/

INSERT INTO table_name (combined_value, column2) VALUES 
    (CONCAT("VAL_", AUTO_INCREMENT), value2);

Utilizing the power of LAST_INSERT_ID()

Using LAST_INSERT_ID() right after an insertion lets you hold the flag of victory before anyone else could. It's like taking the first selfie with your brand new hairdo before anyone else sees it!

INSERT INTO table_name (column1) VALUES ("First name");
SET @last_id_in_table1 = LAST_INSERT_ID();

/*
   Now, I'll know the ID of the newest member faster than 
   a child opening his Christmas gift!
*/