MySQL 自增主键

 自增主键核心特性

1. 自动递增：系统自动分配递增值，无需手动指定

2. 唯一性保证：确保每个值都是唯一的

3. 非空约束：自增字段隐式包含 NOT NULL 约束

4. 单字段限制：每表只能有一个自增字段

5. 数据类型限制：仅适用于整数类型（INT, BIGINT 等）

自增主键语法详解

基本语法

CREATE TABLE table_name (

    id INT AUTO_INCREMENT PRIMARY KEY,

    ...

);

指定起始值和步长

设置起始值为100

CREATE TABLE users (

    id INT AUTO_INCREMENT PRIMARY KEY,

    ...

) AUTO_INCREMENT=100;

设置步长为5（需通过系统变量设置）

SET @@auto_increment_increment=5;

自增主键工作机制

1. 初始行为：

    默认从1开始

    默认增量为1

    插入NULL或0时自动分配下一个值

2. 存储机制：

    当前自增值存储在内存和表定义文件中

    服务器重启后会持久化保存

3. 不连续情况原因：

    事务回滚

    插入失败（如唯一键冲突）

    手动删除记录

    批量插入时预分配的值未被完全使用

高级用法

获取最后插入的ID

INSERT INTO users (name) VALUES ('John');

SELECT LAST_INSERT_ID();  -- 获取刚插入的ID

重置自增值

重置为当前最大值+1

ALTER TABLE users AUTO_INCREMENT = 1;

完全重建表重置

TRUNCATE TABLE users;

复合主键中的自增

自增字段必须是复合主键的第一个字段：

CREATE TABLE order_items (
    id INT AUTO_INCREMENT,
    order_id INT,
    product_id INT,
    PRIMARY KEY (id, order_id)
);

性能优化建议

1. **使用足够大的数据类型**：避免达到上限

   -- 大型系统推荐使用

   id BIGINT AUTO_INCREMENT PRIMARY KEY

2. 批量插入优化：

    单条INSERT插入多行比多条INSERT效率高

    批量插入时自增值连续分配

3. 避免手动指定自增值：可能引起冲突或浪费

实际应用示例

用户表设计：

CREATE TABLE users (
    user_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 
                     ON UPDATE CURRENT_TIMESTAMP
) AUTO_INCREMENT=1000;

插入数据示例

INSERT INTO users (username, email) VALUES 
('john_doe', 'john@example.com'),
('jane_smith', 'jane@example.com');

订单系统设计：

CREATE TABLE orders (
    order_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    order_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    total_amount DECIMAL(12,2) NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
) AUTO_INCREMENT=100000;

分表时设置不同的初始值

CREATE TABLE orders_2023 (

    order_id BIGINT AUTO_INCREMENT PRIMARY KEY,

    ...

) AUTO_INCREMENT=200000;

注意事项

1. 复制环境中的问题：

    在主从复制中需注意自增偏移量设置

    可使用`auto_increment_offset`和`auto_increment_increment`配置

2. InnoDB特性：

    自增计数器在内存中维护

    服务器重启后可能"丢失"部分未使用的自增值

3. 分布式系统考虑：

    自增ID在分布式系统中可能不是最佳选择

    可考虑UUID或雪花算法等替代方案