collate数据库（mysql数据库中的collation如何修改）

本文目录

• mysql数据库中的collation如何修改
• SQLserver数据库怎么给指定的表区分大小写
• sql数据库COLLATE
• sql中的collation干什么用

mysql数据库中的collation如何修改

mysql中怎么修改collation

【问题报错】

在数据库插入数据时，varchar 类型的字段插入中文数据时报错。

报错原文：

【原因分析】

通过 show full columns from user_bean;语句查看字段的collation属性，发现字段的collation属性值是 latin1_swedish_ci ，说明字段默认为英语。未对汉语进行设置，所以当输入汉语时，mysql会报错为“字符串的值不正确”。

【解决方法】

1、mysql的配置文件 /etc/my.cnf，vi /etc/my.cnf

在 下面加入两行补充

2、重启Mysql服务 systemctl restart mysqld

向数据库插入含中文的数据，成功！

注意：上面步骤可以解决以后创建表时字段的collation属性的问题，但前面已经创建的表字段的collation属性值并不会发生变化。

如果要改变之前已经创建好的表，怎么办？

方法1：对原来的表进行修改，可以通过类似语句

alter table user_bean change address address varchar(255) character set utf8 collate utf8_general_ci not null;

方法2：删除原来的表，重新再建。

【命令总结】

SQLserver数据库怎么给指定的表区分大小写

这个 ALTER DATABASE 语句用来修改数据库的属性，比如改变它的默认排序规则（即 COLLATE）。因此，你可以使用它来设置数据库中所有表都区分大小写。

要使用这个语句，你需要先确保你有足够的权限去修改数据库。然后，你可以在 SQL Server Management Studio 中执行以下步骤：

• 打开 SQL Server Management Studio 并连接到你想要修改的数据库服务器。

• 在对象资源管理器中，右键单击你想要修改的数据库，然后选择“属性”。

• 在“数据库属性”窗口中，选择“排序规则”选项卡。

• 在“排序规则”下拉列表中，选择“Chinese_PRC_CS_AS”。

• 单击“确定”按钮以保存更改。

这样，数据库中的所有表都将区分大小写。但是，如果你只想修改某个特定的表，那么你可以在创建表时指定它的排序规则，或者使用 ALTER TABLE 语句来修改表的排序规则。

举个例子，假设你想要修改名为“TBL_ACCOUNT”的表，使其区分大小写，你可以在创建表时指定排序规则：

这个 ALTER DATABASE 语句用来修改数据库的属性，比如改变它的默认排序规则（即 COLLATE）。因此，你可以使用它来设置数据库中所有表都区分大小写。

要使用这个语句，你需要先确保你有足够的权限去修改数据库。然后，你可以在 SQL Server Management Studio 中执行以下步骤：

• 打开 SQL Server Management Studio 并连接到你想要修改的数据库服务器。

• 在对象资源管理器中，右键单击你想要修改的数据库，然后选择“属性”。

• 在“数据库属性”窗口中，选择“排序规则”选项卡。

• 在“排序规则”下拉列表中，选择“Chinese_PRC_CS_AS”。

• 单击“确定”按钮以保存更改。

这样，数据库中的所有表都将区分大小写。但是，如果你只想修改某个特定的表，那么你可以在创建表时指定它的排序规则，或者使用 ALTER TABLE 语句来修改表的排序规则。

举个例子，假设你想要修改名为“TBL_ACCOUNT”的表，使其区分大小写，你可以在创建表时指定排序规则：

CREATE TABLE TBL_ACCOUNT (
id INT PRIMARY KEY,
username VARCHAR(255) COLLATE Chinese_PRC_CS_AS,
password VARCHAR(255)
);
或者，如果你已经创建了表，你可以使用 ALTER TABLE 语句来修改表的排序规则：

ALTER TABLE TBL_ACCOUNT
ALTER COLUMN username VARCHAR(255) COLLATE Chinese_PRC_CS_AS;
这样就可以让表“TBL_ACCOUNT”区分大小写了。

sql数据库COLLATE

