序列概述
序列是数据库中用于生成唯一数值的对象。虽然 MySQL 没有像 Oracle 或 PostgreSQL 那样的独立序列对象，但提供了多种方式来实现序列功能，主要通过 AUTO_INCREMENT 属性和自定义序列表来实现。
🔄 AUTO_INCREMENT
MySQL 内置的自动递增机制
📊 序列表
自定义表实现的序列功能
🔧 存储过程序列
通过存储过程管理的序列
⚡ 内存序列
基于内存表的高性能序列
序列的应用场景：
- 主键生成：为表生成唯一的主键值
- 订单号生成：创建唯一的业务编号
- 版本控制：为记录生成版本号
- 排序编号：为数据生成排序序号
- 分布式ID：在分布式系统中生成唯一标识
- 批次号生成：为批处理任务生成批次标识
AUTO_INCREMENT 序列
基本使用
-- 创建带有 AUTO_INCREMENT 的表
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 插入数据，ID 自动生成
INSERT INTO users (username, email) VALUES
('john_doe', 'john@example.com'),
('jane_smith', 'jane@example.com'),
('bob_wilson', 'bob@example.com');
-- 查看生成的 ID
SELECT * FROM users;
-- 获取最后插入的 ID
SELECT LAST_INSERT_ID();
-- 查看当前 AUTO_INCREMENT 值
SHOW TABLE STATUS LIKE 'users';
AUTO_INCREMENT 属性设置
-- 设置 AUTO_INCREMENT 起始值
CREATE TABLE products (
product_id INT AUTO_INCREMENT PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
price DECIMAL(10,2)
) AUTO_INCREMENT = 1000;
-- 修改现有表的 AUTO_INCREMENT 值
ALTER TABLE products AUTO_INCREMENT = 2000;
-- 查看 AUTO_INCREMENT 相关变量
SHOW VARIABLES LIKE 'auto_increment%';
-- 设置 AUTO_INCREMENT 步长（全局）
SET @@auto_increment_increment = 2;
SET @@auto_increment_offset = 1;
-- 设置 AUTO_INCREMENT 步长（会话）
SET SESSION auto_increment_increment = 5;
SET SESSION auto_increment_offset = 1;
-- 插入数据测试步长
INSERT INTO products (product_name, price) VALUES
('Product A', 99.99),
('Product B', 149.99),
('Product C', 199.99);
SELECT * FROM products;
AUTO_INCREMENT 的限制和注意事项
-- 重置 AUTO_INCREMENT（危险操作）
TRUNCATE TABLE products; -- 重置为初始值
-- 或者
ALTER TABLE products AUTO_INCREMENT = 1;
-- 手动插入指定 ID
INSERT INTO users (id, username, email) VALUES
(100, 'admin', 'admin@example.com');
-- 后续插入将从 101 开始
INSERT INTO users (username, email) VALUES
('test_user', 'test@example.com');
-- 查看结果
SELECT * FROM users ORDER BY id;
-- 检查 AUTO_INCREMENT 当前值
SELECT
TABLE_NAME,
AUTO_INCREMENT
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = DATABASE()
AND AUTO_INCREMENT IS NOT NULL;
AUTO_INCREMENT 注意事项：
- 每个表只能有一个 AUTO_INCREMENT 列
- AUTO_INCREMENT 列必须是索引的一部分
- 删除记录不会重置 AUTO_INCREMENT 值
- 手动插入大值会影响后续自动生成的值
- 在主从复制环境中需要注意步长设置
- 达到数据类型最大值时会产生错误
自定义序列表
创建序列表
-- 创建序列管理表
CREATE TABLE sequences (
sequence_name VARCHAR(50) PRIMARY KEY,
current_value BIGINT NOT NULL DEFAULT 0,
increment_value INT NOT NULL DEFAULT 1,
min_value BIGINT DEFAULT 1,
max_value BIGINT DEFAULT 9223372036854775807,
cycle_flag BOOLEAN DEFAULT FALSE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
-- 初始化一些序列
INSERT INTO sequences (sequence_name, current_value, increment_value) VALUES
('order_seq', 10000, 1),
('invoice_seq', 100000, 1),
('batch_seq', 1, 10),
('user_code_seq', 1000, 2);
-- 查看序列状态
SELECT * FROM sequences;
序列操作函数
-- 创建获取下一个序列值的函数
DELIMITER //
CREATE FUNCTION NEXTVAL(seq_name VARCHAR(50))
RETURNS BIGINT
READS SQL DATA
DETERMINISTIC
BEGIN
DECLARE next_val BIGINT;
DECLARE inc_val INT;
DECLARE max_val BIGINT;
DECLARE cycle_flag BOOLEAN;
-- 获取序列信息并更新
SELECT current_value + increment_value, increment_value, max_value, cycle_flag
INTO next_val, inc_val, max_val, cycle_flag
FROM sequences
WHERE sequence_name = seq_name
FOR UPDATE;
-- 检查是否超过最大值
IF next_val > max_val THEN
IF cycle_flag THEN
-- 循环模式，重置为最小值
SELECT min_value INTO next_val FROM sequences WHERE sequence_name = seq_name;
ELSE
-- 非循环模式，抛出错误
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Sequence value exceeds maximum';
END IF;
END IF;
-- 更新序列值
UPDATE sequences
SET current_value = next_val,
updated_at = CURRENT_TIMESTAMP
WHERE sequence_name = seq_name;
RETURN next_val;
END //
DELIMITER ;
-- 创建获取当前序列值的函数
DELIMITER //
CREATE FUNCTION CURRVAL(seq_name VARCHAR(50))
RETURNS BIGINT
READS SQL DATA
DETERMINISTIC
BEGIN
DECLARE curr_val BIGINT;
SELECT current_value INTO curr_val
FROM sequences
WHERE sequence_name = seq_name;
RETURN curr_val;
END //
DELIMITER ;
-- 创建设置序列值的存储过程
DELIMITER //
CREATE PROCEDURE SETVAL(IN seq_name VARCHAR(50), IN new_value BIGINT)
BEGIN
UPDATE sequences
SET current_value = new_value,
updated_at = CURRENT_TIMESTAMP
WHERE sequence_name = seq_name;
IF ROW_COUNT() = 0 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Sequence not found';
END IF;
END //
DELIMITER ;
使用自定义序列
-- 使用序列生成订单号
SELECT NEXTVAL('order_seq') as next_order_id;
SELECT NEXTVAL('order_seq') as next_order_id;
SELECT NEXTVAL('order_seq') as next_order_id;
-- 查看当前序列值
SELECT CURRVAL('order_seq') as current_order_id;
-- 在表中使用序列
CREATE TABLE orders (
order_id BIGINT PRIMARY KEY,
customer_id INT NOT NULL,
order_date DATE NOT NULL,
total_amount DECIMAL(10,2)
);
-- 插入数据时使用序列
INSERT INTO orders (order_id, customer_id, order_date, total_amount) VALUES
(NEXTVAL('order_seq'), 1, CURDATE(), 299.99),
(NEXTVAL('order_seq'), 2, CURDATE(), 149.50),
(NEXTVAL('order_seq'), 1, CURDATE(), 89.99);
-- 查看结果
SELECT * FROM orders;
-- 设置序列值
CALL SETVAL('order_seq', 20000);
SELECT NEXTVAL('order_seq') as next_value;
-- 查看所有序列的当前状态
SELECT
sequence_name,
current_value,
increment_value,
CONCAT('Next value: ', current_value + increment_value) as next_value
FROM sequences;
高级序列实现
带格式的序列号生成
-- 创建格式化序列表
CREATE TABLE formatted_sequences (
sequence_name VARCHAR(50) PRIMARY KEY,
prefix VARCHAR(20) DEFAULT '',
current_value BIGINT NOT NULL DEFAULT 0,
increment_value INT NOT NULL DEFAULT 1,
padding_length INT DEFAULT 6,
suffix VARCHAR(20) DEFAULT '',
reset_period ENUM('NONE', 'DAILY', 'MONTHLY', 'YEARLY') DEFAULT 'NONE',
last_reset_date DATE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 初始化格式化序列
INSERT INTO formatted_sequences
(sequence_name, prefix, current_value, padding_length, reset_period) VALUES
('invoice_num', 'INV', 0, 6, 'MONTHLY'),
('order_num', 'ORD', 0, 8, 'DAILY'),
('batch_num', 'BAT', 0, 4, 'NONE');
-- 创建格式化序列生成函数
DELIMITER //
CREATE FUNCTION NEXT_FORMATTED_VAL(seq_name VARCHAR(50))
RETURNS VARCHAR(100)
READS SQL DATA
DETERMINISTIC
BEGIN
DECLARE next_val BIGINT;
DECLARE prefix_val VARCHAR(20);
DECLARE suffix_val VARCHAR(20);
DECLARE padding_len INT;
DECLARE reset_period_val VARCHAR(10);
DECLARE last_reset DATE;
DECLARE should_reset BOOLEAN DEFAULT FALSE;
DECLARE formatted_result VARCHAR(100);
-- 获取序列配置
SELECT prefix, current_value + increment_value, padding_length, suffix,
reset_period, last_reset_date
INTO prefix_val, next_val, padding_len, suffix_val, reset_period_val, last_reset
FROM formatted_sequences
WHERE sequence_name = seq_name
FOR UPDATE;
-- 检查是否需要重置
CASE reset_period_val
WHEN 'DAILY' THEN
SET should_reset = (last_reset IS NULL OR last_reset < CURDATE());
WHEN 'MONTHLY' THEN
SET should_reset = (last_reset IS NULL OR
YEAR(last_reset) < YEAR(CURDATE()) OR
MONTH(last_reset) < MONTH(CURDATE()));
WHEN 'YEARLY' THEN
SET should_reset = (last_reset IS NULL OR YEAR(last_reset) < YEAR(CURDATE()));
ELSE
SET should_reset = FALSE;
END CASE;
-- 如果需要重置，将值重置为1
IF should_reset THEN
SET next_val = 1;
END IF;
-- 更新序列值
UPDATE formatted_sequences
SET current_value = next_val,
last_reset_date = CASE WHEN should_reset THEN CURDATE() ELSE last_reset_date END
WHERE sequence_name = seq_name;
-- 格式化结果
SET formatted_result = CONCAT(
IFNULL(prefix_val, ''),
CASE
WHEN reset_period_val = 'DAILY' THEN DATE_FORMAT(CURDATE(), '%Y%m%d')
WHEN reset_period_val = 'MONTHLY' THEN DATE_FORMAT(CURDATE(), '%Y%m')
WHEN reset_period_val = 'YEARLY' THEN DATE_FORMAT(CURDATE(), '%Y')
ELSE ''
END,
LPAD(next_val, padding_len, '0'),
IFNULL(suffix_val, '')
);
RETURN formatted_result;
END //
DELIMITER ;
-- 测试格式化序列
SELECT NEXT_FORMATTED_VAL('invoice_num') as invoice_number;
SELECT NEXT_FORMATTED_VAL('invoice_num') as invoice_number;
SELECT NEXT_FORMATTED_VAL('order_num') as order_number;
SELECT NEXT_FORMATTED_VAL('batch_num') as batch_number;
-- 查看序列状态
SELECT * FROM formatted_sequences;
分布式序列实现
-- 创建分布式序列表
CREATE TABLE distributed_sequences (
sequence_name VARCHAR(50),
node_id INT,
current_value BIGINT NOT NULL DEFAULT 0,
step_size INT NOT NULL DEFAULT 1000,
max_value BIGINT NOT NULL,
last_allocated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (sequence_name, node_id)
);
-- 初始化分布式序列（假设有3个节点）
INSERT INTO distributed_sequences
(sequence_name, node_id, current_value, step_size, max_value) VALUES
('global_id', 1, 1000, 1000, 1999),
('global_id', 2, 2000, 1000, 2999),
('global_id', 3, 3000, 1000, 3999);
-- 创建分布式序列分配函数
DELIMITER //
CREATE FUNCTION ALLOC_DISTRIBUTED_RANGE(seq_name VARCHAR(50), node_id INT)
RETURNS JSON
READS SQL DATA
DETERMINISTIC
BEGIN
DECLARE start_val BIGINT;
DECLARE end_val BIGINT;
DECLARE step_val INT;
DECLARE result JSON;
-- 获取当前范围并分配新范围
SELECT max_value + 1, step_size
INTO start_val, step_val
FROM distributed_sequences
WHERE sequence_name = seq_name AND node_id = node_id
FOR UPDATE;
SET end_val = start_val + step_val - 1;
-- 更新分配的范围
UPDATE distributed_sequences
SET current_value = start_val,
max_value = end_val,
last_allocated = CURRENT_TIMESTAMP
WHERE sequence_name = seq_name AND node_id = node_id;
-- 返回分配的范围
SET result = JSON_OBJECT(
'start_value', start_val,
'end_value', end_val,
'step_size', step_val
);
RETURN result;
END //
DELIMITER ;
-- 测试分布式序列分配
SELECT ALLOC_DISTRIBUTED_RANGE('global_id', 1) as allocated_range;
SELECT ALLOC_DISTRIBUTED_RANGE('global_id', 2) as allocated_range;
-- 查看分配状态
SELECT
sequence_name,
node_id,
current_value,
max_value,
CONCAT(current_value, ' - ', max_value) as allocated_range,
last_allocated
FROM distributed_sequences
ORDER BY sequence_name, node_id;
内存表序列（高性能）
-- 创建内存表序列（重启后数据丢失）
CREATE TABLE memory_sequences (
sequence_name VARCHAR(50) PRIMARY KEY,
current_value BIGINT NOT NULL DEFAULT 0,
increment_value INT NOT NULL DEFAULT 1
) ENGINE=MEMORY;
-- 初始化内存序列
INSERT INTO memory_sequences (sequence_name, current_value, increment_value) VALUES
('session_id', 0, 1),
('temp_id', 1000, 1),
('cache_key', 0, 1);
-- 创建内存序列函数
DELIMITER //
CREATE FUNCTION MEMORY_NEXTVAL(seq_name VARCHAR(50))
RETURNS BIGINT
READS SQL DATA
DETERMINISTIC
BEGIN
DECLARE next_val BIGINT;
-- 原子性更新并获取新值
UPDATE memory_sequences
SET current_value = current_value + increment_value
WHERE sequence_name = seq_name;
-- 获取更新后的值
SELECT current_value INTO next_val
FROM memory_sequences
WHERE sequence_name = seq_name;
RETURN next_val;
END //
DELIMITER ;
-- 测试内存序列性能
SELECT MEMORY_NEXTVAL('session_id') as session_id;
SELECT MEMORY_NEXTVAL('session_id') as session_id;
SELECT MEMORY_NEXTVAL('temp_id') as temp_id;
-- 批量测试（模拟高并发）
SELECT
MEMORY_NEXTVAL('cache_key') as id1,
MEMORY_NEXTVAL('cache_key') as id2,
MEMORY_NEXTVAL('cache_key') as id3;
-- 查看内存序列状态
SELECT * FROM memory_sequences;
序列性能优化
批量序列分配
-- 创建批量序列分配存储过程
DELIMITER //
CREATE PROCEDURE ALLOC_SEQUENCE_BATCH(
IN seq_name VARCHAR(50),
IN batch_size INT,
OUT start_value BIGINT,
OUT end_value BIGINT
)
BEGIN
DECLARE current_val BIGINT;
DECLARE inc_val INT;
-- 获取当前值并分配批次
SELECT current_value, increment_value
INTO current_val, inc_val
FROM sequences
WHERE sequence_name = seq_name
FOR UPDATE;
SET start_value = current_val + inc_val;
SET end_value = start_value + (batch_size * inc_val) - 1;
-- 更新序列值
UPDATE sequences
SET current_value = end_value
WHERE sequence_name = seq_name;
END //
DELIMITER ;
-- 测试批量分配
CALL ALLOC_SEQUENCE_BATCH('order_seq', 100, @start, @end);
SELECT @start as batch_start, @end as batch_end;
-- 应用程序可以在内存中使用这个范围
SELECT
@start + 0 as id1,
@start + 1 as id2,
@start + 2 as id3,
'...' as more_ids,
@end as last_id;
序列缓存机制
-- 创建序列缓存表
CREATE TABLE sequence_cache (
sequence_name VARCHAR(50) PRIMARY KEY,
cache_size INT NOT NULL DEFAULT 100,
cached_start BIGINT,
cached_end BIGINT,
current_cached BIGINT,
last_refresh TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 初始化缓存配置
INSERT INTO sequence_cache (sequence_name, cache_size) VALUES
('fast_seq', 1000),
('medium_seq', 100),
('slow_seq', 10);
-- 创建缓存序列函数
DELIMITER //
CREATE FUNCTION CACHED_NEXTVAL(seq_name VARCHAR(50))
RETURNS BIGINT
READS SQL DATA
DETERMINISTIC
BEGIN
DECLARE next_val BIGINT;
DECLARE cache_start BIGINT;
DECLARE cache_end BIGINT;
DECLARE cache_current BIGINT;
DECLARE cache_sz INT;
DECLARE need_refresh BOOLEAN DEFAULT FALSE;
-- 检查缓存状态
SELECT cached_start, cached_end, current_cached, cache_size
INTO cache_start, cache_end, cache_current, cache_sz
FROM sequence_cache
WHERE sequence_name = seq_name
FOR UPDATE;
-- 检查是否需要刷新缓存
IF cache_current IS NULL OR cache_current >= cache_end THEN
SET need_refresh = TRUE;
END IF;
-- 刷新缓存
IF need_refresh THEN
-- 从主序列表分配新的缓存范围
CALL ALLOC_SEQUENCE_BATCH(seq_name, cache_sz, cache_start, cache_end);
-- 更新缓存信息
UPDATE sequence_cache
SET cached_start = cache_start,
cached_end = cache_end,
current_cached = cache_start,
last_refresh = CURRENT_TIMESTAMP
WHERE sequence_name = seq_name;
SET next_val = cache_start;
ELSE
-- 使用缓存中的下一个值
SET next_val = cache_current + 1;
-- 更新缓存当前值
UPDATE sequence_cache
SET current_cached = next_val
WHERE sequence_name = seq_name;
END IF;
RETURN next_val;
END //
DELIMITER ;
-- 测试缓存序列
SELECT CACHED_NEXTVAL('fast_seq') as cached_id;
SELECT CACHED_NEXTVAL('fast_seq') as cached_id;
SELECT CACHED_NEXTVAL('fast_seq') as cached_id;
-- 查看缓存状态
SELECT
sc.*,
CONCAT(cached_start, ' - ', cached_end) as cached_range,
(cached_end - current_cached) as remaining_cache
FROM sequence_cache sc;
序列方案比较
| 方案 | 性能 | 灵活性 | 并发性 | 持久性 | 复杂度 | 适用场景 |
|---|---|---|---|---|---|---|
| AUTO_INCREMENT | 很高 | 低 | 很好 | 很好 | 很低 | 简单主键生成 |
| 序列表 | 中等 | 很高 | 中等 | 很好 | 中等 | 业务编号生成 |
| 格式化序列 | 中等 | 很高 | 中等 | 很好 | 高 | 复杂业务编号 |
| 分布式序列 | 高 | 高 | 很好 | 很好 | 很高 | 分布式系统 |
| 内存序列 | 很高 | 中等 | 很好 | 差 | 中等 | 临时ID生成 |
| 缓存序列 | 很高 | 高 | 很好 | 很好 | 高 | 高并发场景 |
序列监控和维护
序列状态监控
-- 创建序列监控视图
CREATE VIEW sequence_monitor AS
SELECT
s.sequence_name,
s.current_value,
s.increment_value,
s.max_value,
ROUND((s.current_value / s.max_value) * 100, 2) as usage_percentage,
CASE
WHEN s.current_value / s.max_value > 0.9 THEN 'CRITICAL'
WHEN s.current_value / s.max_value > 0.8 THEN 'WARNING'
WHEN s.current_value / s.max_value > 0.7 THEN 'CAUTION'
ELSE 'NORMAL'
END as status,
s.updated_at as last_used
FROM sequences s;
-- 查看序列监控信息
SELECT * FROM sequence_monitor ORDER BY usage_percentage DESC;
-- 查找需要注意的序列
SELECT * FROM sequence_monitor
WHERE status IN ('WARNING', 'CRITICAL')
ORDER BY usage_percentage DESC;
-- 创建序列使用统计
CREATE TABLE sequence_usage_log (
id INT AUTO_INCREMENT PRIMARY KEY,
sequence_name VARCHAR(50),
operation VARCHAR(20),
old_value BIGINT,
new_value BIGINT,
operation_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX idx_seq_time (sequence_name, operation_time)
);
-- 创建序列使用记录触发器
DELIMITER //
CREATE TRIGGER sequence_usage_trigger
AFTER UPDATE ON sequences
FOR EACH ROW
BEGIN
IF OLD.current_value != NEW.current_value THEN
INSERT INTO sequence_usage_log
(sequence_name, operation, old_value, new_value)
VALUES
(NEW.sequence_name, 'UPDATE', OLD.current_value, NEW.current_value);
END IF;
END //
DELIMITER ;
-- 测试序列使用记录
SELECT NEXTVAL('order_seq');
SELECT NEXTVAL('order_seq');
-- 查看使用记录
SELECT * FROM sequence_usage_log ORDER BY operation_time DESC LIMIT 10;
序列维护操作
-- 序列备份
CREATE TABLE sequences_backup AS
SELECT *, NOW() as backup_time FROM sequences;
-- 序列恢复
-- REPLACE INTO sequences
-- SELECT sequence_name, current_value, increment_value, min_value, max_value,
-- cycle_flag, created_at, updated_at
-- FROM sequences_backup
-- WHERE backup_time = '2024-01-01 12:00:00';
-- 序列重置（谨慎操作）
DELIMITER //
CREATE PROCEDURE RESET_SEQUENCE(IN seq_name VARCHAR(50), IN reset_value BIGINT)
BEGIN
DECLARE old_value BIGINT;
-- 记录旧值
SELECT current_value INTO old_value FROM sequences WHERE sequence_name = seq_name;
-- 重置序列
UPDATE sequences
SET current_value = reset_value,
updated_at = CURRENT_TIMESTAMP
WHERE sequence_name = seq_name;
-- 记录重置操作
INSERT INTO sequence_usage_log
(sequence_name, operation, old_value, new_value)
VALUES
(seq_name, 'RESET', old_value, reset_value);
SELECT CONCAT('Sequence ', seq_name, ' reset from ', old_value, ' to ', reset_value) as result;
END //
DELIMITER ;
-- 序列清理（删除未使用的序列）
DELIMITER //
CREATE PROCEDURE CLEANUP_UNUSED_SEQUENCES(IN days_threshold INT)
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE seq_name VARCHAR(50);
DECLARE last_used TIMESTAMP;
DECLARE cleanup_cursor CURSOR FOR
SELECT sequence_name, updated_at
FROM sequences
WHERE updated_at < DATE_SUB(NOW(), INTERVAL days_threshold DAY);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
CREATE TEMPORARY TABLE cleanup_report (
sequence_name VARCHAR(50),
last_used TIMESTAMP,
action VARCHAR(20)
);
OPEN cleanup_cursor;
cleanup_loop: LOOP
FETCH cleanup_cursor INTO seq_name, last_used;
IF done THEN
LEAVE cleanup_loop;
END IF;
-- 记录清理信息
INSERT INTO cleanup_report VALUES (seq_name, last_used, 'MARKED_FOR_CLEANUP');
-- 这里可以添加实际的删除逻辑
-- DELETE FROM sequences WHERE sequence_name = seq_name;
END LOOP;
CLOSE cleanup_cursor;
-- 显示清理报告
SELECT * FROM cleanup_report;
DROP TEMPORARY TABLE cleanup_report;
END //
DELIMITER ;
-- 运行清理检查（不实际删除）
CALL CLEANUP_UNUSED_SEQUENCES(30);
最佳实践和注意事项
序列设计最佳实践：
- 选择合适的数据类型：考虑序列的最大值需求
- 设置合理的步长：平衡性能和数值连续性
- 考虑并发性：在高并发场景下使用批量分配
- 实现监控机制：及时发现序列即将耗尽的情况
- 定期备份序列状态：防止数据丢失
- 文档化序列用途：便于维护和管理
常见陷阱和注意事项：
- AUTO_INCREMENT 在删除记录后不会回收ID
- 序列表需要考虑事务隔离级别的影响
- 内存表序列在服务器重启后会丢失
- 分布式序列需要处理节点故障的情况
- 格式化序列的重置逻辑要仔细测试
- 高并发下的序列竞争可能影响性能
- 序列达到最大值时的处理策略要提前规划
| 场景 | 推荐方案 | 关键考虑 |
|---|---|---|
| 简单主键 | AUTO_INCREMENT | 性能最优，使用简单 |
| 业务编号 | 自定义序列表 | 灵活性和可控性 |
| 高并发场景 | 缓存序列 | 减少数据库访问 |
| 分布式系统 | 分布式序列 | 避免单点故障 |
| 临时标识 | 内存序列 | 性能优先，可丢失 |
| 复杂格式 | 格式化序列 | 业务规则复杂 |
-- 清理示例对象
DROP VIEW IF EXISTS sequence_monitor;
DROP TRIGGER IF EXISTS sequence_usage_trigger;
DROP TABLE IF EXISTS sequence_usage_log;
DROP TABLE IF EXISTS sequences_backup;
DROP TABLE IF EXISTS sequence_cache;
DROP TABLE IF EXISTS distributed_sequences;
DROP TABLE IF EXISTS formatted_sequences;
DROP TABLE IF EXISTS memory_sequences;
DROP TABLE IF EXISTS sequences;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS users;
DROP FUNCTION IF EXISTS CACHED_NEXTVAL;
DROP FUNCTION IF EXISTS ALLOC_DISTRIBUTED_RANGE;
DROP FUNCTION IF EXISTS NEXT_FORMATTED_VAL;
DROP FUNCTION IF EXISTS MEMORY_NEXTVAL;
DROP FUNCTION IF EXISTS CURRVAL;
DROP FUNCTION IF EXISTS NEXTVAL;
DROP PROCEDURE IF EXISTS CLEANUP_UNUSED_SEQUENCES;
DROP PROCEDURE IF EXISTS RESET_SEQUENCE;
DROP PROCEDURE IF EXISTS ALLOC_SEQUENCE_BATCH;
DROP PROCEDURE IF EXISTS SETVAL;
SELECT 'Sequence tutorial completed. All example objects cleaned up.' AS message;