How to set initial value and auto increment in MySQL?

How to Set Initial Value and Auto Increment in MySQL?

So, you want to set an initial value for an "id" column in a MySQL table that starts from 1001? And you also want to insert data into the table without specifying the initial value for the id column? Well, you've come to the right place! In this blog post, we'll delve into the depths of MySQL and provide you with easy solutions to this common problem.

The Scenario

Let's say you have a table called "users" with columns such as "id", "name", and "email". You want the "id" column to automatically increment its value but start from 1001 instead of the default 1. Additionally, you want to insert data into the table without having to explicitly mention the initial value for the "id" column.

🚀 The Solution

To achieve this, we'll use the power of MySQL's AUTO_INCREMENT attribute and a simple SQL statement. Here's a step-by-step guide to set the initial value and auto increment the "id" column in MySQL:

Step 1: Create the Table

First, let's create the "users" table with the desired columns. Use the following SQL statement:

CREATE TABLE users (
  id INT AUTO_INCREMENT,
  name VARCHAR(255),
  email VARCHAR(255),
  PRIMARY KEY (id)
) AUTO_INCREMENT = 1001;

Here, we're defining the "id" column as AUTO_INCREMENT and setting its initial value to 1001.

Step 2: Insert Data

To insert data into the "users" table without specifying the value for the "id" column, exclude it from the column list in your INSERT statement. Here's an example:

INSERT INTO users (name, email) VALUES ('John Doe', 'johndoe@example.com');

MySQL will automatically assign the next auto-incremented value (1001) to the "id" column for this record.

Step 3: View the Results

To verify that the initial value and auto increment are correctly set, run a SELECT query on the "users" table:

SELECT * FROM users;

You should see the inserted record with the "id" column value as 1001.

📣 Call to Action

And there you have it! Setting the initial value and auto increment in MySQL is no longer a mystery. So go ahead and give it a try in your own database.

If you found this blog post helpful or have any questions/suggestions, we'd love to hear from you in the comments section below. Share this post with your friends who might find it useful too. Happy coding! 😄