Batch replacement of the string in the MySQL specified field

  • 2020-05-09 19:27:34
  • OfStack

The specific syntax of batch replacement is:

 
UPDATE  The name of the table  SET 
 Specified field  = replace( Specified field , ' The string to replace ', ' The desired string ') 
WHERE  conditions ; 


If you want to replace "resolve" with "liberate" in the content field for records with ID less than 5000 in the article table, then the syntax is:
 
UPDATE article SET 
content = replace(content, ' To solve ', ' liberation ') 
WHERE ID<5000; 


Isn't it convenient :)

There are a lot of download connections on the website. We used to use port 86 before, but now we have migrated the server. The default of the new server is port 80.
Fortunately, the links are all written in the MySQL database, so it is efficient to batch replace the port number in the specified field by MySQL.

The specific syntax for batch substitution of a specific string for a field specified by MySQL is:


UPDATE  The name of the table  SET
 Specified field  = replace ( Specified field , ' The string you want to replace ', ' Replaced by a string ')
WHERE  conditions ;

In addition, a filtering substitution can also be made for ID. For example, if we want to replace "86" with "80" in content_type_value with ID less than 300 in content_typtheme table, then the syntax is:


UPDATE content_type__theme SET
field_down_value = replace (field_down_value, '86 ' , '80 ' );
WHERE ID<300;


Related articles: