How Can I Correctly Order String Numbers in MySQL?

The correct way to sort string numbers in MySQL

In the MySQL database, if strings are mistakenly treated as characters for sorting, the sorting results may be incorrect. In order to solve this problem, the string needs to be explicitly or implicitly converted to an integer type for numerical sorting.

Explicit conversion

Column values ​​can be converted explicitly to integers using the CAST() function:

<code class="language-sql">SELECT col FROM yourtable
ORDER BY CAST(col AS UNSIGNED);</code>

Implicit conversion

Implicit conversions can be performed using mathematical operations that coerce values ​​to numeric types:

<code class="language-sql">SELECT col FROM yourtable
ORDER BY col + 0;</code>

Notes on implicit conversion

MySQL’s string to number conversion considers characters from left to right:

字符串值	整数值
'1'	1
'ABC'	0
'123miles'	123
'3'	0

With the cast, the numeric representation of the string will be used for sorting, resulting in the desired ascending result.

The above is the detailed content of How Can I Correctly Order String Numbers in MySQL?. For more information, please follow other related articles on the PHP Chinese website!