How to sort the conversion of field types in mysql
In this issue, the editor will bring you about how to sort the field types in mysql. The article is rich in content and analyzes and describes for you from a professional point of view. I hope you can get something after reading this article.
The field server_id in the table is of type varchar. Now when we query the data, we want to sort it by server_id, and the sorted result:
Select server_id from cardserver where game_id = 1 order by server_id desc limit 10
+-+
| | server_id |
+-+
| | 8 |
| | 7 |
| | 6 |
| | 5 |
| | 4 |
| | 3 |
| | 2 |
| | 10 |
| | 1 |
+-+
Obviously, the result we want should be 10, 8, 7, 6, 5 and so on. But this 10 comes after 2. Arranged according to the string. In fact, we want to arrange it as a numerical value.
Manually convert the type:
Use the following method to sort it after server_id+0, and the problem is solved.
Select server_id from cardserver where game_id = 1 order by server_id+0 desc limit 10
+-+
| | server_id |
+-+
| | 10 |
| | 8 |
| | 7 |
| | 6 |
| | 5 |
| | 4 |
| | 3 |
| | 2 |
| | 1 |
+-+
Use the MySQL function CAST/CONVERT:
Provides us with two type conversion functions: CAST and CONVERT, how can we let go of something ready-made?
The CAST () and CONVERT () functions can be used to get a value of one type and produce a value of another type.
This type can be one of the following values:
BINARY [(N)]
CHAR [(N)]
DATE
DATETIME
DECIMAL
SIGNED [INTEGER]
TIME
UNSIGNED [INTEGER]
So we can also use CAST to solve the problem:
Select server_id from cardserver where game_id = 1 order by CAST (server_id as SIGNED) desc limit 10
+-+
| | server_id |
+-+
| | 10 |
| | 8 |
| | 7 |
| | 6 |
| | 5 |
| | 4 |
| | 3 |
| | 2 |
| | 1 |
+-+
You can also use CONVERT to fix this problem:
Select server_id from cardserver where game_id = 1 order by CONVERT (server_id,SIGNED) desc limit 10
+-+
| | server_id |
+-+
| | 10 |
| | 8 |
| | 7 |
| | 6 |
| | 5 |
| | 4 |
| | 3 |
| | 2 |
| | 1 |
+-+
The above is how to sort the field type conversion in the mysql shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.
