MySQL 序列使用学习笔记

MySQL 序列是一种生成连续数字序列的方法，类似于自增 id 字段。但是序列可以用于生成任意数字，并且可以在多张表中共享，使得序列可以更加灵活地应用于不同场景下。

创建序列

创建一个数据库 test，并创建一个序列 my_sequence。

sqlCopy Code
CREATE DATABASE test;

USE test;

CREATE TABLE sequences (
    sequence_name VARCHAR(100) NOT NULL,
    value BIGINT NOT NULL,
    PRIMARY KEY (sequence_name)
);

INSERT INTO sequences (sequence_name, value) VALUES ('my_sequence', 1);

这里创建了一个名为 sequences 的表，用于存储所有的序列。每个序列都有一个唯一的名称，对应表中的 sequence_name 列。序列从第一个值开始，对应表中的 value 列。这里以 my_sequence 为例，将其初始值设为 1。

使用序列

在需要使用该序列的表中添加一个 sequence_id 字段，使用以下语句获取序列中的下一个值：

sqlCopy Code
UPDATE sequences SET value = LAST_INSERT_ID(value + 1) WHERE sequence_name = 'my_sequence';

SELECT LAST_INSERT_ID();

这里使用了 LAST_INSERT_ID() 函数来获取最近插入的自增 id 值。由于该函数只能使用在自增 id 列上，因此这里将自定义的序列值存放在了 value 列中。通过修改该列的值，再使用 LAST_INSERT_ID() 函数获取下一个序列值。

以下是在表中插入数据时，如何使用序列作为自定义 id 值：

sqlCopy Code
INSERT INTO my_table (id, name, age) VALUES (?, ?, ?);

UPDATE sequences SET value = LAST_INSERT_ID(value + 1) WHERE sequence_name = 'my_sequence';

SELECT LAST_INSERT_ID();

这里使用问号占位符来代替 id 列的值，然后通过更新序列的方法获取下一个值，并将其设置为该记录的 id 值。

实例

假设有两个表 customers 和 orders，需要在每个表中使用相同的序列 customer_id_seq 作为主键 id：

sqlCopy Code
CREATE TABLE customers (
    id BIGINT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    address VARCHAR(200) NOT NULL,
    phone VARCHAR(20) NOT NULL
);

CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    order_date DATE NOT NULL,
    amount DECIMAL(10,2) NOT NULL
);

INSERT INTO sequences (sequence_name, value) VALUES ('customer_id_seq', 1);

-- insert a new customer
UPDATE sequences SET value = LAST_INSERT_ID(value + 1) WHERE sequence_name = 'customer_id_seq';
INSERT INTO customers (id, name, address, phone) VALUES (LAST_INSERT_ID(), 'John Smith', '123 Main St', '555-1234');

-- insert a new order for customer with id 1
UPDATE sequences SET value = LAST_INSERT_ID(value + 1) WHERE sequence_name = 'customer_id_seq';
INSERT INTO orders (id, customer_id, order_date, amount) VALUES (LAST_INSERT_ID(), 1, '2023-06-03', 99.99);

这里创建了两个表，每个表中都有一个 id 字段。在插入新记录时，使用相同的序列 customer_id_seq 来获取下一个 id 值，并将其设置为该记录的 id 字段值。由于序列只需要在序列表中创建一次，因此可以在多张表中共享。