How to use MySQL replace

In Oracle, there is the merge into syntax, which allows you to modify a statement at the same time and add data. MySQL does not have the merge into syntax, but replace.

REPLACE runs like INSERT. There is only one exception. Assume that an old record in the table and a record used for PRIMARY

If the new record of a KEY or UNIQUE index has the same value, the old record is deleted before the new record is inserted.
Note: Unless the table has a primary key or UNIQUE index, using a REPLACE statement is meaningless. The

The statement is the same as the INSERT statement, because no index is used to determine whether other rows are 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.

Same as INSERT. You cannot reference a value from the current row or use a value in a new row. If you use

SET col_name = col_name + 1 ", the reference to the column name on the right will be used as DEFAULT

(Col_name) processing. 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

Before a new row is inserted, one or more old rows are deleted. If the table contains multiple unique indexes and a new row is copied

The value 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 only one row is added to REPLACE, or whether REPLACE also replaces other rows.

: Check whether the number is 1 (add) or greater (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

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...

Replace into 'Table' ('unique _ Column', 'num') VALUES ('$ unique_value', $ num); and insert into 'Table' ('unique _ Column ', 'num') VALUES ('$ unique_value', $ num) on duplicate update num = $ num; there are some differences.
The difference is that old records are deleted when replace into is used. If the table has an auto-increment primary key.
There is a problem.

First, because the new record and the old record have different primary key values, all the associations established with the old data primary key id in other tables will be damaged.

Second, frequent replace into causes the primary key value of the new record to increase rapidly.
One day. When the maximum value is reached, the data overflows because of the large amount of data. There is no way to insert new records. The data table is full, not because the space is insufficient, but because the value of the primary key cannot be increased.

Let's take a look at the detailed rules for using replace.

For ease of demonstration, I first create a table users

Create table users (

User_id int (11) unsigned not null,

User_name varchar (64) default null,

Primary key (user_id)

) Engine = innodb default charset = UTF8;

If you insert two rows of data, you may not be able to use Oracle. But it is really interesting to use SQL.

> Insert into users (user_id, user_name) values (1, 'A'), (2, 'BB ');

Query OK, 2 rows affected (0.00 sec)

Records: 2 Duplicates: 0 Warnings: 0

The data is as follows:

> Select * from users;

+ --------- + ----------- +

| User_id | user_name |

+ --------- + ----------- +

| 1 | aa |

| 2 | bb |

+ --------- + ----------- +

2 rows in set (0.00 sec)

Well, let's take a look at the use of replace into. If the data already exists in the table when inserting data into the table, replace into will be directly updated, deleted, and inserted.

It is very important to understand this point, because this will directly affect the accuracy of the data.

Let's take a look at the use of replace. For example, insert the following record.

> Replace into users (user_id, user_name) values (1, 'CC ');

Query OK, 2 rows affected (0.00 sec)

The data is as follows:

> Select * from users;

+ --------- + ----------- +

| User_id | user_name |

+ --------- + ----------- +

| 1 | cc |

| 2 | bb |

+ --------- + ----------- +

2 rows in set (0.00 sec)

It seems that the data is replaced, and the data is overwritten after being deleted. How to verify it.

We can try the trace method first. Is there any gains.

First, use the explain extended method, which will get a lot of details about the execution plan.

According to the output, this method cannot produce the expected data.

In another way, optimizer_trace is used in versions later than 5.6.

> Set optimizer_trace = "enabled = on ";

Query OK, 0 rows affected (0.00 sec)

> Replace into users (user_id, user_name) values (1, 'DD ');

Query OK, 2 rows affected (0.01 sec)

The output result is as follows, but no detailed information is obtained.

Do not be discouraged at this time. It is always more difficult to find a solution. We can use a new way of thinking to test it, and we can also take verification with us.

The only difference between users2 and users is that user_id uses auto_increment.

Create table 'users2 '(

User_id int (11) unsigned not null AUTO_INCREMENT,

User_name varchar (64) default null,

Primary key (user_id)

) Engine = innodb default charset = UTF8;

Insert 3 rows of data.

> Insert into users2 (user_id, user_name) VALUES (1, 'A'), (2, 'BB'), (3, 'CC ');

Query OK, 3 rows affected (0.00 sec)

Records: 3 Duplicates: 0 Warnings: 0

In this case, view the table creation DDL as follows:

> Show create table users2 \ G

* *************************** 1. row ***************************

Table: users2

Create Table: create table 'users2 '(

'User _ id' int (11) unsigned not null AUTO_INCREMENT,

'User _ name' varchar (64) default null,

Primary key ('User _ id ')

) ENGINE = InnoDB AUTO_INCREMENT = 4 default charset = utf8

1 row in set (0.01 sec)

The data is as follows:

> SELECT * FROM users2;

+ --------- + ----------- +

| User_id | user_name |

+ --------- + ----------- +

| 1 | aa |

| 2 | bb |

| 3 | cc |

+ --------- + ----------- +

3 rows in set (0.00 sec)

Let's first perform a replace into operation.

> Replace into users2 (user_id, user_name) VALUES (1, 'DD ');

Query OK, 2 rows affected (0.00 sec)

The data is as follows. The data whose original user_id is 1 has been changed.

> SELECT * FROM users2;

+ --------- + ----------- +

| User_id | user_name |

+ --------- + ----------- +

| 1 | dd |

| 2 | bb |

| 3 | cc |

+ --------- + ----------- +

3 rows in set (0.01 sec)

Check whether the value of auto_increment is 4 again.

> Show create table users2 \ G

* *************************** 1. row ***************************

Table: users2

Create Table: create table 'users2 '(

'User _ id' int (11) unsigned not null AUTO_INCREMENT,

'User _ name' varchar (64) default null,

Primary key ('User _ id ')

) ENGINE = InnoDB AUTO_INCREMENT = 4 default charset = utf8

1 row in set (0.00 sec)

It is still difficult to draw a conclusion at this time. Remember not to take it for granted. Replace into requires that the table have a primary key or a unique index, and user_id has a primary key. We create a unique index for user_name.

> Alter table users2 add unique key users2_uq_name (user_name );

Query OK, 0 rows affected (0.06 sec)

Records: 0 Duplicates: 0 Warnings: 0

Well, the important moment is now. Let's take a look at the effect of the following statements. Only user_name is mentioned in the statement to see if user_id is incremental or the current value is retained.

> Replace into users2 (user_name) VALUES ('DD ');

Query OK, 2 rows affected (0.00 sec)

We can see that user_id is incremented, which means that this is a brand new insert data.

> Select * from users2;

+ --------- + ----------- +

| User_id | user_name |

+ --------- + ----------- +

| 2 | bb |

| 3 | cc |

| 4 | dd |

+ --------- + ----------- +

3 rows in set (0.00 sec)

At this time, check the table creation DDL as follows. auto_increment is indeed increasing.

Create table 'users2 '(

'User _ id' int (11) unsigned not null AUTO_INCREMENT,

'User _ name' varchar (64) default null,

Primary key ('User _ id '),

Unique key 'users2 _ uq_name '('User _ name ')

) ENGINE = InnoDB AUTO_INCREMENT = 5 default charset = utf8

Therefore, through the test and reasoning above, we know that replace into is a delete, insert operation, rather than an update based on the current data.

So we need to pay special attention when using replace into. Some operations may not be as expected. If duplicate data exists during data insertion, the current record is updated. What should we do, you can use the replace into statement, insert into on duplicate key method, and use the update option later.

For example, we still insert data with user_name 'DD' based on the above data. If so, modify it.

> Insert into users2 (user_name) VALUES ('DD') on duplicate key update user_name = VALUES (user_name );

Query OK, 0 rows affected (0.00 sec)

According to the running results, data is not modified, which is better than we expected.

Therefore, all statements and functions are not omnipotent. We have to look at the scenario. It is hard to say clearly without using the scenario.

In addition, replace into is supplemented for reference.

> Replace into users2 (user_id, user_name) select 2, 'bbbbb ';

Query OK, 2 rows affected (0.01 sec)

Records: 1 Duplicates: 1 Warnings: 0

> Select * from users2;

+ --------- + ----------- +

| User_id | user_name |

+ --------- + ----------- +

| 2 | bbbb |

| 3 | cc |

| 4 | dd |

+ --------- + ----------- +

3 rows in set (0.00 sec)

In fact, I checked the use of replace into again and found that the log had prompted that 2 rows affected. Of course, we have a conclusion in the process, which is a good attempt.