Shulou(Shulou.com)05/31 Report--

This article is about how to use replace into in MySQL. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

In Oracle, there is the syntax of merge into, which can achieve the function of modifying and adding data at the same time. There is no merge into syntax in MySQL, but there is replace into.

Let's take a look at the usage details of replace into.

To facilitate the 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

Insert 2 rows of data, may not adapt to the Oracle students, how can SQL write this, but it is really interesting to use.

> insert into users (user_id,user_name) values (1maxiaa'), (2recractionbb')

Query OK, 2 rows affected (0.00 sec)

Records: 2 Duplicates: 0 Warnings: 0

The data are as follows:

> select * from users

+-+ +

| | user_id | user_name |

+-+ +

| | 1 | aa |

| | 2 | bb |

+-+ +

2 rows in set (0.00 sec)

OK, let's take a look at the use of replace into. If you insert data into the table, the same data already exists in the table. Will the replace into update directly or delete it, and then insert it.

It is important to understand this because it directly affects the accuracy of the data.

Let's first look at the use of replace into. For example, insert a record below.

> replace into users (user_id, user_name) values (1, 'cc')

Query OK, 2 rows affected (0.00 sec)

After completion, 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 has been replaced, and it seems that it has been deleted and overwritten again. How to verify it.

We can try trace's method first. If you can get something.

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

According to the output, this method can not get the expected data results.

Let's take a different approach and use optimizer_trace above version 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.01sec)

The output is as follows, but we still don't get very detailed information.

Don't be discouraged at this time, knowing that there are always more ways than difficulties. We can test it with a new way of thinking, and we can also verify it by the way.

We recreate a table users2, and the only difference from 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, 'aa'), (2,' bb'), (3, 'cc')

Query OK, 3 rows affected (0.00 sec)

Records: 3 Duplicates: 0 Warnings: 0

At this time, the DDL for creating the table is 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 are as follows:

> SELECT * FROM users2

+-+ +

| | user_id | user_name |

+-+ +

| | 1 | aa |

| | 2 | bb |

| | 3 | cc |

+-+ +

3 rows in set (0.00 sec)

Let's do a replace into operation first.

> 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 with the original user_id of 1 has been changed.

> SELECT * FROM users2

+-+ +

| | user_id | user_name |

+-+ +

| | 1 | dd |

| | 2 | bb |

| | 3 | cc |

+-+ +

3 rows in set (0.01sec)

Check again whether the value of auto_increment is still 4

> 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's still hard to come to a conclusion at this time, so don't take it for granted. Replace into requires that there is a primary key or unique index in the table, and user_id has a primary key, so 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

All right, the big moment has come. Let's take a look at the effect of the following sentence. Only mention user_name in the statement to see if user_id increments or retains the current value.

> REPLACE INTO users2 (user_name) VALUES ('dd')

Query OK, 2 rows affected (0.00 sec)

You can see that user_id has been incremented, which means that this is a completely new insert 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 DDL of the table again as follows, and the auto_increment is indeed incremented.

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

So through the above tests and reasoning, we know that replace into is an operation of delete,insert, not an update based on current data.

In this way, we need to pay special attention when using replace into, some operations may not be what we want, if there is duplicate data when inserting data, which is the case of updating the current record, what should we do? we can use the sister sentence of replace into, the way of insert into on duplicate key, and then we need to use the update option.

For example, based on the above data, we insert data with a user_name of 'dd', and modify it if it exists.

> 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, there is no modification of the data, which is better than we expected.

Therefore, any sentence and function is not omnipotent, but also depends on the scene, without the use of the scene, it is difficult to say.

In addition, another way of using replace into is added for reference.

> replace into users2 (user_id,user_name) select 2 dint bbbb'

Query OK, 2 rows affected (0.01sec)

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, once again look at the use of replace into, found that the log has been reminded, 2 rows affected. Of course, we have a process and conclusions, which is a good attempt.

Thank you for reading! This is the end of the article on "how to use replace into in MySQL". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.