数据库，表，列排序规则之间的区别 [英] Difference between database, table, column collation

问题描述

我知道排序规则是一组用于对字符集进行比较的规则.除了列排序规则外，MySQL还具有表和数据库排序规则.我想知道这三个排序规则(数据库，表和列)之间的区别是什么.

非常感谢.

解决方案

MySQL的字符集和排序规则可以解释为自上而下的优先项列表.最高的是最低优先级，最低的是最高优先级.

从最高到最低的优先顺序:

• 服务器整理
• 特定于连接的排序规则
• 数据库整理
• 表排序规则
• 列排序规则
• 查询归类(使用CAST或CONVERT)

服务器归类由服务器设置，它可以在my.cnf内部设置，也可以在从源代码构建服务器时设置.默认情况下，通常为latin1或utf8，具体取决于您的平台.

特定于连接的排序规则是由客户端使用诸如SET NAMES 'utf8' COLLATE 'utf8_unicode_ci';之类的查询来设置的.大多数客户端没有设置特定于连接的排序规则，因此服务器将使用如上所述的默认值.

数据库排序规则是在数据库创建期间设置的，或在以后进行更新时手动设置的.如果未指定，则它将使用下一个更高级别的排序规则，该排序规则可以是特定于连接的排序规则，也可以是服务器排序规则.

表排序规则与数据库排序规则相同，不同的是，如果保留为空白，它将使用数据库作为默认值，然后使用特定于连接的信息，最后使用服务器的排序规则.

列排序规则将表的排序规则作为默认排序规则，如果未设置排序规则，则它将跟踪链以查找要使用的排序规则，如果未设置所有其他排序规则，则在服务器处停止. /p>

查询排序规则是使用CAST或CONVERT在查询中指定的，否则将使用链中的下一个可用排序规则.除非您使用函数，否则无法进行设置.

另请参阅手册页字符集支持

I understand that collations are a set of rules for making comparisons over a character set. MySQL has table and database collations in addition to column collation. I was wondering what was the difference between a collation on these three (database, table and column).

Thanks alot.

解决方案

MySQL's character sets and collations can be interpreted as a top-down list of prioritized items. The topmost is least priority and the bottommost is most priority.

Order of precedence with topmost being least precedence:

• Server collation
• Connection-specific collation
• Database collation
• Table collation
• Column collation
• Query collation (using CAST or CONVERT)

The server collation is set by the server, which is set either inside of my.cnf or when the server was built from source code. By default, this will usually be latin1 or utf8, depending on your platform.

The connection-specific collation is set by the client using a query like SET NAMES 'utf8' COLLATE 'utf8_unicode_ci';. Most clients don't set a connection-specific collation, so the server will use its own default as explained above.

The database collation is set during database creation, or manually by updating it later. If you don't specify one, it will use the next higher-level collation, which would either be the connection-specific or the server collation.

The table collation is the same as the database collation, except if left blank, it will use the database as its default, then connection-specific, and then finally the server's collation.

The column collation uses the table's collation as its default, and if there is no collation set, it will then follow up the chain to find a collation to use, stopping at server if all of the others weren't set.

The query collation is specified in the query by using CAST or CONVERT, but otherwise will use the next available collation in the chain. There's no way to set this unless you use a function.

Please also refer to the manual page Character Set Support.

这篇关于数据库，表，列排序规则之间的区别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！