使用MySQL查询查找并替换整个表中的文本。

Usually I use manual find to replace text in a MySQL database using phpmyadmin. I'm tired of it now, how can I run a query to find and replace a text with new text in the entire table in phpmyadmin?

通常我用phpmyadmin替换MySQL数据库中的文本。我现在已经厌倦了，如何运行查询来查找和替换phpmyadmin中整个表中的文本?

Example: find keyword domain.com, replace with www.domain.com.

示例:查找关键字domain.com，替换为www.domain.com。

8 个解决方案

#1

For a single table update

对于单个表的更新

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

From multiple tables-

从多个表-

If you want to edit from all tables, best way is to take the dump and then find/replace and upload it back.

如果您想要从所有表中进行编辑，最好的方法是获取转储，然后找到/替换并将其上载回来。

#2

The easiest way I have found is to dump the database to a text file, run a sed command to do the replace, and reload the database back into MySQL.

我发现的最简单的方法是将数据库转储到一个文本文件中，运行一个sed命令进行替换，并将数据库重新加载回MySQL。

All commands are bash on Linux, from memory.

所有的命令都是来自内存的Linux上的bash。

Dump database to text file

将数据库转储到文本文件。

mysqldump -u user -p databasename > ./db.sql

Run sed command to find/replace target string

运行sed命令查找/替换目标字符串

sed -i 's/oldString/newString/g' ./db.sql

Reload the database into MySQL

将数据库重新加载到MySQL中

mysql -u user -p databasename < ./db.sql

Easy peasy.

容易peasy。

#3

Put this in a php file and run it and it should do what you want it to do.

把它放到php文件中并运行它，它应该执行您希望的操作。

// Connect to your MySQL database.
$hostname = "localhost";
$username = "db_username";
$password = "db_password";
$database = "db_name";

mysql_connect($hostname, $username, $password);

// The find and replace strings.
$find = "find_this_text";
$replace = "replace_with_this_text";

$loop = mysql_query("
    SELECT
        concat('UPDATE ',table_schema,'.',table_name, ' SET ',column_name, '=replace(',column_name,', ''{$find}'', ''{$replace}'');') AS s
    FROM
        information_schema.columns
    WHERE
        table_schema = '{$database}'")
or die ('Cant loop through dbfields: ' . mysql_error());

while ($query = mysql_fetch_assoc($loop))
{
        mysql_query($query['s']);
}

#4

Running an SQL query in PHPmyadmin to find and replace text in all wordpress blog posts, such as finding mysite.com/wordpress and replacing that with mysite.com/news Table in this example is tj_posts

在PHPmyadmin中运行一个SQL查询来查找和替换所有wordpress博客文章中的文本，例如找到mysite.com/wordpress并将其替换为本例中的mysite.com/news表就是tj_posts

UPDATE `tj_posts`
SET `post_content` = replace(post_content, 'mysite.com/wordpress', 'mysite.com/news')

#5

 UPDATE table SET field = replace(field, text_needs_to_be_replaced, text_required);

Like for example, if I want to replace all occurrences of John by Mark I will use below,

例如，如果我想用Mark替换所有出现的John，我将在下面使用，

UPDATE student SET student_name = replace(student_name, 'John', 'Mark');

#6

Another option is to generate the statements for each column in the database:

另一种选择是为数据库中的每一列生成语句:

SELECT CONCAT(
    'update ', table_name , 
    ' set ', column_name, ' = replace(', column_name,', ''www.oldDomain.com'', ''www.newDomain.com'');'
) AS statement
FROM information_schema.columns
WHERE table_schema = 'mySchema' AND table_name LIKE 'yourPrefix_%';

This should generate a list of update statements that you can then execute.

这将生成一个更新语句列表，您可以执行这些语句。

#7

I believe "swapnesh" answer to be the best ! Unfortunately I couldn't execute it in phpMyAdmin (4.5.0.2) who although illogical (and tried several things) it kept saying that a new statement was found and that no delimiter was found…

我相信“swapnesh”的答案是最好的!不幸的是，我无法在phpMyAdmin(4.5.0.2)中执行它，尽管它不符合逻辑(并尝试了一些东西)，但它一直说找到了一条新的语句，而且没有找到分隔符……

Thus I came with the following solution that might be usefull if you exeprience the same issue and have no other access to the database than PMA…

因此，我提出了以下解决方案，如果您遇到相同的问题，并且除了PMA之外没有其他访问数据库的权限，那么该解决方案可能是有用的。

UPDATE `wp_posts` AS `toUpdate`,
(SELECT `ID`,REPLACE(`guid`,'http://old.tld','http://new.tld') AS `guid` 
 FROM `wp_posts` WHERE `guid` LIKE 'http://old.tld%') AS `updated`
SET `toUpdate`.`guid`=`updated`.`guid`
WHERE `toUpdate`.`ID`=`updated`.`ID`;

To test the expected result you may want to use :

为了测试您可能想要使用的预期结果:

SELECT `toUpdate`.`guid` AS `old guid`,`updated`.`guid` AS `new guid`
FROM `wp_posts` AS `toUpdate`,
(SELECT `ID`,REPLACE(`guid`,'http://old.tld','http://new.tld') AS `guid`
 FROM `wp_posts` WHERE `guid` LIKE 'http://old.tld%') AS `updated`
WHERE `toUpdate`.`ID`=`updated`.`ID`;

#8

Generate change SQL queries (FAST)

mysql -e "SELECT CONCAT( 'update ', table_name , ' set ', column_name, ' = replace(', column_name,', ''www.oldsite.com'', ''www.newsite.com'');' ) AS statement FROM information_schema.columns WHERE table_name LIKE 'wp_%'" -u root -p your_db_name_here > upgrade_script.sql

mysql -e "选择CONCAT('update '， table_name，' set '， column_name '， ' = replace('， column_name，'， " www.oldsite.com "， " www.newsite.com ")作为information_schema的语句。列中表名如'wp_%' -u root -p your_db_name_b0 upgrade_script.sql

Remove any garbage at the start of the file. I had some.

nano upgrade_script.sql

纳米upgrade_script.sql

Run generated script with --force options to skip errors. (SLOW - grab a coffee if big DB)

mysql -u root -p your_db_name_here --force < upgrade_script.sql

这里是mysql -u root -p your_db_name_here——强制执行< upgrade_script.sql