MySQL update batch modification and substitution statement

MySQL Bulk substitution specified field string statement

UPDATE data table name set field name = Replace (field name, ' String to replace ', ' replace with ') WHERE set condition;

Example

The code is as follows	Copy Code
UPDATE ' cdb_name ' SET ' field_name ' = replace (' Field_name ', ' from_str ', ' to_str ')

Description

cdb_name--the name of the table where the character or string is located
field_name--the field name of the field where the character or string is located
from_str--a string to replace
to_str--replaced with string

For example:

Enter phpMyAdmin, open the database to operate, click on the top of the SQL, execute the following SQL statement: (here I use the Discuz demo)

The code is as follows	Copy Code
UPDATE ' pre_forum_post ' SET ' author ' = replace (' author ', ' Xiang Lei tribe ', ' Xiang Lei ')

It means to change the username "Xiang Lei tribe" to "Xiang Lei", where pre_ to modify the table prefix of your own database.

The code is as follows	Copy Code
UPDATE phpcms_article SET title=replace (title, ' [2009] ', ' (2009) '); UPDATE phpcms_article SET content=replace (Content, ' [2009] ', ' (2009) ');

You can also add substitution criteria in the statement, such as replacing only content with a qualified ID less than 200:

The code is as follows	Copy Code
UPDATE wp_posts SET post_content = replace (post_content, ' Search engine optimization ', ' Search engine marketing ') WHERE ID < 200;

With an advanced point at the back.

The code is as follows	Copy Code
UPDATE ' cdb_settings ' SET ' value ' = ' WHERE CONVERT (' variable ' USING utf8) = ' adminipaccess ' LIMIT 1;