数据库、表、列排序规则的区别 [英] Difference between database, table, column collation
问题描述
我了解排序规则是一组用于对字符集进行比较的规则.MySQL/MariaDB 除了列排序规则外，还有表和数据库排序规则.我想知道这三个(数据库、表和列)的排序规则有什么区别.
I understand that collations are a set of rules for making comparisons over a character set. MySQL / MariaDB 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).
谢谢.
推荐答案
MySQL 的字符集和排序规则可以解释为自上而下的优先项列表.最高优先级最低，最低优先级最高.
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:
- 服务器排序
- 特定于连接的排序规则
- 数据库排序规则
- 表格排序规则
- 列排序规则
- 查询排序规则(使用
CAST或CONVERT)
server collation 由服务器设置，该设置可以在 my.cnf 内部设置，也可以在从源代码构建服务器时设置.默认情况下，这通常是 latin1 或 utf8，具体取决于您的平台.
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.
特定于连接的排序规则由客户端使用 SET NAMES 'utf8' COLLATE 'utf8_unicode_ci'; 之类的查询设置.大多数客户端不设置特定于连接的排序规则，因此服务器将使用自己的默认值，如上所述.
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.
column collation 使用表的 collation 作为其默认值，如果没有 collation 集，它将沿着链查找要使用的 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.
查询排序规则 在查询中使用 CAST 或 CONVERT 指定，否则将使用链中的下一个可用排序规则.除非您使用函数，否则无法设置它.
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.
另请参阅手册页字符集支持.
这篇关于数据库、表、列排序规则的区别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