数据虽然完全相同，但是的两个数据库实例的COLLATE设置不同，两者不兼容。这种情况通常出现在与临时表进行连接的时候。临时表创建在tempdb数据库里，它默认是继承了SQL Server实例的COLLATE设置。如果你的用户数据库是从另外一台服务器附加上来的话，很可能是另一中COLLATE设置。两者不兼容就会出现这种错误。
解决办法：可以在创建临时表时，为文本字段指定 COLLATE DATABASE_DEFAULT， 从而明确指定从当前用户数据库继承 COLLATE 设置。

sql中的collation干什么用

排序规则（collation）

关于SQL Server的排序规则，估计大家都不陌生，在创建数据库时我们经常要选择一种排序规则（conllation），一般我们会留意到每一种语言的排序规则都有许多种，比如标准大陆简体中文Chinese_PRC的排序规则就有数十种之多
这些排序规则有什么作用呢？让我们先来看看MS官方的解释：
排序规则指定了表示每个字符的位模式。它还指定了用于排序和比较字符的规则。排序规则具有下面的特征：
语言
区分大小写
区分重音
区分假名
比如在SQL Server 2005中，排序规则名称由两部份构成，比如 Chinese_PRC_CI_AI_WS
前半部份是指本排序规则所支持的字符集，如Chinese_PRC 指针对大陆简体字UNICODE的排序规则。
后半部份即后缀的含义如下：

_BIN
指定使用向后兼容的二进制排序顺序。
_BIN2 指定使用 SQL Server 2005 中引入的码位比较语义的二进制排序顺序。
_Stroke 按笔划排序
_CI(CS) 是否区分大小写，CI不区分，CS区分
_AI(AS) 是否区分重音，AI不区分，AS区分
_KI(KS) 是否区分假名类型，KI不区分，KS区分
_WI(WS) 是否区分全半角，WI不区分，WS区分

既然排序规则如此复杂，那么应用了不同排序规则的列之间默认情况下便不能进行Union、Join、Like等equal操作了，于是便有了排序规则（collation）冲突。

排序规则（collation）冲突

我们知道，SQL Server 从2000 开始，便支持多个排序规则。SQL Server 2000 的数据库可使用除默认排序规则以外的其他排序规则。此外，SQL Server 2000 还支持为列专门制定排序规则。
这样一来，我们在写跨表、跨数据库、跨服务器操作的T-SQL时，如果equal的字段排序规则不同，便会发生排序规则冲突。
比如我们先见两个结构相同的表，但字段的排序规则不同：
-- 1. Create TableA.
CREATE TABLE TagsTableA
(
TagName NVARCHAR(64) COLLATE Chinese_PRC_BIN
)
-- 2. Create TableB.
CREATE TABLE TagsTableB
(
TagName NVARCHAR(64) COLLATE Chinese_PRC_CI_AS
)
当表建好之后执行：
-- 3. Try to join them
SELECT * from TagsTableA A INNER JOIN TagsTableB B on A.TagName = B.TagName
便会出下类似下面的问题：
无法解决 equal to 操作中 "Chinese_PRC_BIN" 和 "Chinese_PRC_CI_AS" 之间的排序规则冲突。
常见的场景——临时表

