1. Problem description
In the table, field: chapter_number is varchar, the stored values are 1, 2, 3, 4, 5, 10, 11, 12 numbers. When sorting in positive order, the returned data is 1, 10, 11, 12, 2, 3, 4, 5
2. Solution
your_column: your field name
CAST(your_column AS SIGNED);
For example:
your_table: your table name
your_column: your field name
SELECT * FROM your_table ORDER BY CAST(your_column AS SIGNED);
Method Description
The MySQL CAST() function is used to convert values from one data type to another specific data type. The CAST() function accepts two parameters, namely the value to be converted and the data type to which the value needs to be converted.
The data type that can be converted to a given value is:
DATE: Used to convert values to DATE data type. The returned format is "YYYY-MM-DD".
DATETIME: Used to convert values to DATETIME data type. The returned format is "YYYY-MM-DD HH:MM:SS".
TIME: Used to convert values to TIME data type. The returned format is "HH:MM:SS".
CHAR: Used to convert values to CHAR data type.
SIGNED: Used to convert values to SIGNED data type.
UNSIGNED: Used to convert values to UNSIGNED data type.
BINARY: Used to convert values to BINARY data type.
usage:
CAST(input_value AS datatype)
Parameters used:
input_value – Used to specify the value to be converted.
datatype – It is used to specify the data type in which the value needs to be converted.
Return value:
After conversion, the MySQL CAST() function returns the value of the required data type.
Supported MySQL version:
MySQL 5.7
MySQL 5.6
MySQL 5.5
MySQL 5.1
MySQL 5.0
MySQL 4.1
MySQL 4.0
MySQL 3.23
Example 1: Implement the CAST() function to convert the value to a DATE data type.
SELECT CAST("2019-11-21" AS DATE);
Output：2019-11-21
Example 2: Implement the CAST() function to convert the value to a CHAR data type.
SELECT CAST(121 AS CHAR); Output：121
Example 3: Implement the CAST() function to convert the value to the SIGNED data type.
SELECT CAST(2-4 AS SIGNED); Output：-2
Example 4: Implement the CAST() function to convert the value to UNSIGNED data type.
SELECT CAST(2-4 AS UNSIGNED); Output：18446744073709551614
The above is the detailed content of MySQL's implementation method of sorting varchar type numbers. For more information about MySQL varchar number sorting, please pay attention to my other related articles!