How can I replace a string in a MySQL database for all tables in all fields in all rows?

Question

I have a Moodle installation that I migrated to another server and I need to change several references to the old domain.

How can I replace a string for another string in MySQL for a given database searching all tables, all fields, and all rows?

I don't need to change the field names, just the values.

Related: How can I use mySQL replace() to replace strings in multiple records?

But the marked as answer solution implies I strongly type the table name and I need to fire this into an entire database, not manually work on each table running the script N times.

You rather create a new table then...drop that old one.. as you are saying all the tables in all fields in all rows... that's literally an entier database isn't it? — bonCodigo
– bonCodigo, Commented Jan 9, 2013 at 15:14
I'd try writing a query that generates the script that would do it one query for a table... — ppeterka
– ppeterka, Commented Jan 9, 2013 at 15:14
@ppeterka: Meta, I like it. But I'm hoping MySQL has something built in. Who knows these days, with Postgres having array fields and such! — sergserg
– sergserg, Commented Jan 9, 2013 at 15:16
@bonCodigo: I want to keep content and structure. Just change the values - by replacing value X with value Y. — sergserg
– sergserg, Commented Jan 9, 2013 at 15:17

Dariusz · Accepted Answer · 2013-01-09 15:17:16Z

13

This may seem a bit ... ugly. But maybe simply dump the database to a sql/txt file, replace all strings and recreate the database using the modified dump.

answered Jan 9, 2013 at 15:17

Dariusz

22.5k9 gold badges81 silver badges116 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

sergserg Over a year ago

I have that as last resort, doing a mysqldump and import will take several hours as the database is about 170GB big.

sergserg Over a year ago

Do you know what command on Linux I should look into for replacing text inside a .sql file directly from terminal?

Dariusz Over a year ago

What makes you think that doing that using built-in functions will be faster? Full text search is what it is whatever the tool used. I daresay using linux sed on text file will be faster than any built-in tool. //edit: you posted after I wrote - 'sed' is what you are looking for

sergserg Over a year ago

sed 's/old/new/g' input.txt' backup.sql - Would something like this work? The documentation shows this example with the s and g in the front and back of the new and old strings, but doesn´t mention what they do?

Tom · Accepted Answer · 2013-01-09 15:49:28Z

5

You could run the following code to create all the udpate statements you would need to run to do your updates. It would update every field in every table within your database. You would need to run this code, and copy the results and run them.

WARNING - Be sure to test this in a test environment. You don't want any unpleasant surprises. Modify as needed.

SELECT concat('UPDATE ', TABLE_NAME, ' SET ', COLUMN_NAME, ' = REPLACE(', COLUMN_NAME, ', ''STRING_TO_REPLACE'', ''REPLACE_STRING'')')
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE TABLE_SCHEMA = 'YOUR_DATABASE_NAME'
      AND DATA_TYPE IN ('char', 'varchar')
;

I limited this to only char and varchar fields. You may need to add additional data types.

edited Jan 9, 2013 at 15:49

answered Jan 9, 2013 at 15:31

Tom

6,6633 gold badges24 silver badges42 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 12:19:14Z

3

I would consider querying INFORMATION_SCHEMA.COLUMNS and dynamically searching the columns and tables. Try something like creating a cursor of all the columns and tables in the db:

DECLARE col_names CURSOR FOR
SELECT column_name, table_name
FROM INFORMATION_SCHEMA.COLUMNS

Then iterate through each of the columns in each of the tables and run dynamic/prepared sql to update where the string exists.

Here are a couple of good posts to get you in the right direction:

https://stackoverflow.com/a/4951354/1073631

https://stackoverflow.com/a/5728155/1073631

edited May 23, 2017 at 12:19

CommunityBot

11 silver badge

answered Jan 9, 2013 at 15:24

sgeddes

62.9k7 gold badges67 silver badges85 bronze badges

Comments

marc_s · Accepted Answer · 2024-11-02 21:29:02Z

I was able to take the two suggestions here and combine them into a single stored procedure that I've been personally using and wanted to share, hope it helps someone else!

DELIMITER $$

DROP PROCEDURE IF EXISTS ReplaceValueInStringColumns$$

CREATE PROCEDURE ReplaceValueInStringColumns
    (IN db_name VARCHAR(255), 
     IN old_value VARCHAR(255), 
     IN new_value VARCHAR(255))
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE col_name VARCHAR(255);
DECLARE tbl_name VARCHAR(255);
DECLARE col_type VARCHAR(255);
DECLARE sql_query TEXT;

DECLARE col_names CURSOR FOR
SELECT column_name, table_name, data_type
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_schema = db_name
AND data_type IN ('char', 'varchar', 'text', 'tinytext', 'mediumtext', 'longtext');

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN col_names;

read_loop: LOOP
FETCH col_names INTO col_name, tbl_name, col_type;
IF done THEN
LEAVE read_loop;
END IF;

-- Output the table name being updated
SELECT CONCAT('Updating table: ', tbl_name) AS message;

SET @sql_query = CONCAT('UPDATE ', tbl_name, ' SET ', col_name, ' = REPLACE(', col_name, ', "', old_value, '", "', new_value, '");');
PREPARE stmt FROM @sql_query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END LOOP;

CLOSE col_names;
END$$

DELIMITER ;

And then when you want to use it, you just call the stored procedure:

CALL ReplaceValueInStringColumns('your_database_name', 'old_value', 'new_value');

Collectives™ on Stack Overflow

How can I replace a string in a MySQL database for all tables in all fields in all rows?

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related