SQL character type field by numeric field sorting method

This has been a problem for a long time. Last year, we wrote an WP plugin: WordPress voting plugin Ludou Simple Vote. Since some users need a poll leader, we need an SQL to read the list of articles sorted by scores.

The Ludou Simple Vote vote score is stored in postmeta of WordPress in the form of a custom column. The score is stored in meta_value field. The meta_value field type is longtext.

 ORDER BY `meta_value` 

Then the results sorted by score might be:
1
10
11
123
1234
2
25
253
3

Since it is arranged in alphabetical order, 123 comes before 2, which obviously does not meet our requirements. So how can we sort it according to our expected numerical order? Here are two approaches.

1. The cast function of MySQL is adopted to convert the field format

Here we convert the meta_value field to the DECIMAL field of numeric type, and then sort it:

 ORDER BY CAST(`meta_value` AS DECIMAL) 

It is important to note that the meta_value field you are sorting must have values that can be converted to Numbers, otherwise an error will occur.

2. Use MySQL absolute value function ABS

Use the MySQL absolute value function ABS, which tells MySQL to handle the field with the absolute value:

ORDER BY ABS(`meta_value`)