Shulou(Shulou.com)05/31 Report--

This article is about how mysql removes duplicate data. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

In mysql, you can use "SELECT" statements and "DISTINCT" keywords to remove repeated queries, filter out duplicate data, and use the syntax "SELECT DISTINCT field name FROM data table name;".

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

When you perform a simple data query using the SELECT statement in MySQL, all matching records are returned. If some fields in the table do not have uniqueness constraints, then there may be duplicate values for those fields. In order to query non-duplicated data, MySQL provides the DISTINCT keyword.

The main function of the DISTINCT keyword is to filter the duplicate data of 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

Where "Field name" is the name of the field that needs to eliminate duplicate records, and multiple fields are separated by commas.

Note the following when using the DISTINCT keyword:

The DISTINCT keyword can only be used in SELECT statements.

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

If there are multiple fields after the DISTINCT keyword, multiple fields will be deduplicated by combining them, that is, they will be deduplicated only if the multiple fields are exactly the same.

Example

The following is a concrete example to illustrate how to implement the query without duplicating data.

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

Mysql > SELECT * FROM test.student +-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 de-duplicated below. The SQL statement and the running result 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 de-duplicated, and the SQL statement and run result 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)

All the fields in the student table are deduplicated, and the SQL statement and run result are as follows:

Mysql > SELECT DISTINCT * FROM student +-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, but not other fields, in practice, we often use the DISTINCT keyword to return the number of fields that are not repeated.

Query the number of records in the student table after the name and age fields are deduplicated, the SQL statement and the running result 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 there are five records in the student table after the name and age fields are deduplicated.

Thank you for reading! This is the end of the article on "how to remove duplicate data from mysql". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.