Shulou(Shulou.com)05/31 Report--

This article mainly explains "what are the sorting rules and character sets in SQL Server and MySQL". The content in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's ideas to study and learn "what are the sorting rules and character sets in SQL Server and MySQL".

Character set & collation

A character set is a set of character codes for different languages, such as UTF-8 character set, GBK character set, GB2312 character set and so on. Different character sets use different rules to encode characters. The sorting rule is a specific character sorting method based on a specific character set. The sorting rule is based on the character set and is a division of the character set in the sorting mode dimension.

Sorting rules depend on the character set, a character set can have multiple sorting rules, but a kind of sorting rule can only be based on a certain character set, such as Chinese character set, that is, Chinese characters. You can sort by "Pinyin", "sort by last name stroke" and so on. For English, there is no "pinyin" and "surname strokes", but it can be divided into case-sensitive, case-insensitive, and so on, while other languages have their own specific sorting rules.

In SQL Server, a database of any character set can store characters of any language.

This is not to say that the data of the Latin character set cannot be stored in Chinese, and the database of the Chinese (Chinese) character set cannot store Mongolian (as long as the operating system itself supports it). In sqlserver, a database (or field) of any character set (actually a collation) can use nvarchar (or nchar), while nvarchar (or nchar) can store any non-Unicode character. As for collation, it is artificially defined according to the different collations supported by different character sets.

Character sets and collations in SQL Server

The collation simply specifies the sorting (comparison) rules for the stored data, in other words, the collation already contains information about the character set. Therefore, in sqlserver, you don't need to care about the character set, you only need to care about the collation. In sqlserver, you can only specify the collation (not the character set directly). For example, if you take a screenshot, you can only specify collation, that is, the character set.

Character sets and collations in MySQL

As mentioned above, collation is dependent on a character set. A character set can have multiple collations, but a collation can only be based on one character set. The following is the correspondence between the collation and the character set in MySQL.

MySQL's syntax for building a database is ridiculous. You can specify the character set and collation. If the specified collation is below the character set, there will be no problem. If the specified collation is not below the character set, an error will be reported.

For example, the collation utf8_bin belongs to the collation under the character set utf8, and there is no problem with the execution of this statement.

Create database test_database2 charset utf8 collate utf8_bin

For example, the following sentence, collation latin1_bin is not a sort of collation under the character set utf8, this statement execution will report an error

Create database test_database2 charset utf8 collate latin1_bin

These are some basic applications of character sets and collations in sqlserver and MySQL, and then talk about the differences between commonly used collations

Characteristics of common collations of * * _ genera_ci & * * _ genera_cs & * * _ bin

The above are three kinds of sorting rules commonly used in a certain character set. Let's take the common utf8 as an example.

Utf8_genera_ci is not case-sensitive, and ci is an abbreviation for case insensitive, that is, case-insensitive

Utf8_general_cs is case-sensitive, and cs is the abbreviation of case sensitive, that is, case-sensitive. However, sorting rules similar to * * _ genera_cs are no longer supported in MySQL versions, and utf8_bin is directly used instead.

Utf8_bin stores each character in a string in binary data, case-sensitive.

So, also case-sensitive, what's the difference between utf8_general_cs and utf8_bin?

Cs stands for case sensitive, which is case-sensitive; bin means binary, that is, binary coding comparison.

Under the utf8_general_cs collation, even if it is case-sensitive, some Western European characters are not distinguished from Latin characters, such as ä = a, but sometimes they do not need ä = a, which is why there is utf8_bin.

The characteristic of utf8_bin is that it uses the binary encoding of characters for operation, and any different binary coding is different, so under the utf8_bin collation: ä a

In the case of utf8_genera_ci, ar = a

Under the utf8_bin collation, Aa, ä a

So if you want to be case-sensitive, use utf8_bin directly if you have any special needs (in fact, * _ general_cs is not supported in MySQL, but is supported in SQL Server). The characteristics and usage of the above character sets are similar in SQL Server.

Thank you for reading, the above is "what are the sorting rules and character sets in SQL Server and MySQL". After the study of this article, I believe you have a deeper understanding of what the sorting rules and character sets in SQL Server and MySQL are, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.