mysql sql归类_带有归类SQL强制转换SQL Server归类介绍

mysql sql归类

SQL Server collation refers to a set of character and character encoding rules, and influences how information is stored according to the order in the data page, how data is matched by comparing two columns, and how information is arranged in the T-SQL query statement. Collate SQL follows rules applied on a table when Select, Insert, Update or Delete operations are performed against contained data. Data always follows collation constraint rules, which are configured when creating an object.

SQL Server排序规则是指一组字符和字符编码规则，它会影响如何按照数据页中的顺序存储信息，如何通过比较两列来匹配数据以及如何在T-SQL查询语句中安排信息。 对包含的数据执行“选择”，“插入”，“更新”或“删除”操作时，Collat​​e SQL遵循应用于表的规则。 数据始终遵循归类约束规则，该规则在创建对象时进行配置。

When retrieving data using a T-SQL query, collation plays a fundamental role in the execution. It matters which collation is associated with a column when ordering clause is applied to that column. For example, information in the column can be case sensitive, and lowercase letters will not be treated the same as uppercase letters. This distinction is important for data sorting in the query result set.

使用T-SQL查询检索数据时，排序规则在执行中起着基本作用。 当将ordering子句应用于该列时，与该列关联的排序规则很重要。 例如，列中的信息可能区分大小写，并且小写字母将不会与大写字母一样。 这种区别对于查询结果集中的数据排序很重要。

SQL Server installation configuration sets the default collation for the created instance (Latin1_General_CI_AI). New databases will be configured with the default collation of the SQL Server. Users can change the collation settings at the database level but not at the SQL Server level. With the assistance of the SQL Server Installation setup, collation can be changed, however, the effect of that on the current database and its tables can be unpredictable. This is not recommended since it can influence existing query execution and query result sets.

SQL Server安装配置为创建的实例（Latin1_General_CI_AI）设置默认排序规则。 新数据库将使用SQL Server的默认排序规则进行配置。 用户可以在数据库级别更改排序规则设置，但不能在SQL Server级别更改排序规则设置。 借助于SQL Server安装设置，可以更改排序规则，但是，排序规则对当前数据库及其表的影响是不可预测的。 不建议这样做，因为它会影响现有的查询执行和查询结果集。

A list of collations supported by the installed version of SQL Server can be generated using sys.fn_helpcollations() (a system DMV). SQL Server allows collations to be configured at different levels of the database engine, but, by default, every level will inherit the collation settings from the parent level. In descending order, these are:

可以使用sys.fn_helpcollat​​ions（）（系统DMV）生成SQL Server已安装版本支持的排序规则列表。 SQL Server允许在数据库引擎的不同级别上配置排序规则，但是默认情况下，每个级别都将从父级别继承排序规则设置。 按降序排列，它们是：

• SQL Server instance
  SQL Server实例
• Database
  数据库
• Column
  柱
• Query
  询问

SQL Server实例级别排序规则 (SQL Server instance level collation)

A SQL Server instance collation is the default collation for the system databases. It includes master, tempdb and model. If a user’s database collation is configured differently than one of the SQL Server instance collation, then a comparison between tables in that database and in tempdb, when it comes to textual data comparison, will return an error. Tempdb will always be configured with the instance level default collation. If one of the user databases is restored with other than tempdb’s collation and the temp table is used in any T-SQL statement or procedure inside a user database that compares text-based columns using WHERE clause or JOIN conditions, the query will return an error because both of the tables do not have the same collation.

SQL Server实例排序规则是系统数据库的默认排序规则。 它包括master，tempdb和model。 如果用户的数据库排序规则的配置与SQL Server实例排序规则之一的配置不同，则涉及文本数据比较时，该数据库与tempdb中的表之间的比较将返回错误。 Tempdb将始终使用实例级别的默认排序规则进行配置。 如果使用除tempdb的排序规则以外的其他方式还原了一个用户数据库，并且在使用WHERE子句或JOIN条件比较基于文本的列的用户数据库中的任何T-SQL语句或过程中使用了temp表，则查询将返回错误因为两个表没有相同的排序规则。

To find the SQL Server instance level collation use, the T-SQL shown below:

要查找SQL Server实例级别的排序规则用法，请如下所示的T-SQL：

SELECT SERVERPROPERTY('collation') AS ServerCollation;

To handle a collation violation, a developer can make a small change in the T-SQL by defining collation on a column. This will be explained in the “Query level collation” section.

