How to remove duplicate data in mysql

In mysql, you can use the "SELECT" statement and the "DISTINCT" keyword to perform deduplication queries and filter out duplicate data. The syntax is "SELECT DISTINCT field name FROM data table name;".

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

When using the SELECT statement to perform a simple data query in MySQL, all matching records are returned. If some fields in a table do not have unique constraints, duplicate values ​​may exist in these fields. In order to query non-duplicate data, MySQL provides the DISTINCT keyword.

The main function of the DISTINCT keyword is to filter duplicate data in one or more fields in the data table and return only one piece of data to the user.

The syntax format of the DISTINCT keyword is:

SELECT DISTINCT <字段名> FROM <表名>;

Among them, "field name" is the name of the field that needs to eliminate duplicate records. If there are multiple fields, separate them with commas. .

You need to pay attention to the following points when using the DISTINCT keyword:

• The DISTINCT keyword can only be used in a SELECT statement.

• When deduplicating one or more fields, the DISTINCT keyword must be at the front of all fields.

• If there are multiple fields after the DISTINCT keyword, the multiple fields will be combined and deduplicated. That is to say, only when the combination of multiple fields is exactly the same. will be deduplicated.

Example

The following uses a specific example to illustrate how to query non-duplicate data.

The table structure and data of the student table in the test database are as follows:

mysql> SELECT * FROM test.student;
+----+----------+------+-------+
| id | name     | age  | stuno |
+----+----------+------+-------+
|  1 | zhangsan |   18 |    23 |
|  2 | lisi     |   19 |    24 |
|  3 | wangwu   |   18 |    25 |
|  4 | zhaoliu  |   18 |    26 |
|  5 | zhangsan |   18 |    27 |
|  6 | wangwu   |   20 |    28 |
+----+----------+------+-------+
6 rows in set (0.00 sec)

The results show that there are 6 records in the student table.

The age field of the student table is deduplicated. The SQL statement and running results are as follows:

mysql> SELECT DISTINCT age FROM student;
+------+
| age  |
+------+
|   18 |
|   19 |
|   20 |
+------+
3 rows in set (0.00 sec)

The name and age fields of the student table are deduplicated. The SQL statement and running results are as follows: :

mysql> SELECT DISTINCT name,age FROM student;
+----------+------+
| name     | age  |
+----------+------+
| zhangsan |   18 |
| lisi     |   19 |
| wangwu   |   18 |
| zhaoliu  |   18 |
| wangwu   |   20 |
+----------+------+
5 rows in set (0.00 sec)

Deduplicate all fields in the student table. The SQL statements and running results are as follows:

mysql> SELECT DISTINCT * FROM student;
+----+----------+------+-------+
| id | name     | age  | stuno |
+----+----------+------+-------+
|  1 | zhangsan |   18 |    23 |
|  2 | lisi     |   19 |    24 |
|  3 | wangwu   |   18 |    25 |
|  4 | zhaoliu  |   18 |    26 |
|  5 | zhangsan |   18 |    27 |
|  6 | wangwu   |   20 |    28 |
+----+----------+------+-------+
6 rows in set (0.00 sec)

Because DISTINCT can only return its target field and cannot return other fields, so In actual situations, we often use the DISTINCT keyword to return the number of unique fields.

Query the number of records after deduplication of the name and age fields in the student table. The SQL statement and running results are as follows:

mysql> SELECT COUNT(DISTINCT name,age) FROM student;
+--------------------------+
| COUNT(DISTINCT name,age) |
+--------------------------+
|                        5 |
+--------------------------+
1 row in set (0.01 sec)

The results show that the name and age fields in the student table are deduplicated. There are 5 records after that.

[Related recommendations: mysql video tutorial]

The above is the detailed content of How to remove duplicate data in mysql. For more information, please follow other related articles on the PHP Chinese website!