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

Source: Internet
Author: User

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?

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.