如何在MySQL数据库中替换所有行中所有字段中的所有表？

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

我有一个Moodle安装，我迁移到另一台服务器，我需要更改几个旧域的引用。

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

对于搜索所有表，所有字段和所有行的给定数据库，如何在MySQL中替换另一个字符串的字符串？

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

我不需要更改字段名称，只需更改值。

相关：如何使用mySQL replace（）替换多个记录中的字符串？

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.

但标记为答案解决方案意味着我强烈键入表名称，我需要将其解压缩到整个数据库，而不是手动处理运行脚本的每个表N次。

3 个解决方案

#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:

我会考虑查询INFORMATION_SCHEMA.COLUMNS并动态搜索列和表。尝试在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.

然后遍历每个表中的每个列，并运行dynamic / prepared sql来更新字符串所在的位置。

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

以下是一些很好的帖子，可以帮助您找到正确的方向：

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

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

#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.

这可能看起来有点......丑陋。但也许只需将数据库转储到sql / txt文件，替换所有字符串并使用修改后的转储重新创建数据库。

#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.

您可以运行以下代码来创建运行以进行更新所需的所有udpate语句。它将更新数据库中每个表中的每个字段。您需要运行此代码，并复制结果并运行它们。

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.

我将此限制为仅限char和varchar字段。您可能需要添加其他数据类型。

#1