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.

Kittie

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? – Babbittry

I'd try writing a query that generates the script that would do it one query for a table... – Unclear

@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! – Kittie

@Babbittry he wants to keep the content... – Unclear

@bonCodigo: I want to keep content and structure. Just change the values - by replacing value X with value Y. – Kittie

Answer 1

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://mcmap.net/q/747149/-mysql-iterate-through-column-names

https://mcmap.net/q/272103/-how-to-have-dynamic-sql-in-mysql-stored-procedure

Vinegarette

Answer 2

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.

Manis

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

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

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 – Manis

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? – Kittie

Answer 3

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.

Albania