Say, in MySQL, if you would like to find the string 'http://www.guiguan.net' in a database and replace all of its occurrences (from all columns/fields of all tables in the database) with another string 'https://www.guiguan.net', because that you just moved to a more secure world, then you can achieve this purely using native SQL statements in MySQL.
First, launch your favourite MySQL client, such as PHPMyAdmin, Adminer or just CLI, select your target database and execute the following SQL statements to define your global_replace procedure:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
# must change statement delimiter if we are using interactive shell DELIMITER ;; CREATE PROCEDURE `global_replace` (IN `from_string` varchar(255), IN `to_string` varchar(255)) BEGIN # declare variable DECLARE db_name, statement_string VARCHAR(255); DECLARE done INT DEFAULT FALSE; # declare cursor to receive subquery results. The subquery is # generating the UPDATE statements that will be executed next # for the actual string replacement operation to take place. # We make use of the meta database `information_schema` to # find all our needed table columns DECLARE cur CURSOR FOR (SELECT CONCAT('UPDATE `', table_name, '` SET `', column_name, '` = replace(`', column_name,'`, \'', from_string, '\', \'', to_string, '\');') FROM information_schema.columns WHERE table_schema = DATABASE()); # declare handle DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; # open cursor OPEN cur; # starts the loop the_loop: LOOP # get the values of each column into our variables FETCH cur INTO statement_string; IF done THEN LEAVE the_loop; END IF; # carry out main loop logic # we must store this local variable to a user defined # variable, because the PREPARE only accepts user # defined variable SET @tmp = statement_string; PREPARE statement FROM @tmp; EXECUTE statement; DEALLOCATE PREPARE statement; END LOOP the_loop; CLOSE cur; END;; DELIMITER ; |
Then, you just call the stored procedure:
|
1 |
CALL global_replace('http://www.guiguan.net', 'https://www.guiguan.net'); |
to replace all occurrences of 'http://www.guiguan.net' across your database with the string 'https://www.guiguan.net'. Done.
In the future, you can just call global_replace again to replace more strings. If you would like to remove the stored procedure, then just:
|
1 |
DROP PROCEDURE `global_replace`; |
If you wish, you can put all the statements together in a single execution.