SQL character fields sort by numeric fields _mysql

This is a long time before the problem, wrote a WP plugin: WordPress voting plug-in Ludou simple Vote, because some users need a vote, so need to use a SQL to read the list of the articles sorted by score.

Ludou simple vote's vote results are stored in a custom column in the WordPress Postmeta, the score is stored in the Meta_value field, and the Meta_value field type is Longtext, If you want to sort by using the following SQL query statement directly:

Copy Code code as follows:

ORDER BY ' Meta_value '

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

Because it is in alphabetical order, so 123 ranked in front of 2, obviously not in line with our requirements, then how can we expect the number of orders in order? Here are two ways to do this.

First, the use of MySQL cast function, conversion field format

Here we convert the Meta_value field to the field decimal of the numeric type, and then sort:

Copy Code code as follows:

Order by CAST (' Meta_value ' as DECIMAL)

It is important to note that the value of the Meta_value field you want to sort must all be convertible to a number, otherwise there will be an error.

Second, use the MySQL absolute value function abs

Using the MySQL absolute value function abs, it tells MySQL to use absolute value to handle this field:

Copy Code code as follows:

Order by ABS (' Meta_value ')