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).
我知道排序规则是一组对字符集进行比较的规则。MySQL除了列排序之外还有表和数据库排序规则。我想知道在这三个(数据库、表和列)上排序的区别是什么。
Thanks alot.
谢谢你很多。
2 个解决方案
#1
15
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.
MySQL的字符集和排序可以解释为一个自顶向下的优先级项列表。最上面的优先级最低，最下面的优先级最高。
Order of precedence with topmost being least precedence:
最高的优先级的顺序是:
- Server collation
- 服务器排序
- Connection-specific collation
- Connection-specific排序
- Database collation
- 数据库排序规则
- Table collation
- 表排序
- Column collation
- 列排序
- Query collation (using
CASTorCONVERT) - 查询排序(使用转换或转换)
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.
服务器排序是由服务器设置的，服务器设置在my.cnf内部或从源代码构建服务器时。默认情况下，这通常是latin1或utf8，这取决于您的平台。
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.
特定于连接的排序规则是由客户机使用诸如设置名称“utf8”、“utf8_unicode_ci”之类的查询来设置的;大多数客户机不设置特定于连接的排序，因此服务器将使用它自己的默认值，如上所述。
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.
还请参考手册页字符集支持。
#2
2
In short. When you set Server collation. to UTF-8. All Databases created without defining collation will inherit it from Server.
简而言之。设置服务器排序时。utf - 8。所有没有定义排序的数据库都将从服务器继承它。
column iherits from table
table inherits from database
database inherits from server
However, you can overwrite default server at one of those points. Then everything will inherit from it.
但是，您可以在其中一个点上覆盖默认服务器。那么所有的一切都将从中继承。
#1
15
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.
MySQL的字符集和排序可以解释为一个自顶向下的优先级项列表。最上面的优先级最低，最下面的优先级最高。
Order of precedence with topmost being least precedence:
最高的优先级的顺序是:
- Server collation
- 服务器排序
- Connection-specific collation
- Connection-specific排序
- Database collation
- 数据库排序规则
- Table collation
- 表排序
- Column collation
- 列排序
- Query collation (using
CASTorCONVERT) - 查询排序(使用转换或转换)
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.
服务器排序是由服务器设置的，服务器设置在my.cnf内部或从源代码构建服务器时。默认情况下，这通常是latin1或utf8，这取决于您的平台。
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.
特定于连接的排序规则是由客户机使用诸如设置名称“utf8”、“utf8_unicode_ci”之类的查询来设置的;大多数客户机不设置特定于连接的排序，因此服务器将使用它自己的默认值，如上所述。
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.
还请参考手册页字符集支持。
#2
2
In short. When you set Server collation. to UTF-8. All Databases created without defining collation will inherit it from Server.
简而言之。设置服务器排序时。utf - 8。所有没有定义排序的数据库都将从服务器继承它。
column iherits from table
table inherits from database
database inherits from server
However, you can overwrite default server at one of those points. Then everything will inherit from it.
但是，您可以在其中一个点上覆盖默认服务器。那么所有的一切都将从中继承。