我们知道，SQL Server的临时表是保存在Tempdb数据库中的。而使用临时表的数据库与临时表的排序规则（conllation）不一定相同。所以，当Tempdb的排序规则与当前使用临时表的数据库排序规则不同时，便会出现排序规则冲突。
一般来说，我们在创建临时表时可能不会注意到排序规则，从而留下排序规则冲突的隐患。
比如Openlab V4.0的Blog模块中的一个存储过程，便有着这种隐患：
/****** 对?象ó: StoredProcedure 脚本日期: 01/20/2010 19:10:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
RETURN VALUES:
Ids
*/
-- =============================================
-- Author: 《Lance Zhang》
-- Create date: 《2010-01-06》
-- Description: 《Make sure all the tag EXISTS in DB, and then get their ids.》
-- 1. Create Temp Table.
-- 2. Insert TagNames into Temp Table.
-- 3. Add new Tags to from query Temp Table.
-- 4. Batch Get All Tag Ids from .
-- 5. Clear and drop Temp Table.
-- =============================================
ALTER PROCEDURE
(
@BlogId INT,
@TagNames XML
)
AS
BEGIN
/******************************* SET CONFIG *************************************************/
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NUMERIC_ROUNDABORT OFF
/******************************* DECLARE VARIABLE *************************************************/
/********************************BEGIN TRANSATION**********************************************/
BEGIN TRY
BEGIN TRANSACTION;
-- 1. Create Temp Table.
CREATE TABLE #TagsTable
(
TagName NVARCHAR(64)
)
-- 2. Insert TagNames into Temp Table.
INSERT INTO
#TagsTable
SELECT
TG.Tags.value(’@i’,’NVARCHAR(64)’) AS TagName
FROM
@TagNames.nodes(’/ts/t’) TG(Tags)
-- 3. Add new Tags to from query Temp Table.
BEGIN
INSERT INTO

(

,
,
,
,
,
,
,
,
,
,
,
)
SELECT
@BlogId,
0, -- ParentId, 0 as default.
2, -- CategoryType, 2 as Post Tag.
TT.TagName,
LOWER(TT.TagName),
TT.TagName, -- Slug, use CategoryName as default.
LOWER(TT.TagName), -- LoweredSlug, use LoweredCategoryName as default.
’’, -- Description, Empty as default.
GETUTCDATE(),
0, -- TotalEntities, 0 as default.
1, -- SortOrder of PostTags can always be 1.
1 -- State, 1 as Normal.
FROM
#TagsTable TT
WHERE
LOWER(TT.TagName) NOT IN
(
SELECT
C.
FROM
C WITH( UPDLOCK, HOLDLOCK )
WHERE
= @BlogId
AND = 2 -- Post Tag.
)
END
-- 4. Batch Get All Tag Ids from .
BEGIN
SELECT

FROM
C WITH(NOLOCK)
JOIN
#TagsTable TT
ON
C. = LOWER( TT.TagName )
WHERE
C. = @BlogId
AND C. = 2 -- Post Tag.
AND C. = 1 -- 1 as Normal status.
END
-- 5. Clear and drop Temp Table.
TRUNCATE TABLE
#TagsTable
DROP TABLE
#TagsTable
COMMIT TRANSACTION;
RETURN 1
END TRY
BEGIN CATCH
IF XACT_STATE() 《》 0
BEGIN
ROLLBACK TRANSACTION;
RETURN -1
END
END CATCH
END
GO
常见的解决方案

知道了什么是排序规则冲突，我们接下来分析冲突的解决方案，以数据库级别的排序规则为例，一般来说，解决方案有下面几种
把SQL实例删了重建 ——大多数情况下等于没说-_-|||
修改数据库的排序规则 ——参考阿牛兄的这篇文章
在T-SQL中使用COLLATE DATABASE_DEFAULT来解决冲突 ——接下来主要讨论这个
COLLATE DATABASE_DEFAULT

Collate XXX 操作可以用在字段定义或使用时，它会将字段定义或转换成XXX 的排序规则格式。而Collate Database_Default 则会将字段定义或转换成当前数据库的默认排序规则，从而解决冲突。
比如在下面的代码中便使用了Collate Database_Default 来解决字段在equal操作中的排序规则冲突：
Insert into Security.Report (Name)
Select C.Path From SSRS.Catalog C
Where C.Path Collate Database_Default Like @ReportPath + ’/%’
And C.Path Collate Database_Default Not In (Select Name From Security.Report R)
当然，在创建临时表时若对字段定义加上Collate Database_Default ，也可以方便地解决潜在的排序规则冲突，比如上一节中提到的存储过程，只要做如下修改即可。
-- 1. Create Temp Table.
CREATE TABLE #TagsTable
(
TagName NVARCHAR(64) COLLATE DATABASE_DEFAULT
)
结束语
***隐藏网址***