序列概述

序列是数据库中用于生成唯一数值的对象。虽然 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', '[email protected]'),
('jane_smith', '[email protected]'),
('bob_wilson', '[email protected]');

-- 查看生成的 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', '[email protected]');

-- 后续插入将从 101 开始
INSERT INTO users (username, email) VALUES
('test_user', '[email protected]');

-- 查看结果
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;