mysql在批量查詢時,通過in查詢的結果和in中的值的順序並不一致,對此mysql也有針對in的排序方式。具體查詢如下:
第一種方式,order by field,按字段查詢,示例:
select * from table where id in (5,3,6,1) order by field(id,5,3,6,1);
排序過程:把選出的記錄的 id 在 FIELD 列表中進行查找,並返回位置,以位置作為排序依據。
注意:這樣的用法,會導致 Using filesort,是效率很低的排序方式。除非數據變化頻率很低,或者有長時間的緩存,否則不建議用這樣的方式排序。
第二種方式,order by substring_index,此排序用到了substring_index(str,delim,count)函數,截斷in中id字符串進行排序,示例:
select * from table where id in (51,3,6,5) order by substring_index(concat(',',replace('51,3,6,5',',',',,'),','),concat(',',id,','),1);
排序過程:返回字符串str中在第count個出現的分隔符delim之前的子串。如果count是一個正數,返回從最後的(從左邊開始計數)分隔符到左邊所有字符。如果 count 是負數,返回從最後的(從右邊開始計數)分隔符到右邊所有字符。最後查詢出substring_index結果後,對此進行排序。
注意:由於截取方式的原因,如果後面字符與前面字符處於包含關系例如上述例子中的5和51,這樣會導致截取出你不想要的結果(把51拆分,而不是拆分逗號)從而使排序出錯,所以在上面例子中加入替換方法,把str中的 ',' 號替換為',,' ,然後左右兩側加上','然後在處理後的這個字符串中 用 ','+ID',' 來進行截斷,就不會出現截取錯誤的情況了。
第三種方式,order by find_in_set,此排序用到了find_in_set(str,strlist)函數,根據str在strlist中的位置分配由1到N的數值進行排序,示例:
select * from table where id in (5,3,6,1) order by find_in_set(id,'5,3,6,1');
排序過程:字符串str在由N子鏈組成的字符串列表strlist 中,則返回值的范圍在1到N之間 。一個字符串列表就是一個由一些被‘,’符號分開的自鏈組成的字符串,最後根據返回字符串在對應的strlist中的key值進行排序。
注意:因為函數主要是用‘,’區分,所以排序字符中不能有‘,’。
表結構如下:
mysql> select * from test;
+----+-------+
| id | name |
+----+-------+
| 1 | test1 |
| 2 | test2 |
| 3 | test3 |
| 4 | test4 |
| 5 | test5 |
+----+-------+
執行以下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)
這個select在mysql中得結果會自動按照id升序排列,
但是我想執行"select * from test where id in(3,1,5);"的結果按照in中得條件排序,即:3,1,5,
想得到的結果如下:
id name
3 test3
1 test1
5 test5
請問在這樣的SQL在Mysql中怎麼寫?
網上查到sqlserver中可以用order by charindex解決,但是沒看到Mysql怎麼解決??請高手幫忙,謝
謝!
select * from a order by substring_index('3,1,2',id,1);
試下這個good,ls正解。
order by find_in_set(id,'3,1,5')
謝謝,經測試order by substring_index和order by find_in_set都可以