Mysql analysis on the problem of numeric ordering in strings _mysql

This example describes MySQL's problem with sorting numbers in strings. Share to everyone for your reference, specific as follows:

MySQL String trust everyone is not unfamiliar, in the MySQL string sorting often encounter some problems, such as the following to introduce the

Solved today a very strange question about the MySQL string sort, in the data definition is the varchar type, actually holds is the int type data, click the query statement to sort:
*1 a field or +0 to sort the MySQL string field by numeric value

Such as:

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 above methods, here is a sorting method, using Find_in_set () for the invincible sort

The use of the MySQL function Find_in_set () is attached:

Find_in_set (Str,strlist)

Returns a value if string str is in a list of n substrings, Strlist returns an 1 to N. A list of strings is made up of multiple substrings separated by the character ",". If the first argument is a constant string, and the second argument is a set column type, the Find_in_set () function is optimized to use bit operations! If STR is not strlist or if Strlist is an empty string, the return value is 0. If any one argument is null, the return value is also null. If the first argument contains a "," This function will not work at all:

mysql> SELECT find_in_set (' B ', ' a,b,c,d ');
-> 2

For example:

Copy Code code as follows:

$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 ";

Do you have any good ideas and suggestions can leave valuable ideas for common progress

More information about MySQL interested readers can view the site topics: "MySQL Log operation skills Daquan", "MySQL Transaction operation skills Summary", "MySQL stored process skills encyclopedia", "MySQL database lock related skills summary" and "MySQL commonly used function large summary"

I hope this article will help you with the MySQL database meter.