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

Find and replace entire mysql database

提问by Andy

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]');

Do i just use an asterix?

我只使用星号吗？

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

回答by Dean Rather

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

回答by Vishnu Sharma

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');

回答by symcbean

This strongly implies that your data IS NOT NORMALISED to begin with.

这强烈暗示您的数据一开始就没有标准化。

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

这样的事情应该可以工作（注意，您没有提到您使用任何其他语言 - 所以它写成 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.

我将留给您解决如何声明 word_sub 函数。

回答by Mark Byers

Short answer: You can't.

简短的回答：你不能。

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

长答案：您可以使用INFORMATION_SCHEMA来获取表定义并使用它来动态生成必要的 UPDATE 语句。例如，你可以从这个开始：

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.

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

回答by T.Todua

BE CAREFUL, when replacing with REPLACE command!

使用 REPLACE 命令替换时要小心！

why?

为什么？

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

因为您的数据库很有可能包含序列化数据（尤其是wp_options表），所以仅使用“替换”可能会破坏数据。

Use recommended serialization: https://puvox.software/tools/wordpress-migrator

使用推荐的序列化：https: //puvox.software/tools/wordpress-migrator

回答by user1794295

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 数据库中的文本字符串。

回答by Jaha Rabari

Simple Soltion

简单的解决方案

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

回答by John Parker

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 mysqldumpand 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.)

或者，您可以通过mysqldump转储数据库，然后简单地对生成的 SQL 文件执行搜索/替换。（我建议在此过程中将任何可能触及数据库的内容脱机，并使用 --add-drop-table 和 --extended-insert 标志。）但是，您需要确保搜索/替换文本不会改变除数据本身以外的任何内容（即：您要换出的文本可能不会作为 SQL 语法的一部分出现），我真的会尝试重新插入首先是一个空的测试数据库。）

回答by Sibeesh Venu

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
• Select the database you need to perform the changes

• Click on the search option

  

• 登录到您的 phpMyAdmin
• 选择您需要执行更改的数据库

• 点击搜索选项

  

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

您始终可以选择所有表或任何表。请记住给出搜索关键字，它将用作通配符（％）。

• 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_imagesWHERE (CONVERT(idUSING utf8) LIKE '%sibee%' OR CONVERT(pathUSING utf8) LIKE '%sibee%' OR CONVERT(image_md5USING utf8) LIKE '%sibee%' OR CONVERT(resultsUSING utf8) LIKE '%sibee%' OR CONVERT(galleryUSING utf8) LIKE '%sibee%' OR CONVERT(image_sizeUSING utf8) LIKE '%sibee%' OR CONVERT(orig_sizeUSING utf8) LIKE '%sibee%' OR CONVERT(updatesUSING utf8) LIKE '%sibee%' OR CONVERT(updatedUSING utf8) LIKE '%sibee%' OR CONVERT(traceUSING utf8) LIKE '%sibee%' OR CONVERT(attachment_idUSING utf8) LIKE '%sibee%' OR CONVERT(resizeUSING utf8) LIKE '%sibee%' OR CONVERT(convertedUSING utf8) LIKE '%sibee%' OR CONVERT(levelUSING utf8) LIKE '%sibee%' OR CONVERT(pendingUSING utf8) LIKE '%sibee%' OR CONVERT(backupUSING utf8) LIKE '%sibee%')

• 现在您可以一张一张地打开每个表并执行更新 生成的示例查询可能如下所示。

  选择 * 从sibeecst_passion。wp_ewwwio_imagesWHERE (CONVERT( idUSING utf8) LIKE '%sibee%' OR CONVERT( pathUSING utf8) LIKE '%sibee%' OR CONVERT( image_md5USING utf8) LIKE '%sibee%' OR CONVERT( resultsUSING utf8) LIKE '%sibee%' OR CONVERT( galleryUSING utf8) LIKE '%sibee%' OR CONVERT( image_sizeUSING utf8) LIKE '%sibee%' OR CONVERT( orig_sizeUSING utf8) LIKE '%sibee%' OR CONVERT( updatesUSING utf8) LIKE '%sibee%' OR CONVERT( updatedUSING utf8) LIKE '%sibee%' OR CONVERT( traceUSING utf8) LIKE '%sibee%' OR CONVERT( attachment_idUSING utf8) LIKE '%sibee%' OR CONVERT( resizeUSING utf8) LIKE '%sibee%' OR CONVERT( convertedUSING utf8) ) LIKE '%sibee%' 或 CONVERT( levelUSING utf8) LIKE 'pendingbackup使用 utf8) LIKE '%sibee%')

回答by Julien Berrier

I had the same issue on MySQL. I took the procedure from symcbean and adapted her to my needs.

我在 MySQL 上遇到了同样的问题。我采用了 symcbean 的程序并根据我的需要调整了她。

Mine is only replacing textual values (or any type you put in the SELECT FROM information_schema) so if you have date fields, you will not have an error in execution.

我的只是替换文本值（或您放入 SELECT FROM information_schema 的任何类型），因此如果您有日期字段，则不会在执行时出错。

Mind the collate in SET @stmt, it must match you database collation.

注意 SET @stmt 中的校对，它必须与您的数据库校对匹配。

I used a template request in a variable with multiple replaces but if you have motivation, you could have done it with one CONCAT().

我在具有多个替换的变量中使用了模板请求，但如果您有动力，您可以使用一个 CONCAT() 来完成。

Anyway, if you have serialized data in your database, don't use this. It will not work unless you replace your string with a string with the same lenght.

无论如何，如果您的数据库中有序列化数据，请不要使用它。除非您用相同长度的字符串替换您的字符串，否则它将不起作用。

Hope it helps someone.

希望它可以帮助某人。

DELIMITER $$

DROP PROCEDURE IF EXISTS replace_all_occurences_in_database$$
CREATE PROCEDURE replace_all_occurences_in_database (find_string varchar(255), replace_string varchar(255))
BEGIN
  DECLARE loop_done integer DEFAULT 0;
  DECLARE current_table varchar(255);
  DECLARE current_column varchar(255);
  DECLARE all_columns CURSOR FOR
  SELECT
    t.table_name,
    c.column_name
  FROM information_schema.tables t,
       information_schema.columns c
  WHERE t.table_schema = DATABASE()
  AND c.table_schema = DATABASE()
  AND t.table_name = c.table_name
  AND c.DATA_TYPE IN('varchar', 'text', 'longtext');

  DECLARE CONTINUE HANDLER FOR NOT FOUND
  SET loop_done = 1;

  OPEN all_columns;

table_loop:
LOOP
  FETCH all_columns INTO current_table, current_column;
  IF (loop_done > 0) THEN
    LEAVE table_loop;
  END IF;
  SET @stmt = 'UPDATE `|table|` SET `|column|` = REPLACE(`|column|`, "|find|", "|replace|") WHERE `|column|` LIKE "%|find|%"' COLLATE `utf8mb4_unicode_ci`;
  SET @stmt = REPLACE(@stmt, '|table|', current_table);
  SET @stmt = REPLACE(@stmt, '|column|', current_column);
  SET @stmt = REPLACE(@stmt, '|find|', find_string);
  SET @stmt = REPLACE(@stmt, '|replace|', replace_string);
  PREPARE s1 FROM @stmt;
  EXECUTE s1;
  DEALLOCATE PREPARE s1;
END LOOP;
END
$$

DELIMITER ;