Mysql select in sort implementation method by id

  • 2020-05-15 02:22:30
  • OfStack

The table structure is as follows:
mysql > select * from test;
+----+-------+
| id | name |
+----+-------+
| 1 | test1 |
| 2 | test2 |
| 3 | test3 |
| 4 | test4 |
| 5 | test5 |
+----+-------+

Perform the following SQL:
mysql > select * from test where id in(3,1,5);
+----+-------+
| id | name |
+----+-------+
| 1 | test1 |
| 3 | test3 |
| 5 | test5 |
+----+-------+
3 rows in set (0.00 sec)

This select in mysql will automatically rank the results in ascending order id,
But I want to execute "select * from test where id in(3,1,5);" The results are sorted according to the conditions in in, that is :3,1,5,

The desired results are as follows:
id name
3 test3
1 test1
5 test5

How do you write SQL in Mysql?
You can use order by charindex to solve the problem in sqlserver, but you can't see how Mysql can solve it. Ask the best for help, thanks

Thank you!

select * from a order by substring_index('3,1,2',id,1);

Try this good,ls positive solution.


order by find_in_set(id,'3,1,5')

Thank you. After testing, order by substring_index and order by find_in_set are ok

Related articles: