划线反馈

使用自建存储过程将 DATABASE 中的表批量加入或移出表组

更新时间：2026-05-29 08:46

适用版本：V2.1.x、V2.2.x、V3.1.x、V3.2.x、V4.0.x、V4.1.x、V4.2.x、V4.3.x、V4.4.x内容类型：TechNote

表组（Table Group）是 OceanBase 数据库中的一个逻辑概念，表示一组表的集合。默认情况下，不同表之间的数据是随机分布的，没有关系。通过定义表组，可以控制一组表在物理存储上的邻近关系。作为 OceanBase 分布式数据库的一种重要的性能优化手段，表组的作用是在分布式架构下，解决数据分布带来的 SQL 跨机问题，尽量做到数据跨机存，SQL 本地跑，保证数据库能够水平扩展的前提下，业务响应时间保持在稳定水准，具体对应的 SQL 场景有如下两个：

事务内有 SQL 涉及不同表的场景： 创建表组后，事务内的设计多个表的提交将尽量在一台机器完成，避免跨机事务，降低事务响应时间。
多表关联场景： 创建表组后，多表关联的查询将在一台机器完成，避免跨机查询，降低查询响应时间。

本文主要介绍如何使用用户自建的 PL 存储过程，将 MySQL 模式租户下的一批数据库按照业务模块绑定到某个表组中去，以及移出表组。

详细说明

add_db_to_tg

该存储过程用于将 db 里面的表一张一张地串行加入到 tg 中去：

DELIMITER $$
drop procedure if exists test.add_db_to_tg;
CREATE PROCEDURE test.add_db_to_tg(p_db_name VARCHAR(100), p_tg_name VARCHAR(100))
BEGIN
    DECLARE v_done INT DEFAULT FALSE;
    DECLARE v_table_name VARCHAR(100);

    -- 声明游标来获取所有需要加入表组的表
    DECLARE table_cursor CURSOR FOR SELECT a.TABLE_NAME FROM information_schema.TABLES a WHERE a.TABLE_SCHEMA=p_db_name AND a.TABLE_TYPE='BASE TABLE' minus select b.table_name from oceanbase.dba_ob_tablegroup_tables b where b.tablegroup_name=p_tg_name and b.owner=p_db_name;

    -- 声明未找到记录时的处理程序
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;

    -- 为db指定缺省的 tg
    SET @alter_sql = CONCAT('alter database ', p_db_name, ' default tablegroup=', p_tg_name);
    PREPARE stmt FROM @alter_sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    -- 打开游标
    OPEN table_cursor;

    -- 开始循环处理每张表
    FETCH table_cursor INTO v_table_name;
    WHILE v_done<>TRUE DO
        -- 构建 ALTER TABLEGROUP 语句
        SET @alter_sql = CONCAT('ALTER TABLEGROUP ', p_tg_name, ' ADD ', p_db_name, '.', v_table_name);
        select @alter_sql as '执行的 SQL';

        -- 准备并执行动态 SQL
        PREPARE stmt FROM @alter_sql;

        -- 执行 ALTER 语句
        EXECUTE stmt;

        DEALLOCATE PREPARE stmt;
        FETCH table_cursor INTO v_table_name;
    END WHILE;

    -- 关闭游标
    CLOSE table_cursor;
END;
$$
DELIMITER ;

调用示例：

call test.add_db_to_tg('sbtest', 'tg1');
call test.add_db_to_tg('db1', 'tg1');

add_db_to_tg_in_batch

为提高执行效率，支持将单个 db 里面的表按某个固定的 batch size 成批加入到 tg 中去:

DELIMITER $$
drop procedure if exists test.add_db_to_tg_in_batch;
CREATE PROCEDURE test.add_db_to_tg_in_batch(
    IN p_db_name VARCHAR(64),
    IN p_tg_name VARCHAR(64),
    IN p_batch_size int
)
BEGIN
    DECLARE v_table_name VARCHAR(64);
    DECLARE v_counter INT DEFAULT 0;
    DECLARE v_total_tables INT DEFAULT 0;
    DECLARE v_current_batch INT DEFAULT 1;
    DECLARE v_sql_batch TEXT DEFAULT '';
    DECLARE v_done INT DEFAULT FALSE;

    -- 声明游标来获取所有需要加入表组的表
    DECLARE table_cursor CURSOR FOR SELECT a.TABLE_NAME FROM information_schema.TABLES a WHERE a.TABLE_SCHEMA=p_db_name AND a.TABLE_TYPE='BASE TABLE' minus select b.table_name from oceanbase.dba_ob_tablegroup_tables b where b.tablegroup_name=p_tg_name and b.owner=p_db_name order by 1;

    -- 使用异常处理
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;

    -- 为db指定缺省的 tg
    SET @alter_sql = CONCAT('alter database ', p_db_name, ' default tablegroup=', p_tg_name);
    PREPARE stmt FROM @alter_sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    -- 获取总表数
    SELECT COUNT(*) INTO v_total_tables from (select a.table_name FROM information_schema.TABLES a WHERE a.TABLE_SCHEMA=p_db_name AND a.TABLE_TYPE='BASE TABLE' minus select b.table_name from oceanbase.dba_ob_tablegroup_tables b where b.tablegroup_name=p_tg_name and b.owner=p_db_name);

    SELECT CONCAT('数据库 [', p_db_name, '] 中共有 ', v_total_tables, ' 张表需要绑定到表组') AS '开始信息';

    -- 打开游标
    OPEN table_cursor;

    -- 开始循环处理表
    batch_loop: LOOP
        FETCH table_cursor INTO v_table_name;
        IF v_done THEN
            -- 处理最后一批不足 p_batch_size 张的表
            IF v_counter > 0 THEN
                SELECT CONCAT('数据库 [', @p_db_name, '] : 执行第 ', v_current_batch, ' 批，包含 ', v_counter, ' 张表') AS '批次信息';

                -- 移除最后一个逗号
                SET v_sql_batch = TRIM(TRAILING ',' FROM v_sql_batch);

                -- 构建完整的 ALTER TABLEGROUP 语句
                SET @batch_sql = CONCAT('ALTER TABLEGROUP ', p_tg_name, ' ADD ', v_sql_batch);

                -- 显示要执行的 SQL（调试用）
                SELECT @batch_sql AS '最后一个批次执行的 SQL';

                -- 执行 SQL（取消注释以实际执行）
                PREPARE stmt FROM @batch_sql;
                EXECUTE stmt;
                DEALLOCATE PREPARE stmt;

                SELECT CONCAT('数据库 [', @p_db_name, '] : 第 ', v_current_batch, ' 批执行完成') AS '批次完成';
            END IF;
            LEAVE batch_loop;
        END IF;

        -- 增加计数器
        SET v_counter = v_counter + 1;

        -- 将表添加到当前批次的 SQL 中
        SET v_sql_batch = CONCAT(v_sql_batch, p_db_name, '.', v_table_name, ',');

        -- 如果达到批次大小，执行当前批次
        IF v_counter >= p_batch_size THEN
            SELECT CONCAT('数据库 [', @p_db_name, '] : 执行第 ', v_current_batch, ' 批，包含 ', v_counter, ' 张表') AS '批次信息';

            -- 移除最后一个逗号
            SET v_sql_batch = TRIM(TRAILING ',' FROM v_sql_batch);

            -- 构建完整的 ALTER TABLEGROUP 语句
            SET @batch_sql = CONCAT('ALTER TABLEGROUP ', p_tg_name, ' ADD ', v_sql_batch);

            -- 显示要执行的 SQL（调试用）
            SELECT @batch_sql AS '正常批次执行的 SQL';

            -- 执行 SQL（取消注释以实际执行）
            PREPARE stmt FROM @batch_sql;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;

            SELECT CONCAT('数据库 [', @p_db_name, '] : 第 ', v_current_batch, ' 批执行完成') AS '批次完成';

            -- 重置变量，准备下一批次
            SET v_counter = 0;
            SET v_sql_batch = '';
            SET v_current_batch = v_current_batch + 1;
        END IF;
    END LOOP;

    -- 关闭游标
    CLOSE table_cursor;

    -- 计算完成的批次和剩余表数
    SET @completed_batches = v_current_batch - 1;
    SET @remaining_tables = v_total_tables - (@completed_batches * p_batch_size);

    SELECT CONCAT('数据库 [', p_db_name, '] : 所有批次执行完成! 共执行 ', @completed_batches, ' 个完整批次(', p_batch_size, ' 张表/每批次)', CASE WHEN @remaining_tables > 0 THEN CONCAT(' 和 1 个部分批次(', @remaining_tables, ' 张表)') ELSE '' END) AS '最终结果';
END;
$$
DELIMITER ;

调用示例:

call test.add_db_to_tg_in_batch('sbtest', 'tg1', 500);
call test.add_db_to_tg_in_batch('db50', 'tg1', 500);

