MySQL - Using Sequences

MySQL - Generating Unique IDs with AUTO_INCREMENT

In database design, nearly every table needs a primary key—a column with a unique value for each row that serves as its identifier. A common way to generate these unique keys is by using a sequence of numbers (e.g., 1, 2, 3, …).

Simulating Sequences with AUTO_INCREMENT

While some database systems have a dedicated SEQUENCE object, MySQL achieves this functionality through the AUTO_INCREMENT attribute. When you apply this attribute to an integer column, MySQL automatically generates a new, unique integer for that column every time a new row is inserted.

By default, an AUTO_INCREMENT sequence starts at 1 and increments by 1 for each new record. This column must be defined as a key (usually the PRIMARY KEY).

Syntax

CREATE TABLE table_name (
    id_column INT AUTO_INCREMENT PRIMARY KEY,
    column2 DATATYPE,
    ...
);

Example: Creating a Table with an Auto-Incrementing ID

Let’s create an employees table where the ID is automatically generated.

CREATE TABLE employees (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    NAME VARCHAR(50) NOT NULL,
    DEPARTMENT VARCHAR(50) NOT NULL,
    SALARY DECIMAL(10, 2)
);

When inserting records, you can omit the ID column or specify its value as NULL or 0. MySQL will replace it with the next value in the sequence.

-- MySQL will generate IDs automatically
INSERT INTO employees (NAME, DEPARTMENT, SALARY) VALUES
    ('Alice', 'Engineering', 75000.00),
    ('Bob', 'Marketing', 62000.00),
    ('Charlie', 'Engineering', 81000.00);

Output

Querying the table shows that the ID values were generated sequentially.

SELECT * FROM employees;

ID	NAME	DEPARTMENT	SALARY
1	Alice	Engineering	75000.00
2	Bob	Marketing	62000.00
3	Charlie	Engineering	81000.00

Retrieving the Last Inserted ID

It’s often necessary to know the ID of the record you just inserted, for instance, to create a related record in another table. You can retrieve this value using the LAST_INSERT_ID() function.

SQL Example

This function must be called immediately after your INSERT statement within the same database connection.

INSERT INTO employees (NAME, DEPARTMENT, SALARY) VALUES ('David', 'Sales', 68000.00);
SELECT LAST_INSERT_ID(); -- Returns 4

Client-Side Examples

Modern database drivers provide a more direct way to get this ID.

• Node.js (mysql2): The result object from an INSERT query contains an insertId property.
• Python (mysql-connector-python): The cursor object has a lastrowid attribute after an INSERT operation.
• Java (JDBC): You can retrieve generated keys from the PreparedStatement object.
• PHP (mysqli): The mysqli connection object has an insert_id property.

Advanced AUTO_INCREMENT Topics

Warning: Resequencing and Gaps

If you delete rows from a table, their AUTO_INCREMENT IDs are not reused, which creates gaps in the sequence (e.g., 1, 2, 4, 5). This is normal and expected behavior. You should not try to ‘fix’ these gaps.

Best Practice: Treat AUTO_INCREMENT IDs as opaque, unique identifiers. Their only purpose is to uniquely identify a row. Their sequential nature or the presence of gaps has no impact on database performance or functionality. Attempting to renumber rows is a risky operation that can break foreign key relationships and lead to data corruption.

In the rare and critical case where renumbering is absolutely necessary, the safest approach involves creating a new table, copying the data, and renaming it, but this should be avoided unless you are an expert.

Starting a Sequence at a Specific Value

You can set the starting value for an AUTO_INCREMENT sequence when you create the table or alter it later. This can be useful for data migration or to reserve a range of IDs.

-- Set the starting value during table creation
CREATE TABLE new_table (id INT AUTO_INCREMENT PRIMARY KEY, ...) AUTO_INCREMENT = 1000;

-- Alter an existing table's next auto-increment value
ALTER TABLE employees AUTO_INCREMENT = 200;

AUTO_INCREMENT in Application Code

The following examples demonstrate how to insert a new record and retrieve its auto-generated ID using modern client libraries. As always, credentials should be managed securely, not hardcoded.

Node.js
Python
Java
PHP



