MySQL Analysis on the Problem of numerical sorting in strings, mysql string

MySQL Analysis on the Problem of numerical sorting in strings, mysql string

This article describes how to sort numbers in strings in MySQL. We will share this with you for your reference. The details are as follows:

MySQL strings are familiar to everyone. Some problems are often encountered during MySQL string sorting, 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:
Copy codeThe Code is 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 to leave valuable comments for common progress?