add_dblist_to_tg_in_batch

为提高执行效率和用户接口的易用性，支持将一批 dblist 里面的表按某个固定的 batch size 成批加入到 tg 中去:

DELIMITER $$
drop procedure if exists test.add_dblist_to_tg_in_batch;
CREATE PROCEDURE test.add_dblist_to_tg_in_batch(
    IN p_dblist text,
    IN p_tg_name VARCHAR(64),
    IN p_batch_size int
)
BEGIN
    SET @remaining_dbs = p_dblist;

    WHILE LENGTH(@remaining_dbs) > 0 DO
        -- 获取下一个数据库名
        SET @comma_pos = LOCATE(',', @remaining_dbs);

        IF @comma_pos = 0 THEN
            SET @current_db = TRIM(@remaining_dbs);
            SET @remaining_dbs = '';
        ELSE
            SET @current_db = TRIM(SUBSTRING(@remaining_dbs, 1, @comma_pos - 1));
            SET @remaining_dbs = SUBSTRING(@remaining_dbs, @comma_pos + 1);
        END IF;

        -- 检查数据库是否存在
        SET @db_exists = 0;
        SELECT COUNT(*) INTO @db_exists FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = @current_db;

        -- 如果数据库存在，则开始添加该数据库的基表到表组
        IF @db_exists > 0 THEN
          begin
            DECLARE v_table_name VARCHAR(64);
            DECLARE v_counter INT DEFAULT 0;
            DECLARE v_total_tables INT DEFAULT 0;
            DECLARE v_current_batch INT DEFAULT 1;
            DECLARE v_sql_batch TEXT DEFAULT '';
            DECLARE v_done INT DEFAULT FALSE;

            -- 声明游标来获取所有需要加入表组的表
            DECLARE table_cursor CURSOR FOR SELECT a.TABLE_NAME FROM information_schema.TABLES a WHERE a.TABLE_SCHEMA=@current_db AND a.TABLE_TYPE='BASE TABLE' minus select b.table_name from oceanbase.dba_ob_tablegroup_tables b where b.tablegroup_name=p_tg_name and b.owner=@current_db order by 1;

            -- 使用异常处理
            DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;

            -- 为 db 指定缺省的 tg
            SET @alter_sql = CONCAT('alter database ', @current_db, ' default tablegroup=', p_tg_name);
            PREPARE stmt FROM @alter_sql;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;

            -- 获取总表数
            SELECT COUNT(*) INTO v_total_tables from (select a.table_name FROM information_schema.TABLES a WHERE a.TABLE_SCHEMA=@current_db AND a.TABLE_TYPE='BASE TABLE' minus select b.table_name from oceanbase.dba_ob_tablegroup_tables b where b.tablegroup_name=p_tg_name and b.owner=@current_db);

            SELECT CONCAT('数据库 [', @current_db, '] 中共有 ', v_total_tables, ' 张表需要绑定到表组') AS '开始信息';

            -- 打开游标
            OPEN table_cursor;

            -- 开始循环处理表
            batch_loop: LOOP
                FETCH table_cursor INTO v_table_name;
                IF v_done THEN
                    -- 处理最后一批不足 p_batch_size 张的表
                    IF v_counter > 0 THEN
                        SELECT CONCAT('数据库 [', @current_db, '] : 执行第 ', v_current_batch, ' 批，包含 ', v_counter, ' 张表') AS '批次信息';

                        -- 移除最后一个逗号
                        SET v_sql_batch = TRIM(TRAILING ',' FROM v_sql_batch);

                        -- 构建完整的 ALTER TABLEGROUP 语句
                        SET @batch_sql = CONCAT('ALTER TABLEGROUP ', p_tg_name, ' ADD ', v_sql_batch);

                        -- 显示要执行的 SQL（调试用）
                        SELECT @batch_sql AS '最后一个批次执行的 SQL';

                        -- 执行 SQL（取消注释以实际执行）
                        PREPARE stmt FROM @batch_sql;
                        EXECUTE stmt;
                        DEALLOCATE PREPARE stmt;

                        SELECT CONCAT('数据库 [', @current_db, '] : 第 ', v_current_batch, ' 批执行完成') AS '批次完成';
                    END IF;
                    LEAVE batch_loop;
                END IF;

                -- 增加计数器
                SET v_counter = v_counter + 1;

                -- 将表添加到当前批次的 SQL 中
                SET v_sql_batch = CONCAT(v_sql_batch, @current_db, '.', v_table_name, ',');

                -- 如果达到批次大小，执行当前批次
                IF v_counter >= p_batch_size THEN
                SELECT CONCAT('数据库 [', @current_db, '] : 执行第 ', v_current_batch, ' 批，包含 ', v_counter, ' 张表') AS '批次信息';

                    -- 移除最后一个逗号
                    SET v_sql_batch = TRIM(TRAILING ',' FROM v_sql_batch);

                    -- 构建完整的 ALTER TABLEGROUP 语句
                    SET @batch_sql = CONCAT('ALTER TABLEGROUP ', p_tg_name, ' ADD ', v_sql_batch);

                    -- 显示要执行的 SQL（调试用）
                    SELECT @batch_sql AS '正常批次执行的 SQL';

                    -- 执行 SQL（取消注释以实际执行）
                    PREPARE stmt FROM @batch_sql;
                    EXECUTE stmt;
                    
                    DEALLOCATE PREPARE stmt;

                    SELECT CONCAT('数据库 [', @current_db, '] : 第 ', v_current_batch, ' 批执行完成') AS '批次完成';

                    -- 重置变量，准备下一批次
                    SET v_counter = 0;
                    SET v_sql_batch = '';
                    SET v_current_batch = v_current_batch + 1;
                END IF;
            END LOOP;

            -- 关闭游标
            CLOSE table_cursor;

            -- 计算完成的批次和剩余表数
            SET @completed_batches = v_current_batch - 1;
            SET @remaining_tables = v_total_tables - (@completed_batches * p_batch_size);

           SELECT CONCAT('数据库 [', @current_db, '] : 所有批次执行完成! 共执行 ', @completed_batches, ' 个完整批次(', p_batch_size, ' 张表/每批次)', CASE WHEN @remaining_tables > 0 THEN CONCAT(' 和 1 个部分批次(', @remaining_tables, ' 张表)') ELSE '' END) AS '最终结果';
          end;
        ELSE
            -- 可选：记录不存在的数据库警告
            SELECT CONCAT('警告: 数据库 [', @current_db, '] 不存在，已跳过') AS warning;
        END IF;

    END WHILE;
