mysql advanced (8) how to sort varchar type
How to sort the varchar type in MySQL
asc upgrade
desc descending order
The default order by in MySQL can only sort numbers and date types, but for varchar Character type sorting seems to be useless. Now I will introduce to you how to solve the varchar type sorting problem.
I discovered an interesting problem when sorting the national telephone number table today. I wanted the isdcode field to be sorted from small to large, so I wrote it like this
SELECT * FROM gb_country_isdcode ORDER BY isdcode asc
The results are as follows, and I found that it was not the result I wanted. The asc sorting was correct, so I searched and searched and searched, and finally found the reason;
isdcode is a varcher type Yes, it is obviously not possible to use asc directly for sorting. It must be converted to int type and then it can be sorted normally. Just isdcode + 0 is enough
So I write it like this
SELECT * FROM gb_country_isdcode ORDER BY (isdcode+0) asc
It seems that the kind of data you want is relatively large. . But why is +0 good? It turns out that after +0, the INT type is converted to sorting. This way you can sort by size.
What if it is not a phone number but a Chinese character? We only need to perform a simple conversion to sort the Chinese characters.
Use order by in mysql to store Chinese information fields. The default results are not sorted in the order of Chinese pinyin. If you want to sort by the pinyin of Chinese characters, you need to set the character set of the database. to UTF8, and then force the field information to be converted to GBK when ordering by, so that the results are sorted in pinyin order. For example:
SELECT * FROM table_name ORDER BY CONVERT(column_name USING gbk);
I tried it in mysql and the result was very satisfactory.
The conclusion is: when querying, just encode the character set gb2312 used in the queried data through the convert function, and then use the Chinese sorting after convert. But if you really change the character set of the fields in the table to gb2312, it will involve a lot of encoding issues, such as page value transfer and access from the database, which is very troublesome. As long as you specify the character set when querying, it does not really change the physical field to gb2312, it is very simple.
The above is the content. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!

Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

As a PHP developer, we often encounter the need to add a like function to a website or application. This article will introduce how to design and implement a multi-article like function through advanced PHP programming, and provide specific code examples. 1. Functional requirements analysis Before designing the function of liking multiple articles, we first need to clarify our functional requirements: users can view multiple articles on the website and like each article. Users can only like each article once. Once the user has already liked the article, they cannot like it again. use

Advanced skills: Master the advanced application of Go language in crawler development Introduction: With the rapid development of the Internet, the amount of information on web pages is becoming increasingly large. To obtain useful information from web pages, you need to use crawlers. As an efficient and concise programming language, Go language is widely popular in crawler development. This article will introduce some advanced techniques of Go language in crawler development and provide specific code examples. 1. Concurrent requests When developing crawlers, we often need to request multiple pages at the same time to improve the efficiency of data acquisition. Available in Go language

Oracle data type analysis: from basics to advanced Oracle database is a powerful relational database management system that is widely used in enterprise-level application development and data storage. In Oracle database, data type is a very important concept, which defines the format and scope of data storage. This article will systematically introduce the commonly used data types in Oracle database from basic to advanced, including numeric, character, date, etc., and provide specific code examples to help readers better understand. 1. Numerical numbers

In today's era of rapid development of information technology, the choice of programming language has become increasingly important. Golang, as a modern programming language developed by Google, has risen rapidly and received widespread attention in just a few years since its birth. Golang is widely used in cloud computing, network programming, big data processing and other fields due to its simplicity and efficiency. However, for beginners, learning a new programming language often requires overcoming some difficulties. This article will start with basic knowledge and introduce in detail the learning of Golang.

Advanced Tutorial on Regular Expressions in Go Language: How to Use Backreferences Regular expressions are a powerful text matching tool that can help us quickly find and extract the information we want in strings. The Go language also provides a built-in regexp package to support the use of regular expressions. In the previous tutorials, we have learned some basic regular expression usage, such as matching strings, character sets, quantity qualifiers, etc. Now, we'll take a closer look at how to use backreferences, which can help us match more precisely

Advanced Guide to Golang's Template Package: From Beginner to Mastery Introduction Golang's Template package is a template engine built into the Go language, providing a convenient and flexible way to generate and render text output. In this article, we will start with the basics and go deeper, explaining how to use the Template package and how to optimize and extend your templates through some advanced techniques. Basic knowledge: Template syntax Before using the Template package, we need to understand its basic syntax.

Analytical overview of advanced application methods of PHP closures, generators and reflection technologies: In PHP programming, closures, generators and reflection are powerful features and technologies. They provide many advanced and flexible features to help us organize and manage our code better. This article will analyze the advanced application methods of these three technologies and provide specific code examples. 1. Advanced application methods of closures (Closures): 1.1 Use closures to implement "lazy evaluation": Closures can be used to implement the idea of "lazy evaluation", that is, defer calculations only when needed

PHP is a widely used server-side scripting language used to develop dynamic websites, web applications, and other Internet services. In the process of developing PHP applications, using functions can help simplify code, improve code reusability, and reduce development costs. This article will introduce the basic usage and advanced usage of PHP functions. 1. Basic usage of PHP functions 1. Define functions In PHP, use the function keyword to define functions, for example: functiongreet($name){
