How to get unique data in mysql?

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

Mysql method to obtain unique data:

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

Syntax:

select distinct 字段名 from 数据表;

The role of distinct

In mysql, the main function of the distinct keyword is to change one or more of the database tables Filter duplicate data in multiple fields and only return one piece of data to the user. Distinct can only be used in select. The principle of

distinct

distinct is used to remove duplicates. The main principle is to first group the data to be deduplicated, and then return one piece of data from each grouped group to the client. Two different situations may occur during this grouping process:

• distinct The dependent fields all contain indexes:

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

• distinct The dependent fields do not all contain indexes:

  In this case, because the index cannot satisfy the entire deduplication and grouping process, a temporary table needs to be used. MySQL first needs Put the data that meets the conditions into a temporary table, then group this part of the data in the temporary table, and then remove a piece of data from each grouped data in the temporary table. During the grouping process in the temporary table, no data will be added. The data is sorted.

Usage of distinct

The syntax for using distinct is as follows:

select distinct expression[,expression...] from tables [where conditions];

The main thing to pay attention to when using distinct is Here are a few points:

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

• If the distinct keyword When there are multiple fields later, the multiple fields will be combined and deduplicated. Only the combined values ​​of the multiple fields are equal will be deduplicated

Below we will use Some examples about distinct that are often encountered during the development process to deepen everyone's understanding of the usage of this keyword:

The database table structure and data are as shown below:

Deduplication sql for a single field:

select distinct  age from user;

Query results

age
10
20
30

Deduplication sql for multiple fields:

select distinct name,age from user;

Query results

nameage
One10
Zero20
Two20
Four30
One30

Sql to deduplicate multiple fields and find count:

select count(distinct name,age) as total from user;

Query results

total
5

Deduplicate select *

select distinct * from user;

Since * represents all fields, this sql has the same semantics as select distinct id, name, age, sign from user

Query results:

id      name    age     sign
1       One     10      梦想要有的，万一实现了呢
2       Zero    20      http://www.chaoshizhushou.com
3       Two     20      OneZeroTwoFour
4       Four    30      加油
5       One     30      学习才是硬道理
6       Four    30      一日三省吾身

Recommended tutorial: mysql Video tutorial

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