END;
$$
DELIMITER ;

调用示例:

call test.add_dblist_to_tg_in_batch('db20', 'tg1', 500);
call test.add_dblist_to_tg_in_batch('db21,db22,db23', 'tg1', 500);
call test.add_dblist_to_tg_in_batch('  db24 ,   db25,   db26   ', 'tg1', 500);

move_db_out_of_tg

该存储过程用于将单个 db 里面的表一张一张地串行从 tg 中移出来：

说明

目前 OceanBase 数据库只支持将表批量加入到表组，不支持将表批量移出表组，即没有类似 ALTER TABLEGROUP tablegroup_name ADD [TABLE] table_name [, table_name...]; 这样的批量移出表组的语法。

DELIMITER $$
drop procedure if exists test.move_db_out_of_tg;
CREATE PROCEDURE test.move_db_out_of_tg(p_db_name VARCHAR(100), p_tg_name VARCHAR(100))
BEGIN
    DECLARE v_done INT DEFAULT FALSE;
    DECLARE v_table_name VARCHAR(100);

    -- 声明游标来获取所有需要移出表组的表
    DECLARE table_cursor CURSOR FOR SELECT a.TABLE_NAME FROM oceanbase.dba_ob_tablegroup_tables a WHERE a.tablegroup_name=p_tg_name AND a.owner=p_db_name order by 1;

    -- 声明未找到记录时的处理程序
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;

    -- 将db的缺省 tg 设置为空
    SET @alter_sql = CONCAT('alter database ', p_db_name, ' default tablegroup=null');
    PREPARE stmt FROM @alter_sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    -- 打开游标
    OPEN table_cursor;

    -- 开始循环处理每张表
    FETCH table_cursor INTO v_table_name;
    WHILE v_done<>TRUE DO
        -- 构建 ALTER TABLEGROUP 语句
        SET @alter_sql = CONCAT("ALTER table ", p_db_name, ".", v_table_name, " tablegroup ''");
        select @alter_sql as '执行的 SQL';

        -- 准备并执行动态 SQL
        PREPARE stmt FROM @alter_sql;

        -- 执行 ALTER 语句
        EXECUTE stmt;

        DEALLOCATE PREPARE stmt;
        FETCH table_cursor INTO v_table_name;
    END WHILE;

    -- 关闭游标
    CLOSE table_cursor;
END;
$$
DELIMITER ;

