DELIMITER $ DROP PROCEDURE IF EXISTS reset_sync_status $ CREATE PROCEDURE reset_sync_status ( IN p_db_name VARCHAR(30) ) BEGIN DECLARE sql_str VARCHAR(200); DECLARE table_name VARCHAR(60); DECLARE done INT DEFAULT FALSE; DECLARE field_name VARCHAR(20) DEFAULT 'sync_status'; DECLARE def_value VARCHAR(10) DEFAULT '0'; DECLARE cur CURSOR FOR SELECT a.table_name FROM information_schema.TABLES a INNER JOIN information_schema.COLUMNS b ON a.table_name = b.table_name WHERE a.table_schema = p_db_name AND b.column_name = field_name; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; OPEN cur; read_loop:LOOP FETCH cur INTO table_name; SET @sql_str = CONCAT('UPDATE ',table_name,' set ',field_name,'=',def_value,';'); PREPARE stmt FROM @sql_str; EXECUTE stmt; IF done THEN LEAVE read_loop; END IF; END LOOP; CLOSE cur; END $ CALL reset_sync_status('chm_baseinfo') $ DROP PROCEDURE reset_sync_status $ DELIMITER ;
mysql使用游标锁表_数据库游标使用mysql(全表操作)
最新推荐文章于 2021-11-15 16:05:07 发布