Shulou(Shulou.com)06/01 Report--

This article mainly introduces how to obtain non-repetitive data in mysql. It is very detailed and has a certain reference value. Friends who are interested must read it!

In mysql, you can add the distinct keyword to the query statement to filter duplicate records and get non-duplicate data; the syntax "select distinct field name from data table;".

Mysql's method of getting data that is not duplicated:

Add the distinct keyword to the query statement to filter duplicate records

Syntax:

Select distinct Field name from data Table

The role of distinct

In mysql, the main function of the distinct keyword is to filter the duplicate data of one or more fields in the database table and return only one piece of data to the user. Distinct can only be used in select.

The principle of distinct

The main principle of distinct for deduplication is to first group the data to be deduplicated, and then return one of each group of data after grouping to the client. Two different situations may occur in the process of this grouping:

The fields that distinct depends on all contain indexes:

In this case, mysql groups the data that meets the conditions directly by operating the index, and then removes a piece of data from each group of data after the grouping.

The fields that distinct depends on do not all contain indexes:

In this case, because the index can not meet the whole process of de-re-grouping, temporary tables are needed. Mysql first needs to put the data that meets the conditions into the temporary table, and then groups the part of data in the temporary table, and then removes a piece of data from each grouping in the temporary table, and the data will not be sorted in the process of grouping in the temporary table.

The usage of distinct

The syntax for using distinct is as follows:

Select distinct expression [, expression...] From tables [where conditions]

There are a few main points to note when using distinct:

When deduplicating fields, make sure that distinct is at the front of all fields.

If there are multiple fields after the distinct keyword, the multiple fields will be deduplicated, and only if the combined values of the multiple fields are equal will they be deduplicated.

Here are some examples of distinct that we often encounter in the development process to deepen your understanding of the usage of this keyword:

The database table structure and data are shown in the following figure:

Deduplicates a single field with sql:

Select distinct age from user

Query result

Age102030

Deduplicates multiple fields with sql:

Select distinct name,age from user

Query result

NameageOne10Zero20Two20Four30One30

Remove the duplicates of multiple fields and find the sql of count:

Select count (distinct name,age) as total from user

Query result

Total5

Remove the weight of select *

Select distinct * from user

Since * represents all fields, the sql and select distinct id,name,age,sign from user have the same semantics

Query results:

Id name age sign1 One 10 dream of having What if it comes true? 2 Zero 20 http://www.chaoshizhushou.com3 Two 20 OneZeroTwoFour4 Four 30 come on 5 One 30 learning is the last word 6 Four 30 Three provinces a day are all the contents of how to get non-repetitive data in mysql. Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.