查找并替换整个mysql数据库。

i would like to do a find and replace inside an entire database not just a table.

我希望在整个数据库中进行查找和替换，而不仅仅是表。

How can i alter the script below to work?

如何修改下面的脚本以工作?

update [table_name] set [field_name] = replace([field_name],'[string_to_find]','[string_to_replace]');

更新[table_name] set [field_name] = replace([field_name]，'[string_to_find]'，'[string_to_replace]');

Do i just use an asterix?

我用的是asterix吗?

update * set [field_name] = replace([field_name],'[string_to_find]','[string_to_replace]');

更新* set [field_name] = replace([field_name]]，'[string_to_find]'，'[string_to_replace]';

11 个解决方案

#1

sqldump to a text file, find/replace, re-import the sqldump.

sqldump文件到文本文件，找到/替换，重新导入sqldump。

Dump the database to a text file
mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql

将数据库转储到文本文件mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql

Restore the database after you have made changes to it.
mysql -u root -p[root_password] [database_name] < dumpfilename.sql

在对数据库进行更改后恢复它。mysql -u root -p[root_password] [database_name] < dumpfilename.sql

#2

Update old URL to new URL in word-press mysql Query:

在word-press mysql查询中更新旧URL到新URL:

UPDATE wp_options SET option_value = replace(option_value, 'http://olddomain.com', 'http://newdomain.com') WHERE option_name = 'home' OR option_name = 'siteurl';

UPDATE wp_posts SET guid = replace(guid, 'http://olddomain.com','http://newdomain.com');

UPDATE wp_posts SET post_content = replace(post_content, 'http://olddomain.com', 'http://newdomain.com');

UPDATE wp_posts SET post_excerpt = replace(post_excerpt, 'http://olddomain.com', 'http://newdomain.com');

UPDATE wp_postmeta SET meta_value = replace(meta_value, 'http://olddomain.com', 'http://newdomain.com');

#3

This strongly implies that your data IS NOT NORMALISED to begin with. You really should fix that.

这强烈暗示您的数据一开始就不是标准化的。你真的应该解决这个问题。

