MySQL Search and Replace Stored Procedure
I frequently need to do global search and replaces across a single database, so I wrote a stored procedure to do this for me:
####
# CALL THIS SP USING THE FOLLOWING SYNTAX
# sp_DbSearchReplace('oldText','NewText');
####
DELIMITER $$
DROP PROCEDURE IF EXISTS `YOUR_DB_NAME_HERE`.`sp_DbSearchReplace` $$
CREATE PROCEDURE `YOUR_DB_NAME_HERE`.`sp_DbSearchReplace` (IN oldString VARCHAR(255), IN newString VARCHAR(255))
BEGIN
# regular vars
DECLARE nDone INT;
DECLARE sTable VARCHAR(64);
DECLARE sColumn VARCHAR(64);
#cursors
DECLARE cTables CURSOR FOR
SELECT TABLE_NAME, COLUMN_NAME
FROM information_schema.COLUMNS
WHERE information_schema.COLUMNS.TABLE_SCHEMA = 'YOUR_DB_NAME_HERE';
#handlers
DECLARE CONTINUE HANDLER FOR NOT FOUND SET nDone = 1;
OPEN cTables;
REPEAT
FETCH cTables INTO sTable, sColumn;
SET @sQuery = CONCAT('UPDATE ',sTable ,' SET ' ,sColumn,
' = REPLACE(',sColumn,',\'',oldString,'\',\'',newString,'\');');
PREPARE stmt FROM @sQuery;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
UNTIL nDone = 1
END REPEAT;
CLOSE cTables;
END $$
DELIMITER ;
- Log in to post comments