How to sort SQL complex fields by Numeric Fields

This was a long time ago problem. Last year I wrote a WP plug-in: WordPress voting plug-in Ludou Simple Vote. Some users need a Vote ranking, therefore, you need to use an SQL statement to read the list of articles sorted by score.

The Ludou Simple Vote Voting score is stored in postmeta of WordPress in the form of custom columns. The score is stored in the meta_value field, while the meta_value field type is longtext, use the following SQL query statement to sort data:

Copy codeThe Code is as follows: order by 'meta _ value'

The result of sorting by score may be:
1
10
11
123
1234
2
25
253
3

Because it is in alphabetical order, 123 is at the top of 2, which obviously does not meet our requirements. How can we sort the data in the order we expected? The following two methods are described.

I. Use the cast function of MySQL to convert the Field Format

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

Copy codeThe Code is AS follows: order by cast ('meta _ value' as decimal)

Note that the values of the meta_value field to be sorted must be convertible to numbers; otherwise, errors may occur.

Ii. Use the MySQL absolute value function ABS

The MySQL absolute value function ABS tells MySQL to process this field using the absolute value:

Copy codeThe Code is as follows: order by abs ('meta _ value ')