mysql初始化表格的存储过程

创建一个存储过程用于初始化指定表中所有字段为 0.00

CREATE PROCEDURE initialize_table_with_zeros(tableName VARCHAR(255))
BEGIN

-- 存储过程：initialize_table_with_zeros
-- 描述：将指定表中的所有数值类型字段设置为 0
-- 参数：
--     tableName - 目标表名
-- 如何使用：
-- 	   执行以下sql语句即可
--     CALL initialize_table_with_zeros('your_table_name');

    DECLARE done INT DEFAULT 0;
    DECLARE col_name VARCHAR(255);
    DECLARE col_names TEXT;
    DECLARE _value TEXT;
    DECLARE cur CURSOR FOR
        SELECT column_name
        FROM information_schema.columns
        WHERE table_schema = 'your_database_name' AND table_name = tableName;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    SET col_names = '';

    SET _value = '';

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO col_name;
        IF done THEN
            LEAVE read_loop;
        END IF;
        SET col_names = CONCAT(col_names, col_name, ', ');
        SET _value = CONCAT(_value, '''0.00''', ', ');
    END LOOP;
    CLOSE cur;

    SELECT col_names;

    SET col_names = LEFT(col_names, LENGTH(col_names) - 2);  -- 去掉最后一个逗号和空格
    SET _value = LEFT(_value, LENGTH(_value) - 2);  -- 去掉最后一个逗号和空格

    SET @sql = CONCAT('INSERT INTO ', tableName, ' (', col_names, ') VALUES (', _value,');');

    SELECT @sql;  -- 打印 SQL 语句
--     执行sql部分
--     PREPARE stmt FROM @sql;
--     EXECUTE stmt;
--     DEALLOCATE PREPARE stmt;
END


-- 执行语句
CALL initialize_table_with_zeros('your_table_name');

-- 执行后看控制台打印内容