MySQL replace function replacement string statement usage

We often use the MySQL replace function. The following describes the usage of the MySQL replace function in detail, hoping to enlighten you in learning the MySQL replace function.
I recently studied CMS and used mysql replace function during data conversion. Here I will briefly introduce it.
For example, you want to replace the abc of the f1 field in Table tb1 with def.
UPDATE tb1 SET f1 = REPLACE (f1, 'abc', 'def ');
REPLACE (str, from_str, to_str)
All the from_str strings in the str string are replaced by to_str, and the following string is returned:
Mysql> select replace ('www .mysql.com ', 'w', 'ww ');
-> 'Wwwwww .mysql.com'
This function is multi-byte secure.
Example:
Copy codeThe 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, 'Ocean 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', 'A'), ('2', 'bb ')
This statement inserts two records into the table.
2. replace (object, search, replace)
Replace all search objects with replaceselect replace ('www .163.com ', 'w', 'ww') ---> www wWw.163.com
For example, replace aa in the name field of the table with bbupdate table set name = replace (name, 'AA', 'bb ')

The following is an article by other netizens. For more information, see:
Today, I have no intention of discovering the use of replace, and there are still related applications in the project. The application to the project is indeed a trial. Let's take a look at the detailed explanation of repace.

REPLACE runs like INSERT. Except for one, if an old record in the table has the same value as a new record used for the 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: unless the table has a primary key or UNIQUE index, using a REPLACE statement is meaningless. This statement is the same as INSERT, because no index is used to determine whether other rows have been copied in the new row.
The values of all columns are equal to the value specified in the REPLACE statement. All missing columns are set as their default values, which is the same as INSERT. You cannot reference a value from the current row or use a value in a new row. If you use a value such as "SET col_name = col_name + 1", the reference to the column name on the right will be processed as DEFAULT (col_name. Therefore, the value is equivalent to SET col_name = DEFAULT (col_name) + 1.
To use REPLACE, you must have both the INSERT and DELETE permissions for the table.
The REPLACE statement returns a number to indicate the number of affected rows. This is the sum of the number of deleted and inserted rows. If this number is 1 for a single row, one row is inserted and no row is 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 multiple unique indexes, and the new row copies the values of different old rows in different unique indexes, it is possible that a single row replaces multiple old rows.
The number of affected rows can be easily determined whether REPLACE only adds one row, or whether REPLACE also replaces other rows: Check whether the number is 1 (Added) or larger (replaced ).
If you are using c api, you can use the mysql_affected_rows () function to obtain the number of affected rows.
Currently, you cannot change a table in a subquery and select from the same table.
The following is a more detailed description of the algorithm used (this algorithm is also used for load data... REPLACE ):
1. Try to Insert a new row into the table
2. When insertion fails due to a duplicate keyword error for the primary key or unique Keyword:
A. Delete conflicting rows with duplicate keyword values from the table
B. Try to Insert a new row into the table again
Format:
Copy codeThe 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...