为了处理违反排序规则的问题，开发人员可以通过在列上定义排序规则来对T-SQL进行一些小的更改。 这将在“查询级别归类”部分中说明。

数据库整理 (Database collation)

Default database collation is inherited from SQL Server instance collation and used for database restoration. Collation is copied in backup when the backup is created. To find the current database collation, use the T-SQL shown below:

默认数据库排序规则继承自SQL Server实例排序规则，并用于数据库还原。 创建备份后，将在备份中复制排序规则。 要查找当前的数据库排序规则，请使用如下所示的T-SQL：

SELECT name, collation_name DbCollation
FROM sys.databases

If the default collation of a database is changed by a user, then the collation of existing, user-defined table-columns won’t be changed. However, new tables will be characterized by the changed database collation. But, if tables with various collations exist in the database, there can be errors while creating a join between them. To apply new collation to existing tables of a database, the user can set new collation on TEXT columns of tables. This activity will place a lock on the tables because the column will be altered with the new collation. Therefore, the existing column index will be dropped and re-created automatically.

如果用户更改了数据库的默认排序规则，则不会更改现有的用户定义表列的排序规则。 但是，新表将以更改的数据库排序规则为特征。 但是，如果数据库中存在具有各种排序规则的表，则在它们之间创建联接时可能会出错。 要将新的排序规则应用于数据库的现有表，用户可以在表的TEXT列上设置新的排序规则。 此活动将在表上放置一个锁，因为该列将使用新的排序规则进行更改。 因此，现有的列索引将被删除并自动重新创建。

列级排序规则 (Column level collation)

The default collation for SQL Server user-defined column is inherited from the database configured collation. If the database collation is changed, then the existing columns will retain the original collation but new columns will be created with current database collation.

SQL Server用户定义列的默认排序规则是从数据库配置的排序规则继承的。 如果更改了数据库排序规则，则现有列将保留原始排序规则，但将使用当前数据库排序规则创建新列。

This T-SQL can help find the collation for a particular column:

此T-SQL可以帮助查找特定列的排序规则：

SELECT OBJECT_NAME(OBJECT_ID), name ColumnName, collation_name AS ColumnCollation
FROM sys.columns
WHERE collation_name IS NOT NULL
AND OBJECT_NAME(OBJECT_ID) = '?'
AND name = '?'

The image below shows collation type as a column property when navigated through Object explorer and displayed from the Property option of the context menu:

下图通过对象资源管理器浏览并从上下文菜单的“ 属性”选项中显示时，归类类型作为列属性显示：

查询级别整理 (Query level collation)

SQL Server doesn’t support automatic collation casting when executing a query with a comparison of two columns with different collations. As mentioned above, collation is consistent with parent levels. In these situations with various UNICODE characters, COLLATE casting should be utilized.

当执行具有不同归类的两列的比较的查询时，SQL Server不支持自动归类转换。 如上所述，排序规则与父级一致。 在这些情况下，使用各种UNICODE字符，应使用COLLATE强制转换。

Query level collation is useful when a user wants to sidestep the collation of the column in a specific T-SQL query statement. It is possible to collate SQL by adding a collation name with the COLLATE keyword in the expression. The COLLATE is a column collation casting keyword which is operation generally known as collate SQL. COLLATE can also be used with the column name and input character string.

当用户想回避特定T-SQL查询语句中列的排序规则时，查询级别排序规则很有用。 通过在表达式中添加带有COLLATE关键字的排序规则名称，可以对SQL进行排序。 COLLATE是列归类强制转换关键字，通常称为操作归类SQL。 COLLATE也可以与列名和输入字符串一起使用。

WHERE [Column X] = [Column Y] COLLATE Latin1_General_CI_AS
or
ON [Column X] = [Column X] COLLATE Latin1_General_CS_AS

A common use of the query level collation (collate SQL) is to compare case-sensitive strings. For example, imagine two tables with similar collation and compare their columns using join or subquery. Data with the lower-case string exists in the chosen columns of those tables. The requirement is to compare both columns in case-sensitive terms, but both columns don’t have the same collation. Collation at the query level (collate SQL) will allow overriding the database or column-level collation when running a query.

查询级别排序规则（collat​​e SQL）的常见用法是比较区分大小写的字符串。 例如，假设两个表具有相似的排序规则，然后使用join或subquery比较它们的列。 这些表的所选列中存在带有小写字符串的数据。 要求以区分大小写的方式比较两列，但两列没有相同的排序规则。 查询级别（排序SQL）的排序规则将允许在运行查询时覆盖数据库或列级别的排序规则。

