How to delete duplicate rows in mysql table and keep records with smaller (or larger) IDs

In the actual process of entering the database, if the amount of data is relatively large, it is inevitable that multiple duplicate records will be entered for some reasons. So how can we delete the duplicate rows and keep one with a larger id, or one with a larger id? What a small record.

The data table structure used in this example is as follows tdb_goods

The data in the table is repeated as shown

The first step is to use group by to find out the content with a number greater than 2 (that is, duplicate records) in each group

mysql> SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name HAVING COUN
T(goods_name)>=2;
+----------+------------------------------+
| goods_id | goods_name                   |
+----------+------------------------------+
|       20 | X3250 M4机架式服务器 2583i14            |
|       19 | 商务双肩背包                          |
+----------+------------------------------+
2 rows in set (0.01 sec)

and then use left join to connect the original table and the above query results, delete repeated records, and retain the record of smaller ID

mysql> DELETE t1 FROM tdb_goods AS t1 LEFT JOIN( SELECT goods_id,goods_name FROM
 tdb_goods GROUP BY goods_name HAVING COUNT(goods_name)>=2) AS t2 ON t1.goods_na
me = t2.goods_name WHERE t1.goods_id>t2.goods_id;
Query OK, 2 rows affected (0.06 sec)

from the above sentences. The conditions are the same Records with large goods_id. This way you can achieve the desired effect.

                                                                                      out out out out out out out to use use ’ ’’ to be used using ` JOIN ’ to be used.

The above is how mysql deletes duplicate rows in the table and retains the content of records with smaller (or larger) IDs. For more related content, please pay attention to PHP Chinese website (www.php.cn)!