Somteh=thing like this should work (NB you've not mentioned of your using any other languages - so its written as a MySQL stored procedure)

Somteh=类似的东西应该可以用(NB，你没有提到你使用其他语言——所以它是作为MySQL存储过程编写的)

 create procedure replace_all(find varchar(255), 
        replce varchar(255), 
        indb varcv=char(255))
 DECLARE loopdone INTEGER DEFAULT 0;
 DECLARE currtable varchar(100);
 DECLARE alltables CURSOR FOR SELECT t.tablename, c.column_name 
    FROM information_schema.tables t,
    information_schema.columns c
    WHERE t.table_schema=indb
    AND c.table_schema=indb
    AND t.table_name=c.table_name;

 DECLARE CONTINUE HANDLER FOR NOT FOUND
     SET loopdone = 1;

 OPEN alltables;

 tableloop: LOOP
    FETCH alltables INTO currtable, currcol; 
    IF (loopdone>0) THEN LEAVE LOOP;
    END IF;
         SET stmt=CONCAT('UPDATE ', 
                  indb, '.', currtable, ' SET ',
                  currcol, ' = word_sub(\'', find, 
                  '\','\'', replce, '\') WHERE ',
                  currcol, ' LIKE \'%', find, '%\'');
         PREPARE s1 FROM stmt;
         EXECUTE s1;
         DEALLOCATE PREPARE s1;
     END LOOP;
 END //

I'll leave it to you to work out how to declare the word_sub function - I've done enough free programming here.

我把如何声明word_sub函数的问题留给你来解决——我在这里已经做了足够的*编程。

#4

Short answer: You can't.

简短的回答是:你不能。

Long answer: You can use the INFORMATION_SCHEMA to get the table definitions and use this to generate the necessary UPDATE statements dynamically. For example you could start with this:

长话短说:您可以使用INFORMATION_SCHEMA获取表定义，并使用它动态地生成必要的更新语句。例如，你可以这样开始:

SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'your_schema'

I'd try to avoid doing this though if at all possible.

如果可能的话，我会尽量避免这样做。

#5

This isn't possible - you need to carry out an UPDATE for each table individually.

这是不可能的——您需要对每个表分别进行更新。

WARNING: DUBIOUS, BUT IT'LL WORK (PROBABLY) SOLUTION FOLLOWS

警告:值得怀疑，但它可能会带来解决方案

Alternatively, you could dump the database via mysqldump and simply perform the search/replace on the resultant SQL file. (I'd recommend offlining anything that might touch the database whilst this is in progress, as well as using the --add-drop-table and --extended-insert flags.) However, you'd need to be sure that the search/replace text wasn't going to alter anything other than the data itself (i.e.: that the text you were going to swap out might not occur as a part of SQL syntax) and I'd really try doing the re-insert on an empty test database first.)

或者，您可以通过sqldump转储数据库，并在生成的SQL文件上执行搜索/替换。(我建议在此过程中删除任何可能触及数据库的内容，以及使用-add-drop-table和- extension -insert标志。)但是，您需要确保搜索/替换文本不会改变除数据本身之外的任何内容(例如。:您将要交换的文本可能不会作为SQL语法的一部分出现)，而且我确实会首先尝试在一个空的测试数据库上重新插入。

#6

MySQL Search & Replace Tool

MySQL搜索和替换工具

Very useful web-based tool written in PHP which makes it easy to search and replace text strings in a MySQL database.

用PHP编写的非常有用的基于web的工具，它使搜索和替换MySQL数据库中的文本字符串变得非常容易。

#7

Simple Soltion

简单Soltion

UPDATE `table_name`
 SET `field_name` = replace(same_field_name, 'unwanted_text', 'wanted_text')

#8

BE CAREFUL, when replacing with REPLACE command!

why?

为什么?

because there is a great chance that your database contains serialized data (especially wp_options table), so using just "replace" might break data.

因为您的数据库很可能包含序列化数据(特别是wp_options表)，所以使用“replace”可能会破坏数据。

Use recommended serialization: https://www.protectpages.com/tools/WordpressMigrator

推荐使用序列化:https://www.protectpages.com/tools/WordpressMigrator

#9

If you are in phpMyAdmin and you have only a minimal change, you can do this in an easy way.

如果您在phpMyAdmin中，并且您只有一个最小的更改，您可以用简单的方法来做。

• Login to your phpMyAdmin
• 登录到你的phpMyAdmin
• Select the database you need to perform the changes
• 选择需要执行更改的数据库

• Click on the search option

  点击搜索选项

  

You can always select either all the tables or any. Remember to give the search keyword, it will be used as wildcard(%).

您总是可以选择所有的表或任何表。记住要给出search关键字，它将被用作通配符(%)。

• Now click on Go.
• 现在按去。
• This will give you all the tables which have the item you have searched for.
• 这将为您提供包含您已搜索的项目的所有表。

• Now you can open each table one by one and perform the update A sample query generated may look like the following.

  现在可以逐个打开每个表，并执行生成的示例查询的更新，如下所示。

  SELECT * FROM sibeecst_passion.wp_ewwwio_images WHERE (CONVERT(id USING utf8) LIKE '%sibee%' OR CONVERT(path USING utf8) LIKE '%sibee%' OR CONVERT(image_md5 USING utf8) LIKE '%sibee%' OR CONVERT(results USING utf8) LIKE '%sibee%' OR CONVERT(gallery USING utf8) LIKE '%sibee%' OR CONVERT(image_size USING utf8) LIKE '%sibee%' OR CONVERT(orig_size USING utf8) LIKE '%sibee%' OR CONVERT(updates USING utf8) LIKE '%sibee%' OR CONVERT(updated USING utf8) LIKE '%sibee%' OR CONVERT(trace USING utf8) LIKE '%sibee%' OR CONVERT(attachment_id USING utf8) LIKE '%sibee%' OR CONVERT(resize USING utf8) LIKE '%sibee%' OR CONVERT(converted USING utf8) LIKE '%sibee%' OR CONVERT(level USING utf8) LIKE '%sibee%' OR CONVERT(pending USING utf8) LIKE '%sibee%' OR CONVERT(backup USING utf8) LIKE '%sibee%')

  从sibeecst_passion选择*。wp_ewwwio_images地方(转换(id使用utf8)像“% sibee %”或转换(路径使用utf8)像“% sibee %”或转换(image_md5使用use utf8)像“% sibee %”或转换(结果使用utf8)像“% sibee %”或转换(画廊使用use utf8)像“% sibee %”或转换(image_size使用use utf8)像“% sibee %”或转换(orig_size使用use utf8)像“% sibee %”或转换(更新使用utf8)像“% sibee %”或转换(更新使用utf8)像“% sibee %”或转换(跟踪使用use utf8)像“% sibee %”或转换(attachment_id使用utf8)如'%sibee%'或转换(使用utf8调整大小)如'%sibee%'或转换(使用utf8转换)如'%sibee%'或'%sibee%'或'%sibee%'

#10

Another option (depending on the use case) would be to use DataMystic's TextPipe and DataPipe products. I've used them in the past, and they've worked great in the complex replacement scenarios, and without having to export data out of the database for find-and-replace.

另一种选择(取决于用例)是使用DataMystic的TextPipe和DataPipe产品。我以前使用过它们，它们在复杂的替换场景中非常有用，而且不需要从数据库中导出数据进行查找和替换。

#11

I just wanted to share how I did this find/replace thing with sql database, because I needed to replace links from Chrome's sessionbuddy db file.

我只是想分享我如何用sql数据库找到/替换东西，因为我需要替换来自Chrome sessionbuddy db文件的链接。

• So I exported sql database file as .txt file by using SQLite Database Browser 2.0 b1
• 因此，我使用SQLite数据库浏览器2.0 b1将sql数据库文件导出为.txt文件
• Find/replace in notepad++
• notepad++查找/替换
• Imported the .txt file back on SQLite Database Browser 2.0 b1
• 在SQLite数据库浏览器2.0 b1上导入.txt文件