Mysql replace instance tutorial

Mysql replace instance tutorial
Yesterday, when I imported a piece of data, I thought about using update and replace in combination,
Update tablename set fields = replace ('A', 'bb', str) where
What is the difference between Replace INTO and insert into? I told him to visit my blog at night. At that time, I was still busy. Now

I found something from the MYSQL Manual. The MYSQL manual says REPLACE INTO is more detailed.

REPLACE runs like INSERT. Only one record exists. If one old record and one

If the new record of a primary key or UNIQUE index has the same value, the old record

The record is deleted. 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

For example, if "SET col_name = col_name + 1" is assigned, the reference to the column name on the right will be

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 number of rows deleted and inserted.

And. If this number is 1 for a single row, one row is inserted and no row is deleted. If

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,

In addition, if the new row copies the values of different old rows in different unique indexes, a single row may replace multiple

Old row.

The number of affected rows can be easily determined whether only one row is added for REPLACE, or whether REPLACE also replaces its

Row: Check whether the number is 1 (ADD) or greater (replace ).

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

 

Mysql replace usage
1. replace
Replace into table (id, name) values ('1', 'A'), ('2', 'bb ')
This statement inserts two records into the table. If the primary key id is 1 or 2 does not exist
It is equivalent
Insert into table (id, name) values ('1', 'A'), ('2', 'bb ')
Data is not inserted if the same value exists.

2. replace (object, search, replace)
Replace all search objects with replace
Select replace ('www .163.com ', 'w', 'ww') ---> WwWwWw.163.com
For example, replace aa in the name field of the table with bb.
Update table set name = replace (name, 'AA', 'bb ')