整理冲突 (Collation conflicts)

SQL Server supports multiple collations but cannot properly deal with collation mismatches. If a user tries to compare fields with different collations, SQL Server will return an error with collation names as shown below:

SQL Server支持多个排序规则，但不能正确处理排序规则不匹配。 如果用户尝试比较具有不同归类的字段，SQL Server将返回一个错误，其中包含归类名称，如下所示：

Msg 468, Level 16, State 9, Line 9 Cannot resolve the collation conflict between “Latin1_General_CI_AI” and “SQL_Latin1_General_CP1_CI_AS” in the like operation.

消息468，级别16，状态9，行9无法通过类似操作解决“ Latin1_General_CI_AI”和“ SQL_Latin1_General_CP1_CI_AS”之间的排序规则冲突。

To avoid conflicts, users can add a default collation setting in the T-SQL query statement:

为了避免冲突，用户可以在T-SQL查询语句中添加默认排序规则设置：

COLLATE DATABASE_DEFAULT
WHERE Column1 COLLATE DATABASE_DEFAULT = Column2

This may prompt an issue when programming for SQL Server. Users can resolve this issue by utilizing a SQL Server column compare clause with COLLATE as described above. In a situation where SQL Server is recently installed and an inappropriate collation setting is identified, it can cause an issue with the utilization of tempdb. As previously mentioned, tempdb is made with default server-level collation when restarting the SQL Server service.

为SQL Server编程时，这可能会提示问题。 用户可以通过如上所述使用带有COLLATESQL Server列compare子句来解决此问题。 在最近安装SQL Server并标识了不正确的排序规则设置的情况下，它可能导致tempdb的使用出现问题。 如前所述，tempdb是在重新启动SQL Server服务时使用默认服务器级别的排序规则创建的。

在T-SQL JOIN中使用整理SQL (Use collate SQL in T-SQL JOIN)

COLLATE SQL is a very effective column casting function in the SQL Server but it can cause query performance issues. COLLATE SQL can help a developer with a quick fix for a collation error on a Remote Query execution. Imagine a table with a column collation of Latin1_General_CS_AS and another table with a column that has a different collation. When joining the two tables based on columns with the differing collations:

COLLATE SQL是SQL Server中非常有效的列转换功能，但它可能导致查询性能问题。 COLLATE SQL可以帮助开发人员快速解决远程查询执行中的排序规则错误。 想象一下一个表的列排序规则为Latin1_General_CS_AS，而另一个表的列排序规则为不同。 在基于具有不同归类的列的两个表联接时：

SELECT *
FROM TABLE1
INNER JOIN TABLE2 ON TABLE1.Col1 COLLATE Latin1_General_CS_AS = TABLE2.Col1 COLLATE Latin1_General_CS_AS

Here, we have utilized COLLATE with both the columns in a JOIN articulation to make a common collation to avoid an error. In summary, the COLLATE keyword with its collation value as argument is used next to a column name when ad hoc collation resolution is required.

在这里，我们利用JOATE关节中的两个列使用COLLATE进行通用排序规则以避免错误。 总之，当需要临时排序规则解析时，将在其列名旁边使用以排序规则值为参数的COLLATE关键字。

SQL Server collation is a major aspect of the database engine. A mistake in collation configuration during SQL Server installation can cause huge issues during query execution. Although there is a workaround to resolve issues with collate SQL casting, it is always recommended that for a new SQL Server set up, server migration or database migration, collation should be at the top of the activity checklist. The choice of collation should be carefully thought through and consider future integration and implementation needs. It’s especially important in the event that you’ll need to join tables with other databases.

SQL Server排序规则是数据库引擎的主要方面。 SQL Server安装过程中排序规则配置中的错误可能会在查询执行过程中引起巨大的问题。 尽管有解决方法来解决归类SQL强制转换问题，但始终建议对于新SQL Server设置（服务器迁移或数据库迁移），应将归类放在活动清单的顶部。 应仔细考虑排序规则的选择，并考虑将来的集成和实施需求。 在需要将表与其他数据库连接的情况下，这一点尤其重要。

翻译自: https://www.sqlshack.com/sql-server-collation-introduction-with-collate-sql-casting/

mysql sql归类