Untitled

Find/replace in MySQL

sites

    site_id

    url

       

UPDATE sites SET url=REPLACE(url, 'findUrl.com', 'replaceUrl.com');

       

DELIMITER $$

 

CREATE PROCEDURE `find_replace_in_all_tables`()

BEGIN

    DECLARE done INT DEFAULT 0;

    DECLARE TABLE_NAME CHAR(255);

    DECLARE COLUMN_NAME CHAR(255);

 

    DECLARE tables CURSOR for

        SELECT table_name, column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE();

 

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

 

    OPEN tables;

    WHILE done = 0 DO

        FETCH NEXT FROM tables INTO TABLE_NAME, COLUMN_NAME;

 

        IF done = 0 THEN

            SET @SQL_TEXT = CONCAT("UPDATE `", TABLE_NAME, "` SET `", COLUMN_NAME, "`=REPLACE(`", COLUMN_NAME, "`, 'findUrl.com', 'replaceUrl.com');");

 

            PREPARE statement FROM @SQL_TEXT;

            EXECUTE statement;

            DEALLOCATE PREPARE statement;

        END IF;

    END WHILE;

 

    CLOSE tables;

END