MySQL Replace function we often use, the following is a detailed description of the use of MySQL replace function, I hope that you learn the MySQL replace function can be enlightened.
Recently in the study of CMS, in the data conversion needs to use MySQL MySQL replace function, here a brief introduction.
For example, you want to replace the ABC of the F1 field in the table TB1 with Def
UPDATE tb1 SET f1=replace (F1, ' abc ', ' Def ');
REPLACE (STR,FROM_STR,TO_STR)
All occurrences of the string from_str in string str are replaced by TO_STR, which then returns the string:
mysql> SELECT REPLACE (' www.mysql.com ', ' w ', ' Ww ');
-> ' WwWwWw.mysql.com '
This function is multibyte-safe.
Example:
Copy Code code 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
For example: Replace AA in the Name field in table tables with Bbupdate table set Name=replace (name, ' AA ', ' BB ')
Below is another netizen's article, may refer to under:
Today inadvertently found the use of replace, and in the project there are related applications, applied to the project is sure to be tried. Friends, let's take a look at the detailed explanations of repace.
The run of replace is very similar to 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 the insert, because no indexes are used to determine whether the new row replicates other rows.
The values for all columns are taken from the value specified in the Replace statement. All missing columns are set to their default values, which are the same as inserts. 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", a reference to the column name on the right side 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. This number is the same as the number of rows that were 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 a table contains more than one unique index, and a new row duplicates a value from a different old row in a different unique index, it is possible that a single row replaces more than one old row.
The number of rows affected can easily determine whether replace has added only one row, or if replace has also replaced other rows: check to see if 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 replace a table 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 the new row into the table
2. When an insert fails because of a duplicate keyword error for a primary key or unique keyword:
A. Delete conflicting rows that contain duplicate key values from the table
B. Try inserting the new row into the table again
Use format:
Copy Code code 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 ...
