Example of filtering for non-duplicate record values in MySQL

My WordPress database Wp_postmeta table has more than 10,000 lines, if the use of two cycles to find the judge, although we are not tired, but the program runs very tired AH.

If I want to query with a single statement to get all the data that name does not duplicate, then you must use distinct to remove the extra duplicate records.

Select distinct ' Meta_key ' from ' Wp_postmeta '

Query results

From the above figure can see that the query results only 24, all meta_key the same are excluded.

When using MySQL, there are times when you need to query for records that are not duplicated in a field, although MySQL provides a distinct keyword to filter out excess duplicates, but it often only uses it to return the number of records that are not duplicates, rather than using it to return all values that are not logged. The reason for this is that distinct can only return its target field, but cannot return other fields, after experiments, the following methods can be implemented.

Examples are as follows:

This is the structure of the test table.
ID test1 test2
1 a 1
2 a 2
3 a 3
4 a 1
5 B 1
6 B 2
7 B 3
8 B 2
For example, I want to use a statement query to get all the data that test1 do not duplicate, then you must use distinct to remove unnecessary duplicate records.
Select distinct test1 from test
The results obtained are:
Test1
A
B
It seems to be working, but what I want to get is the ID value? Change the query statement:
SELECT DISTINCT test1, ID from test

Test1 ID
A 1
A 2
A 3
A 4
B 5
B 6
B 7
B 8
Why didn't distinct play a role? The role is up, but he also played two fields, that is, must get ID and test1 are the same will be excluded, this is not possible, the ID is automatic growth ...

We will change the query statement:

Select ID, distinct test1 from test
Unfortunately, you can't get anything except the wrong message, distinct must be placed at the beginning. Is it hard to put distinct in the where? Can, still error ....

Through the reference manual, you can use Group_cancat to:

SELECT ID, Group_concat (DISTINCT test1) from test group by Test1
ID Group_concat (DISTINCT test1)
1 A
5 b

However, it can only be used after the 4.1.0, for those older versions of the database is not.

Can be implemented by other functions:

SELECT *, COUNT (distinct test1) from test group by Test1
ID test1 test2 count (distinct test1)
1 a 1 1
5 B 1 1

The last one is superfluous, do not need to control on the line, to achieve ...

There are also simpler ways to achieve this:

Select ID, test1 from test group by Test1
ID test1
1 A
5 b

By the way, the distinct of MySQL has a lot of useful things you can't imagine.

1. Can be used when count does not duplicate a record

For example, select COUNT (DISTINCT ID) from TableName;

is to calculate the number of records with different IDs in the Talbebname table.

2, when you need to return a record of the specific values of different IDs can be used
For example, select DISTINCT ID from tablename;
Returns the specific values of different IDs in the Talbebname table

3. The above situation 2 is ambiguous when you need to return the results of more than 2 columns in the MySQL table.

For example, select DISTINCT ID, type from tablename;

Actually returns the result that ID and type are not the same at the same time, that is, distinct two fields at the same time, must have ID and Tyoe are the same to be excluded, and we expect the result is not the same

4. You can consider using the GROUP_CONCAT function to exclude this time, but this MySQL function is supported above mysql4.1

5. In fact, there is another way to solve the problem is to use

SELECT ID, type, COUNT (DISTINCT ID) from tablename
Although the result of this return is a list of useless count data (perhaps you need this useless data I said)
The result of the return is that all the results with different IDs and 4 types above can be used in a complementary way to see what kind of data you need.