mysql search replace string all tables

If you find this post very useful, please give me comments below.

Thanks.

DROP PROCEDURE IF EXISTS string_replace;

DELIMITER $$
CREATE PROCEDURE string_replace(IN dbname CHAR(250), IN oldStr CHAR(250), IN newStr CHAR(250))
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE tblname CHAR(50);
    DECLARE currentFieldName CHAR(50);
    DECLARE cur1 CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_schema = dbname;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    SET foreign_key_checks = 0;

    OPEN cur1;
    read_loop: LOOP
        FETCH cur1 INTO tblname;
        IF done THEN
            LEAVE read_loop;
        END IF;
        SET @database_name = dbname;
        SET @table_name = tblname;
        SET @strFields = 	(SELECT GROUP_CONCAT(column_name) AS cName FROM information_schema.columns WHERE TABLE_SCHEMA = @database_name AND table_name = @table_name);
        SET @lStr = LENGTH(@strFields);

		  SET @texti = TRIM(BOTH ',' FROM @strFields);
		  SET @strLen = 0;
		  SET @i = 1;
		  WHILE @strLen < LENGTH(@texti)  DO
		    SET @str = SUBSTRING_INDEX(@texti, ',', @i);
		    SET @strLen = LENGTH(@str);
		    SET @i = @i + 1;
			 #INSERT INTO test (name) VALUES (CONCAT(tblname,'-',@strFields));
			 #INSERT INTO tbl (name) VALUES (@str);
			SET currentFieldName = SUBSTRING_INDEX(@str, ',', -1);
			SET @updateQuery = CONCAT('UPDATE ',tblname, ' set ', currentFieldName, ' = replace( ',currentFieldName, " , '" , oldStr, "' , '" , newStr, "' )");
			PREPARE stmt3 FROM @updateQuery;
			EXECUTE stmt3;
			DEALLOCATE PREPARE stmt3;
		  END WHILE;
    END LOOP;
    CLOSE cur1;
    SET foreign_key_checks = 1;
END$$

CALL string_replace('database_name','old_string', 'new_string');

Download text file here

Tags:

Speak Your Mind

Tell us what you're thinking...
and oh, if you want a pic to show with your comment, go get a gravatar!

You must be logged in to post a comment.