I believe that MySQL strings are familiar to everyone. MySQL string sorting often encounters some problems, such as the following:
Today, we have solved a strange problem about MySQL string sorting. The varchar type is defined in the data, and the Int type data is actually stored. sort by the following query statement:
You can sort MySQL string fields by numeric values by field * 1 or + 0.
For example:
- select * from table where 1 order by id*1 desc;
Or
- select * from table where 1 order by id+0 desc;
In addition to the preceding method, a sorting method is provided here to use find_in_set () for invincible sorting.
Attached Mysql function find_in_set () usage:
-------------------------------------------------------
FIND_IN_SET (str, strlist)
Returns a value if the str string is in the strlist consisting of N substrings, a value ranging from 1 to N is returned. A string list consists of multiple substrings separated by the character. If the first parameter is a constant string and the second parameter is of the SET column type, the FIND_IN_SET () function will be optimized to use bitwise operations! If str is not in strlist or if strlist is an empty string, the return value is 0. If any parameter is NULL, the return value is also NULL. If the first parameter contains a ",", this function will not work at all:
- mysql> SELECT FIND_IN_SET('b','a,b,c,d');
- -> 2
-
- for example:
- $sql = "select p.*, find_in_set(p.products_id,$string_hot_pid) as rank from products p where p.products_id in ($string_hot_pid) order by rank";
MySQL string addition function usage example
How to query duplicate fields in a MySQL large table
How to query duplicate records in MYSQL
How to Implement MySQL full-text Query
Implementation of MySQL random Query