Sorts a character field in the MySQL data table, formatted as a single-digit + sequential number. The number does not have a leading zero and the length is not fixed.
This sequence of numbers with letters, sorted out, is not the same as the result we want, because it is not a pure number and can only be ranked by character rules.
The result is a1,a10,a11,a12 .... A19,a2,a20,a21 ....
Rather than the a1,a2,a3 that we usually feel, ... A10,A11, such a result.
There are two ways to get the right sort.
One is to change the structure of the field content, with zeros in front of the numbers, so that all field values have the same length.
The second is not to change the contents of the field, in the sort of SQL statement to find a way.
Order BY mid (column,2,10) +1
Mid () The first argument is a string content, which can be a field name. The second argument is the position of the starting character, and I need to start with the second character here. The third parameter is the length that needs to be obtained, to choose a value that can contain the length of all field content, rather than small.
But this is just a "string of digital content", sorting it still uses the character rules, will not get the results we want.
Then we need to add a value to this result, let MySQL do an arithmetic operation, and then get an integer value, then the order is the result of the number sequence.
SELECT * from Equipmentmes ORDER by MID (eqid,2,10) +1
MySQL database alphanumeric mixed field sorting problem