Detailed explanation of MySQL alter ignore syntax

When I was at work today, the business side asked me this question: I have a table and I need to add a unique field, but currently there are some duplicate values ​​in this field. Is there a good solution?

When I first heard this question, to be honest, I didn't know how to do it, because I had never encountered such a requirement. If you want to set uniqueness on a repeated field, there will inevitably be data loss, because a field cannot be unique and have repeated values. So I asked him about his needs in detail, and finally learned that in this process, only one piece of duplicate data needs to be saved, and some data loss can be tolerated. The duplicate field happens to be the time field. In this case, it is only necessary to ensure that there is a record at each time point.

Hearing this, I thought of the following methods:

1. Back up the table data, use the distinct method to filter this field, and then use the join query to obtain other fields;

2. Back up the table data, then use the query table to record the parts with duplicate values, perform statistics, and then randomly retain one of the records.

In addition to these two methods, there is another method in the lower version of MySQL, which is to use the alter ignore table method. This syntax is rarely used. I conducted an experiment to test it:

[email protected]:yeyztest 23:30:51>>show create table test\G
*************************** 1. row ***************************
    Table: test
Create Table: CREATE TABLE `test` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `age` int(11) DEFAULT NULL,
 `score` int(11) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

[email protected]:test 23:38:39>>select * from test;
+----+------+-------+
| id | age | score |
+----+------+-------+
| 1 | 2 | 3 |
| 2 | 2 | 3 |
| 3 | 3 | 4 |
| 4 | 4 | 5 |
+----+------+-------+
4 rows in set (0.00 sec)

Create a table, insert duplicate records, and then add a unique index to the age field. Let's take a look at the results:

[email protected]:test 23:38:43>>alter table test add unique key uni_key(age);
ERROR 1062 (23000): Duplicate entry '2' for key 'uni_key'

[email protected]:test 23:39:04>>alter ignore table test add unique key uni_key(age);
ERROR 1062 (23000): Duplicate entry '2' for key 'uni_key'


[email protected]:test 23:39:24>>select @@old_alter_table=1;
+---------------------+
| @@old_alter_table=1 |
+---------------------+
| 0 |
+---------------------+
1 row in set (0.00 sec)


[email protected]:test 23:40:22>>set old_alter_table=1;
Query OK, 0 rows affected (0.00 sec)

[email protected]:test 23:40:36>>alter ignore table test add unique key uni_key(age);
Query OK, 4 rows affected (0.04 sec)
Records: 4 Duplicates: 1 Warnings: 0

[email protected]:test 23:40:39>>select * from test;
+----+------+-------+
| id | age | score |
+----+------+-------+
| 1 | 2 | 3 |
| 3 | 3 | 4 |
| 4 | 4 | 5 |
+----+------+-------+
3 rows in set (0.00 sec)

As you can see, we first used the traditional direct modification method. That is, the alter table test method was used. If conflicting records were found, we should use the alter ignore method. The error still remained. After querying, it was found that a parameter was missing, old_alter_table. In order to successfully remove duplicate records and add a unique index, this parameter needs to be set to 1. Finally, the result was successfully achieved.

It should be noted here that the environment for this test is MySQL5.5.19. In the MySQL5.7 environment, this test is unsuccessful and this syntax is marked as incorrect syntax. So this method can only be used in MySQL version 5.5. Interested students can test whether it can be used on version 5.6.

Let me explain one more point. The essence of alter ignore table is to create a new table. Then the age field in the new table structure is unique. Then insert it through the insert ignore syntax. If duplicate records are encountered, they are deleted directly. Therefore, when using this syntax, please pay attention to the amount of data in your table. If the amount of data is large, you need to use it with caution because its execution time may be very long.

That’s all for today.

The above is the detailed explanation of MySQL alter ignore syntax. For more information about MySQL alter ignore syntax, please pay attention to other related articles on 123WORDPRESS.COM!