`mysql2/promise` returns an object where the first element contains metadata, including `insertId`.

```javascript
const mysql = require('mysql2/promise');
require('dotenv').config();

const pool = mysql.createPool({ /* ... connection config ... */ });

async function addEmployee(name, department, salary) {
    let connection;
    try {
        connection = await pool.getConnection();
        const sql = "INSERT INTO employees (NAME, DEPARTMENT, SALARY) VALUES (?, ?, ?)";
        const [result] = await connection.execute(sql, [name, department, salary]);

        console.log(`Successfully added employee '${name}'.`);
        console.log(`New Employee ID: ${result.insertId}`);
        return result.insertId;
    } catch (error) {
        console.error("Failed to add employee:", error);
    } finally {
        if (connection) connection.release();
    }
}

// Example usage
addEmployee('Eve', 'Finance', 72000.00);

After executing an insert, the cursor.lastrowid attribute holds the new ID.

import mysql.connector
import os
from dotenv import load_dotenv

load_dotenv()

def add_employee(name, department, salary):
    new_id = None
    try:
        with mysql.connector.connect(
            host=os.getenv("DB_HOST"),
            user=os.getenv("DB_USER"),
            password=os.getenv("DB_PASSWORD"),
            database=os.getenv("DB_NAME")
        ) as connection:
            sql = "INSERT INTO employees (NAME, DEPARTMENT, SALARY) VALUES (%s, %s, %s)"
            params = (name, department, salary)

            with connection.cursor() as cursor:
                cursor.execute(sql, params)
                connection.commit() # Important: commit the transaction
                new_id = cursor.lastrowid
                print(f"Successfully added employee '{name}'. New Employee ID: {new_id}")
    except mysql.connector.Error as e:
        print(f"Database error: {e}")
    return new_id

# Example usage
add_employee('Frank', 'IT', 95000.00)

In JDBC, you must signal your intent to retrieve generated keys when creating the PreparedStatement.

import java.sql.*;

public class AddEmployee {
    public static void add(String name, String department, double salary) {
        String url = "jdbc:mysql://" + System.getenv("DB_HOST") + "/" + System.getenv("DB_NAME");
        String user = System.getenv("DB_USER");
        String password = System.getenv("DB_PASSWORD");

        String sql = "INSERT INTO employees(NAME, DEPARTMENT, SALARY) VALUES(?, ?, ?)";

        try (Connection conn = DriverManager.getConnection(url, user, password);
             PreparedStatement pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {

            pstmt.setString(1, name);
            pstmt.setString(2, department);
            pstmt.setDouble(3, salary);

            int affectedRows = pstmt.executeUpdate();

            if (affectedRows > 0) {
                // Retrieve the generated keys
                try (ResultSet generatedKeys = pstmt.getGeneratedKeys()) {
                    if (generatedKeys.next()) {
                        long id = generatedKeys.getLong(1);
                        System.out.println("Successfully added employee '" + name + "'. New ID: " + id);
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        add("Grace", "HR", 58000.00);
    }
}

After a successful INSERT, the insert_id property of the mysqli object is populated.

<?php
require_once __DIR__ . '/vendor/autoload.php';

$dotenv = Dotenv\Dotenv::createImmutable(__DIR__);
$dotenv->load();

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli($_ENV['DB_HOST'], $_ENV['DB_USER'], $_ENV['DB_PASSWORD'], $_ENV['DB_NAME']);

function addEmployee($mysqli, $name, $department, $salary) {
    $sql = "INSERT INTO employees (NAME, DEPARTMENT, SALARY) VALUES (?, ?, ?)";
    try {
        $stmt = $mysqli->prepare($sql);
        // 'ssd' = string, string, decimal/double
        $stmt->bind_param('ssd', $name, $department, $salary);
        $stmt->execute();

        $newId = $mysqli->insert_id;
        echo "Successfully added employee '{$name}'. New Employee ID: {$newId}\n";
        $stmt->close();
        return $newId;
    } catch (mysqli_sql_exception $e) {
        echo "Error: " . $e->getMessage() . "\n";
        return null;
    }
}

// Example usage
addEmployee($mysqli, 'Heidi', 'Operations', 65000.00);
$mysqli->close();
?>