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

In this issue, the editor will bring you about the role of replace into sentences in MySQL. The article is rich in content and analyzes and describes it from a professional point of view. I hope you can get something after reading this article.

Operate on the main library.

The code is as follows:

Root@test 12:36:51 > show create table T1\ G

* * 1. Row *

Table: t1

Create Table: CREATE TABLE `t1` (

`id`int (11) NOT NULL AUTO_INCREMENT

`name` varchar (20) DEFAULT NULL

PRIMARY KEY (`id`)

UNIQUE KEY `name` (`name`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

Root@test 12:37:41 > insert into T1 (name) values ('a')

At this point, check that the table structure of T1 on the main and standby database is the same, and the AUTO_INCREMENT is 2.

The code is as follows:

Root@test 12:37:51 > show create table T1\ G

* * 1. Row *

Table: t1

Create Table: CREATE TABLE `t1` (

`id`int (11) NOT NULL AUTO_INCREMENT

`name` varchar (20) DEFAULT NULL

PRIMARY KEY (`id`)

UNIQUE KEY `name` (`name`)

) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

Perform replace into operations on the main library

Root@test 12:37:58 > replace into T1 (name) values ('a')

Root@test 12:38:40 > replace into T1 (name) values ('a')

Root@test 12:38:49 > select * from T1

+-+ +

| | id | name |

+-+ +

| | 3 | a |

+-+ +

1 row in set (0.00 sec)

Check the T1 table structure in the main and standby libraries at this time, please pay attention to AUTO_INCREMENT=4.

The code is as follows:

Root@test 12:38:51 > show create table T1\\ G

* * 1. Row *

Table: t1

Create Table: CREATE TABLE `t1` (

`id`int (11) NOT NULL AUTO_INCREMENT

`name` varchar (20) DEFAULT NULL

PRIMARY KEY (`id`)

UNIQUE KEY `name` (`name`)

) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

From the table structure of T1 on the library, AUTO_INCREMENT=2

The code is as follows:

Root@test 12:39:35 > show create table T1\ G

* * 1. Row *

Table: t1

Create Table: CREATE TABLE `t1` (

`id`int (11) NOT NULL AUTO_INCREMENT

`name` varchar (20) DEFAULT NULL

PRIMARY KEY (`id`)

UNIQUE KEY `name` (`name`)

) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

Root@test 12:39:43 > select * from T1

+-+ +

| | id | name |

+-+ +

| | 3 | a |

+-+ +

1 row in set (0.00 sec)

[analysis]

The table structure of table T1 is AUTO_INCREMENT=2, while the table structure of T1 on the main library is AUTO_INCREMENT=4. Originally, when the replace operation is in the case of self-increasing primary key, the delete+insert is executed when there is a unique key conflict, but when the binlog is recorded, it is recorded as a update operation, and the update operation does not involve the modification of auto_increment. After the binlog is applied to the slave library, the auto_increment attribute of the table of the slave library remains unchanged.

Third, risk points:

If the master-slave switch occurs in the master and slave database, and the slave database becomes the original master database, what happens next according to the original business logic?

The code is as follows:

Root@test 12:40:46 > replace into T1 (name) values ('a')

Query OK, 2 rows affected (0.00 sec)

Root@test 12:40:48 > select * from T1

+-+ +

| | id | name |

+-+ +

| 2 | a |-id has changed from 3 to 2.

+-+ +

1 row in set (0.00 sec)

If the T1 table already has multiple records, after the master-slave switch, the application to write a new master library will have a primary key conflict, which will be left to the readers to test. ^ _ ^

IV. Solutions

It is recommended to use insert into table values (,....) On duplicate key update syntax structure to address business requirements. To prevent replace into from bringing primary inconsistencies to tables with self-increasing primary keys.

The code is as follows:

Root@test 01:14:28 > insert into T1 (name) values ('a')

Query OK, 1 row affected (0.00 sec)

Root@test 01:14:51 > insert into T1 (name) values ('b')

Query OK, 1 row affected (0.00 sec)

Root@test 01:14:54 > insert into T1 (name) values ('c')

Query OK, 1 row affected (0.00 sec)

Root@test 01:14:57 > select * from T1

+-+ +

| | id | name |

+-+ +

| | 1 | a |

| | 2 | b | |

| | 3 | c |

+-+ +

3 rows in set (0.00 sec)

Root@test 01:16:17 > show create table T1\ G

* * 1. Row *

Table: t1

Create Table: CREATE TABLE `t1` (

`id`int (11) NOT NULL AUTO_INCREMENT

`name` varchar (20) DEFAULT NULL

PRIMARY KEY (`id`)

UNIQUE KEY `name` (`name`)

) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

Root@test 01:16:26 > insert into tt (name) values ('a') on duplicate key update name='a'

Query OK, 1 row affected (0.00 sec)

Root@test 01:17:09 > show create table T1\ G

* * 1. Row *

Table: t1

Create Table: CREATE TABLE `t1` (

`id`int (11) NOT NULL AUTO_INCREMENT

`name` varchar (20) DEFAULT NULL

PRIMARY KEY (`id`)

UNIQUE KEY `name` (`name`)

) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

The above is the role of the replace into statement in MySQL shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.