Thoughts on primary key conflicts during MySQL UPDATE

Assume there is a table with the following structure:

Mysql> create table 'A '( 'Id' int (10) unsigned not null AUTO_INCREMENT, 'Id2' int (10) unsigned not null default '0 ', Primary key ('id ') ) ENGINE = MyISAM;

This table has only 6 records, as shown below:

Mysql> select * from; + ---- + --------- + | Id | city_id | + ---- + --------- + | 2 | 2 | | 3 | 3 | | 5 | 5 | | 4 | 4 | | 6 | 6 | | 7 | 7 | + ---- + --------- +

Now you want to separate the id field-1 and execute the following statement to get an error:

Mysql> update a set id = id-1; ERROR 1062 (23000): Duplicate entry '4' for key 'primary'

Check the updated results and you can see:

Mysql> select * from; + ---- + --------- + | Id | city_id | + ---- + --------- + | 1 | 2 | | 2 | 3 | | 5 | 5 | | 4 | 4 | | 6 | 6 | | 7 | 7 | + ---- + --------- +

The first two records are successfully updated, and the subsequent records fail. If the third record is to be updated, a primary key conflict occurs.

At this time, if order by is added during the update, the update will be successful.

Mysql> update a set id = id-1 order by id; Query OK, 6 rows affected (0.00 sec) Rows matched: 6 Changed: 6 Warnings: 0

Next, let's take a look at the result of converting it into an innodb table.

Mysql> alter table a engine = innodb; Query OK, 6 rows affected (0.01 sec) Records: 6 Duplicates: 0 Warnings: 0

Mysql> select * from; + ---- + --------- + | Id | city_id | + ---- + --------- + | 2 | 2 | | 3 | 3 | | 4 | 4 | | 5 | 5 | | 6 | 6 | | 7 | 7 | + ---- + --------- +

The row data is displayed in the id order.

• 2 pages in total:
• Previous Page
• 1
• 2
• Next Page