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.

Answer 1

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.

Answer 2

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.

Answer 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