调用示例：

call test.move_db_out_of_tg('sbtest', 'tg1');
call test.move_db_out_of_tg('db1', 'tg1');

move_dblist_out_of_tg

为提高用户接口的易用性，支持将一批 dblist 里面的表一张一张地串行从 tg 中移出来：

DELIMITER $$
drop procedure if exists test.move_dblist_out_of_tg;
CREATE PROCEDURE test.move_dblist_out_of_tg(
    IN p_dblist text,
    IN p_tg_name VARCHAR(100)
)
BEGIN
    SET @remaining_dbs = p_dblist;

    WHILE LENGTH(@remaining_dbs) > 0 DO
        -- 获取下一个数据库名
        SET @comma_pos = LOCATE(',', @remaining_dbs);

        IF @comma_pos = 0 THEN
            SET @current_db = TRIM(@remaining_dbs);
            SET @remaining_dbs = '';
        ELSE
            SET @current_db = TRIM(SUBSTRING(@remaining_dbs, 1, @comma_pos - 1));
            SET @remaining_dbs = SUBSTRING(@remaining_dbs, @comma_pos + 1);
        END IF;

        -- 检查数据库是否存在
        SET @db_exists = 0;
        SELECT COUNT(*) INTO @db_exists FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = @current_db;

        -- 如果数据库存在，则开始将该数据库中的基表移出表组
        IF @db_exists > 0 THEN
        begin
            DECLARE v_done INT DEFAULT FALSE;
            DECLARE v_table_name VARCHAR(100);

             -- 声明游标来获取所有需要移出表组的表
             DECLARE table_cursor CURSOR FOR SELECT a.TABLE_NAME FROM oceanbase.dba_ob_tablegroup_tables a WHERE a.tablegroup_name=p_tg_name AND a.owner=@current_db order by 1;

             -- 声明未找到记录时的处理程序
             DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;

             -- 将db的缺省 tg 设置为空
             SET @alter_sql = CONCAT('alter database ', @current_db, ' default tablegroup=null');
             PREPARE stmt FROM @alter_sql;
             EXECUTE stmt;
             DEALLOCATE PREPARE stmt;

             -- 打开游标
             OPEN table_cursor;

             -- 开始循环处理每张表
             FETCH table_cursor INTO v_table_name;
             WHILE v_done<>TRUE DO
                 -- 构建 ALTER TABLEGROUP 语句
                 SET @alter_sql = CONCAT("ALTER table ", @current_db, ".", v_table_name, " tablegroup ''");
                 select @alter_sql as '执行的 SQL';

                 -- 准备并执行动态 SQL
                 PREPARE stmt FROM @alter_sql;

                 -- 执行 ALTER 语句
                 EXECUTE stmt;

                 DEALLOCATE PREPARE stmt;
                 FETCH table_cursor INTO v_table_name;
             END WHILE;

             -- 关闭游标
             CLOSE table_cursor;
           end;
        ELSE
            -- 可选：记录不存在的数据库警告
            SELECT CONCAT('警告: 数据库 [', @current_db, '] 不存在，已跳过') AS warning;
        END IF;

    END WHILE;
END;
$$
DELIMITER ;

调用示例：

call test.move_dblist_out_of_tg('db32', 'tg1');
call test.move_dblist_out_of_tg('db30,db31', 'tg1');
call test.move_dblist_out_of_tg('db40,db41,db42', 'tg1');

注意

如果通过 alter table/alter tablegroup 将表加入表组的话，该表或者它的分区不会立刻自动重新均衡好，还依赖于当前租户级配置项 enable_rebalance、enable_transfer 和 partition_balance_schedule_interval 的设置。即：enable_rebalance 和 enable_transfer 需要设置为 true，且需要等到下一个 partition_balance_schedule_interval 调度周期到了才会触发分区的均衡。
partition_balance_schedule_interval 和 enable_transfer 这二个参数属于 V4.x 版本。
因为分区的均衡和 tablet 的 transfer 可能会对系统的 CPU、Memory 以及 IO 带来压力，因此，请尽量控制在业务低峰期再打开相关开关，触发分区自动均衡的调度。

适用版本

OceanBase 数据库 V2.x、V3.x、V4.x 版本的 MySQL 模式租户。

关于 DBMS_OUTPUT 包 BUFFER_SIZE 缓冲区的说明

OceanBase 数据库 Oracle 租户 PL 调用执行报错 -5838，原生 Oracle 不报错的原因和解决方法

OceanBase 数据库