MySQL使用游标实现如果表字段不存在就添加字段

检查数据库中所有表 SET @database = 'hainanhd_0'; SELECT TABLE_NAME, TABLE_SCHEMA FROM information_schema.`TABLES` WHERE TABLE_SCHEMA = @database AND TABLE_NAME like '%_component_draw_user_win_%'; 检查表字段 SET @database = 'hainanhd_0', @field = 'order_id'; SELECT COLUMN_NAME FROM information_schema.`COLUMNS` WHERE TABLE_SCHEMA = 'hainanhd_0' AND TABLE_NAME = 'zbunch_component_draw_user_win_10191' AND COLUMN_NAME = @field; 创建存储过程 CREATE PROCEDURE alter_draw_tables(IN db_name VARCHAR(100)) BEGIN DECLARE done INT DEFAULT 0; END; 使用游标遍历结果集 CREATE PROCEDURE alter_draw_tables(IN db_name VARCHAR(100)) BEGIN DECLARE tb_name varchar(100); DECLARE done INT DEFAULT 0; # 获取结果集 DECLARE tb_cursor CURSOR FOR SELECT TABLE_NAME, TABLE_SCHEMA FROM information_schema.`TABLES` WHERE TABLE_SCHEMA = db_name AND TABLE_NAME like '%_component_draw_user_win_%'; # 处理"No data - zero rows fetched, selected, or processed"异常 DECLARE CONTINUE HANDLER FOR NOT found SET done = 1; OPEN tb_cursor; REPEAT # 遍历结果集 FETCH tb_cursor INTO tb_name, db_name; IF done != 1 THEN SELECT tb_name, db_name; # for debug END IF; UNTIL done = 1 END REPEAT; CLOSE tb_cursor; END; 执行存储过程 CALL alter_draw_tables('hainanhd_0'); 删除存储过程 DROP PROCEDURE IF EXISTS alter_draw_tables;