如何为StarRocks/MySQL数据库所有基表执行ALTER TABLE设置属性并验证

一、生成批量ALTER TABLE语句 #

基于你的查询思路优化，生成仅针对基表的批量修改语句，避免误操作视图等对象：

SELECT 
    GROUP_CONCAT(
        CONCAT('ALTER TABLE `', table_name, '` SET ("write_quorum" = "ONE")') 
    SEPARATOR '; '
) AS full_query
FROM information_schema.tables
WHERE table_schema = 'db_name' 
  AND table_type = 'BASE TABLE';

执行该查询后，将结果中的full_query内容复制出来直接运行，即可批量修改所有目标基表。用反引号包裹表名，能避免表名含特殊字符时出错。

二、用存储过程自动执行（可选） #

如果表数量较多，不想手动复制执行，可通过存储过程循环处理：

MySQL版本 #

DELIMITER //
CREATE PROCEDURE AlterAllBaseTables()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE tbl_name VARCHAR(255);
    DECLARE cur CURSOR FOR 
        SELECT table_name 
        FROM information_schema.tables 
        WHERE table_schema = 'db_name' 
          AND table_type = 'BASE TABLE';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO tbl_name;
        IF done THEN
            LEAVE read_loop;
        END IF;
        SET @sql = CONCAT('ALTER TABLE `', tbl_name, '` SET ("write_quorum" = "ONE")');
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP;
    CLOSE cur;
END //
DELIMITER ;

-- 调用存储过程
CALL AlterAllBaseTables();

StarRocks版本 #

StarRocks的存储过程语法略有差异，参考如下：

CREATE OR REPLACE PROCEDURE AlterAllBaseTables()
BEGIN
    DECLARE tbl_name VARCHAR(255);
    DECLARE cur CURSOR FOR 
        SELECT table_name 
        FROM information_schema.tables 
        WHERE table_schema = 'db_name' 
          AND table_type = 'BASE TABLE';
    OPEN cur;
    LOOP
        FETCH cur INTO tbl_name;
        IF cur%NOTFOUND THEN
            LEAVE;
        END IF;
        EXECUTE IMMEDIATE CONCAT('ALTER TABLE `', tbl_name, '` SET ("write_quorum" = "ONE")');
    END LOOP;
    CLOSE cur;
END;

-- 调用存储过程
CALL AlterAllBaseTables();

三、验证修改结果 #

StarRocks验证语句 #

查询系统表确认属性设置是否生效：

SELECT 
    table_name,
    property_name,
    property_value
FROM information_schema.table_properties
WHERE table_schema = 'db_name'
  AND property_name = 'write_quorum'
ORDER BY table_name;

MySQL验证语句 #

通过查询表选项信息确认（不同存储引擎可能略有差异）：

SELECT 
    table_name,
    option_value
FROM information_schema.table_options
WHERE table_schema = 'db_name'
  AND option_name = 'write_quorum' -- 部分属性键名可能不同，需根据实际调整
ORDER BY table_name;

也可生成批量查看建表语句的查询，直接确认属性：

SELECT CONCAT('SHOW CREATE TABLE `', table_name, '`;') AS check_stmt
FROM information_schema.tables
WHERE table_schema = 'db_name'
  AND table_type = 'BASE TABLE';

内容的提问来源于stack exchange，提问作者Yerrash