Demo data
Table structure:
The code is as follows |
Copy Code |
Mysql> DESC Demo; +-------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+----------------+ | ID | int (one) unsigned | NO | PRI | NULL | auto_increment | | site | varchar (100) | NO | MUL | | | +-------+------------------+------+-----+---------+----------------+ 2 rows in Set (0.00 sec) Data: Mysql> SELECT * FROM demo order by ID; +----+------------------------+ | ID | site | +----+------------------------+ | 1 | http://www.111cn.net | | 2 | | | 3 | http://www.111cn.net | | 4 | http://www.111cn.net | | 5 | http://www.111cn.net | +----+------------------------+ 5 rows in Set (0.00 sec) |
When you do not create a table or create INDEX permissions, you can use the following method:
If you want to delete older duplicate records, you can use the following statement:
The code is as follows |
Copy Code |
Mysql> Delete from a -> using Demo as A, demo as B -> where (a.ID > b.ID) -> and (a.site = B.site); Query OK, 2 rows affected (0.12 sec) Mysql> SELECT * FROM demo order by ID; +----+------------------------+ | ID | site | +----+------------------------+ | 1 | http://www.111cn.net | | 2 | | | 3 | http://www.111cn.net | +----+------------------------+ 3 Rows in Set (0.00 sec) |
You want to delete newer duplicate records, you can use the following statement:
The code is as follows |
Copy Code |
Mysql> Delete from a -> using Demo as A, demo as B -> WHERE (a.ID < b.id) -> and (a.site = B.site); Query OK, 2 rows affected (0.12 sec) Mysql> SELECT * FROM demo order by ID; +----+------------------------+ | ID | site | +----+------------------------+ | 2 | | | 4 | http://www.111cn.net | | 5 | http://www.111cn.net | +----+------------------------+ |
3 Rows in Set (0.00 sec)
You can use the following statement to confirm the duplicate records that will be deleted:
The code is as follows |
Copy Code |
Mysql> SELECT a.* -> from demo A, demo b -> WHERE a.id > b.ID -> and (a.site = B.site); +----+------------------------+ | ID | site | +----+------------------------+ | 1 | http://www.111cn.net | | 3 | http://www.111cn.net | +----+------------------------+ 2 rows in Set (0.00 sec) |
If you have permission to create an index, you can use the following method:
To create a unique key index on a table:
The code is as follows |
Copy Code |
Mysql> alter Ignore table demo add unique index Ukey (site); Query OK, 5 rows affected (0.46 sec) Records:5 Duplicates:2 warnings:0 Mysql> SELECT * FROM demo order by ID; +----+------------------------+ | ID | site | +----+------------------------+ | 1 | http://www.111cn.net | | 2 | | | 3 | http://www.111cn.net | +----+------------------------+ 3 Rows in Set (0.00 sec) |
After a duplicate record is deleted, you can delete the index if necessary:
The code is as follows |
Copy Code |
Mysql> ALTER TABLE demo DROP INDEX Ukey;
Query OK, 3 rows affected (0.37 sec) Records:3 duplicates:0 warnings:0 |
If you have permission to create a table, you can use the following method:
Create a new table, and then insert the data that is not duplicated in the original table into the new table:
The code is as follows |
Copy Code |
Mysql> CREATE TABLE Demo_new as SELECT * from demo group by site; Query OK, 3 rows affected (0.19 sec) Records:3 duplicates:0 warnings:0 Mysql> Show tables; +----------------+ | Tables_in_test | +----------------+ | Demo | | Demo_new | +----------------+ 2 rows in Set (0.00 sec) Mysql> SELECT * FROM demo order by ID; +----+------------------------+ | ID | site | +----+------------------------+ | 1 | http://www.111cn.net | | 2 | | | 3 | http://www.111cn.net | | 4 | http://www.111cn.net | | 5 | http://www.111cn.net | +----+------------------------+ 5 rows in Set (0.00 sec) Mysql> SELECT * from Demo_new the order by ID; +----+------------------------+ | ID | site | +----+------------------------+ | 1 | http://www.111cn.net | | 2 | | | 3 | http://www.111cn.net | +----+------------------------+ 3 Rows in Set (0.00 sec) |
Then, back up the original table and rename the new table to the current table:
code is as follows |
copy code |
mysql> Rename table demo to Demo_old, Demo_new to demo; Query OK, 0 rows affected (0.04 sec) Mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | demo | | demo_old | +----------------+ 2 rows in Set (0.00 sec) Mysql> SELECT * FROM demo order by ID; +----+------------------------+ | id | site | +----+------------------------+ | 1 | http://www.111cn.net | | 2 | | | 3 | http://www.111cn.net | +----+------------------------+ 3 Rows in Set (0.00 sec) |
Note: Tables created in this manner will lose the index information of the original table!
The code is as follows |
Copy Code |
Mysql> DESC Demo; +-------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+-------+ | ID | int (one) unsigned | NO | | 0 | | | site | varchar (100) | NO | | | | +-------+------------------+------+-----+---------+-------+ 2 rows in Set (0.00 sec) |
If you want to keep the same information as the original table, you can use the show create table demo; To view the creation statement of the original table, and then create a new table using the creation statement of the original table, and then use the INSERT ... SELECT statement to insert the data, and then rename the table.