MySQL - 使用序列

MySQL - 使用 AUTO_INCREMENT 生成唯一 ID

在数据库设计中，几乎每张表都需要一个主键——一个为每行提供唯一值的列，用作其标识符。生成这些唯一键的一种常见方法是使用数字序列（例如 1、2、3 等）。

使用 AUTO_INCREMENT 模拟序列

尽管某些数据库系统具有专用的 SEQUENCE（序列）对象，但 MySQL 通过 AUTO_INCREMENT 属性实现此功能。当您将此属性应用于整数列时，每当插入新行时，MySQL 都会自动为该列生成一个新的、唯一的整数。

默认情况下，AUTO_INCREMENT 序列从 1 开始，每添加一条新记录递增 1。此列必须定义为键（通常是 PRIMARY KEY）。

语法

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

示例：创建带自动递增 ID 的表

让我们创建一个 employees 表，其中 ID 自动生成。

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

插入记录时，您可以省略 ID 列或将其值指定为 NULL 或 0。MySQL 将用序列中的下一个值替换它。

-- MySQL 将自动生成 ID
INSERT INTO employees (NAME, DEPARTMENT, SALARY) VALUES
    ('Alice', 'Engineering', 75000.00),
    ('Bob', 'Marketing', 62000.00),
    ('Charlie', 'Engineering', 81000.00);

输出

查询表显示 ID 值是按顺序生成的。

SELECT * FROM employees;

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

获取最后插入的 ID

通常需要知道刚刚插入的记录的 ID，例如，以便在另一个表中创建相关记录。您可以使用 LAST_INSERT_ID() 函数检索此值。

SQL 示例

此函数必须在您的 INSERT 语句之后，在同一数据库连接中立即调用。

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

客户端示例

现代数据库驱动程序提供了更直接的方式来获取此 ID。

• Node.js (mysql2)：INSERT 查询的结果对象包含一个 insertId 属性。
• Python (mysql-connector-python)：INSERT 操作后，游标对象具有一个 lastrowid 属性。
• Java (JDBC)：您可以从 PreparedStatement 对象检索生成的键。
• PHP (mysqli)：mysqli 连接对象具有一个 insert_id 属性。

高级 AUTO_INCREMENT 主题

警告：重新排序和间隙

如果您从表中删除行，其 AUTO_INCREMENT ID 不会被重复使用，这会在序列中创建间隙（例如 1、2、4、5）。这是正常且预期的行为。您不应该尝试“修复”这些间隙。

最佳实践： 将 AUTO_INCREMENT ID 视为不透明的唯一标识符。它们唯一的目的是唯一标识一行。它们的顺序性或间隙的存在对数据库性能或功能没有影响。尝试重新编号行是一项有风险的操作，可能会破坏外键关系并导致数据损坏。

在极少数且关键的情况下，如果绝对需要重新编号，最安全的方法是创建一个新表，复制数据，然后重命名它，但除非您是专家，否则应避免这样做。

从特定值开始序列

您可以在创建表时或之后修改表时，为 AUTO_INCREMENT 序列设置起始值。这对于数据迁移或保留一段 ID 范围非常有用。

-- 在表创建期间设置起始值
CREATE TABLE new_table (id INT AUTO_INCREMENT PRIMARY KEY, ...) AUTO_INCREMENT = 1000;

-- 修改现有表的下一个自动递增值
ALTER TABLE employees AUTO_INCREMENT = 200;

AUTO_INCREMENT 在应用程序代码中的应用

以下示例演示了如何使用现代客户端库插入新记录并检索其自动生成的 ID。一如既往，凭证应安全管理，而不是硬编码。

Node.js
Python
Java
PHP



`mysql2/promise` 返回一个对象，其中第一个元素包含元数据，包括 `insertId`。

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

const pool = mysql.createPool({ /* ... 连接配置 ... */ });

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(`成功添加员工 '${name}'。`);
        console.log(`新员工 ID：${result.insertId}`);
        return result.insertId;
    } catch (error) {
        console.error("添加员工失败：", error);
    } finally {
        if (connection) connection.release(); // 释放连接回连接池
    }
}

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

执行插入后，cursor.lastrowid 属性将保存新 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() # 重要：提交事务
                new_id = cursor.lastrowid
                print(f"成功添加员工 '{name}'。新员工 ID：{new_id}")
    except mysql.connector.Error as e:
        print(f"数据库错误：{e}")
    return new_id

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

在 JDBC 中，创建 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) {
                // 检索生成的键
                try (ResultSet generatedKeys = pstmt.getGeneratedKeys()) {
                    if (generatedKeys.next()) {
                        long id = generatedKeys.getLong(1);
                        System.out.println("成功添加员工 '" + name + "'。新 ID：" + id);
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

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

成功 INSERT 后，mysqli 对象的 insert_id 属性会被填充。

<?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 "成功添加员工 '{$name}'。新员工 ID：{$newId}\n";
        $stmt->close();
        return $newId;
    } catch (mysqli_sql_exception $e) {
        echo "错误：" . $e->getMessage() . "\n";
        return null;
    }
}

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