MySQL Replace function replaces the use of a string statement (MySQL string substitution)

MySQL Replace function we often use, the following gives you a detailed description of the use of MySQL replace function, I hope you learn MySQL Replace function can be enlightened.
Recently in the study of CMS, in the data conversion need to use the MySQL MySQL replace function, here is a brief introduction.
For example, you want to replace the ABC of the F1 field in the table tb1 with the Def
UPDATE tb1 SET f1=replace (F1, ' abc ', ' Def ');
REPLACE (STR,FROM_STR,TO_STR)
All occurrences of the string from_str in the string str are replaced by TO_STR, and then the string is returned:
mysql> SELECT REPLACE (' www.mysql.com ', ' w ', ' Ww ');
' WwWwWw.mysql.com '
This function is multi-byte safe.

The code is as follows:

UPDATE ' dede_addonarticle ' SET BODY = REPLACE (body, ' </td> ', ');
UPDATE ' dede_addonarticle ' SET BODY = REPLACE (body, ' </tr> ', ');
UPDATE ' dede_addonarticle ' SET BODY = REPLACE (body, ' <tr> ', ');
UPDATE ' dede_archives ' SET title= REPLACE (title, ' Oceanic News-', ');
UPDATE ' dede_addonarticle ' SET BODY = REPLACE (body, ' ... /.. /.. /.. /.. /.. /', ' http://sc.jb51.net/meal/');

MySQL Replace
Usage 1.replace intoreplace into table (id,name) VALUES (' 1 ', ' AA '), (' 2 ', ' BB ')
The purpose of this statement is to insert two records into table tables.
2.replace (object, Search,replace)
Replace all occurrences of search in object with Replaceselect replace (' www.163.com ', ' w ', ' Ww ')--->www wWw.163.com
Example: Replacing AA in the Name field in table tables with Bbupdate table set Name=replace (name, ' AA ', ' BB ')

Here are the other users of the article, you can refer to the following:
The use of replace has been inadvertently discovered today, and there are related applications in the project that have been applied to the project. Friends, let's see a detailed explanation of Repace.

Replace runs much like an insert. Except for one point, if an old record in the table has the same value as a new record for primary key or a unique index, the old record is deleted before the new record is inserted. See section 13.2, 4, "Insert Syntax".
Note that it is meaningless to use a Replace statement unless the table has a primary key or a unique index. The statement is the same as insert, because no index is used to determine whether the new row has replicated other rows.
The values of all columns are taken from the values that are specified in the Replace statement. All missing columns are set to their default values, which is the same as insert. You cannot reference a value from the current row, nor can you use a value in a new row. If you use an assignment such as "SET col_name = col_name + 1", the reference to the column name on the right is treated as default (col_name). Therefore, the assignment is equivalent to set col_name = DEFAULT (col_name) + 1.
In order to be able to use replace, you must have both insert and delete permissions for the table.
The Replace statement returns a number that indicates the number of rows affected. The number is the number of rows that are deleted and inserted. If the number is 1 for a single-line replace, the row is inserted and no rows are deleted. If the number is greater than 1, one or more old rows are deleted before the new row is inserted. If the table contains more than one unique index, and the new row replicates the values of the different old rows in different unique indexes, it is possible that a single row has replaced multiple old rows.
The number of rows affected can easily determine if replace adds only one row, or if replace replaces other rows: Check whether the number is 1 (added) or larger (replace).
If you are using the C API, you can use the Mysql_affected_rows () function to get the number of rows affected.
Currently, you cannot change from one table to another in a subquery and select from the same table.
The following is a more detailed description of the algorithm used (the algorithm is also used for load DATA ...). REPLACE):
1. Try inserting a new row into the table
2. When an insert fails because of a duplicate keyword error for a primary key or a unique keyword:
A. Removing conflicting rows from a table that contain duplicate key values
B. Try again to insert a new row into the table
Use format:

The code is as follows:

REPLACE [Low_priority | DELAYED]
[Into] tbl_name [(Col_name,...)]
VALUES ({expr | DEFAULT},...), (...),...
Or:
REPLACE [Low_priority | DELAYED]
[Into] Tbl_name
SET col_name={expr | DEFAULT}, ...
Or:
REPLACE [Low_priority | DELAYED]
[Into] tbl_name [(Col_name,...)]
SELECT ...

MySQL Replace function replaces the use of a string statement (MySQL string substitution)