Varchar sorting in MySQL _ MySQL

Sorting varchar types in MySQL bitsCN.com

Varchar sorting in MySQL

During today's development, we encountered the following problem:

In the database table, desc sorts varchar values. However, strange phenomena have emerged .. The data in the table is not sorted from the bottom of the height .. I feel a little tearful .. It's good to go through expert guidance. So I 'd like to share it with you .. I hope that the next mysql beginner will not be like me .. Let's talk about the code ..

For example, there is a T_TESTDEMO table with two DEMOID and DEMONAME fields. DEMOID is of the int type, DEMONAME is of the VARCHAR type, but it is stored as data of the numerical type. The data in it is as follows:

1, 222222"

2, 33333333"

3, 111111"

4, 2222"

5, "99999"

6, 8888"

7, 11111"

8, "777"

Now we have a requirement to sort the data in DEMONAME in a descending order. it is very easy to read, so we are proud to write the following SQL:

SELECT * FROM T_TESTDEMO ORDER BY DEMONAME DESC 

However, the sorting result is:

5, "99999"

6, 8888"

8, "777"

2, 33333333"

1, 222222"

4, 2222"

3, 111111"

7, 11111"

It's not the kind of sort by size that you want. it's just a bit Petrochemical. So here's the focus:

What if the following SQL statement is used?

SELECT * FROM T_TESTDEMO  ORDER BY (DEMONAME + 0) DESC ; 

The sorting result is:

2, 33333333"

1, 222222"

3, 111111"

5, "99999"

7, 11111"

6, 8888"

4, 2222"

8, "777"

Yes. It seems that the data you want is relatively small .. But why is it better to add 0?

It turns out that after + 0, INT type sorting is converted. In this way, you can sort by size ..

BitsCN.com