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
| --------------
DROP PROCEDURE IF EXISTS changer
--------------
--------------
CREATE PROCEDURE changer(_old VARCHAR(255),
_new VARCHAR(255))
DETERMINISTIC
NO SQL
BEGIN
DECLARE _fin INTEGER DEFAULT 1;
DECLARE _table VARCHAR(255);
DECLARE _colonne VARCHAR(255);
DECLARE curseur CURSOR FOR
SELECT table_name, column_name
FROM information_schema.columns
WHERE table_schema IN ('base')
AND data_type IN ('varchar','char','tinytext','mediumtext','text','longtext');
DECLARE CONTINUE HANDLER FOR NOT FOUND SET _fin=0;
OPEN curseur;
FETCH curseur INTO _table,_colonne;
WHILE _FIN DO
SET @s = concat('update ',_table,' SET ',_colonne,' = replace(',_colonne,',\'',_old,'\',\'',_new,'\') where ',_colonne,' like \'%',_old,'%\';');
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
FETCH curseur INTO _table,_colonne;
END WHILE;
CLOSE curseur;
END
--------------
--------------
CALL changer('un','deux')
--------------
Appuyez sur une touche pour continuer... |
Partager