SQL Sql表中的Unicode字符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4144767/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Unicode characters in Sql table
提问by Boris
I am using Sql Server 2008 R2 Enterprise. I am coding an application capable of inserting, updating, deleting and selecting records from a Sql tables. The application is making errors when it comes to the records that contain special characterssuch as ?, ? ?, ? and ?.
我正在使用 Sql Server 2008 R2 Enterprise。我正在编写一个能够从 Sql 表中插入、更新、删除和选择记录的应用程序。当涉及包含特殊字符（如 ?, ? ?, ? 和 ？。
Here's what happens:
这是发生的事情：
The command:
命令：
INSERT INTO Account (Name, Person)
VALUES ('Boris Borenovi?', 'True')
WHERE Id = '1'
inserts a new record but the Name field is Boris Borenovic, so character ?is changed to c.
插入一条新记录，但名称字段为Boris Borenovic，因此字符?更改为c。
The command:
命令：
SELECT * FROM Account
WHERE Name = 'Boris Borenovi?'
returns the correct record, so again the character ?is replaced by cand the record is returned.
返回正确的记录，因此再次?替换字符c并返回记录。
Questions:
问题：
- Is it possible to make Sql Server save the
?and other special charactersmentioned earlier? - Is it still possible, if the previous question is resolved, to make Sql be able to return the
Boris Borenovi?record even if the query asks forBoris Borenovic?
- 是否可以让 Sql Server 保存前面提到的
?和其他特殊字符？ - 如果前面的问题得到解决，是否仍有可能使 Sql 能够返回
Boris Borenovi?记录，即使查询要求Boris Borenovic？
So, when saving records I want Sql to save exactly what is given, but when retrieving the records, I want it to be able to ingnore the special characters. Thanks for all the help.
因此，在保存记录时，我希望 Sql 准确保存给定的内容，但是在检索记录时，我希望它能够忽略特殊字符。感谢所有的帮助。
回答by Damien_The_Unbeliever
1) Make sure the column is of type nvarchar rather than varchar (or nchar for char)
1) 确保该列的类型为 nvarchar 而不是 varchar（或 nchar 为 char）
2) Use N'at the start of string literals containing such strings, e.g. N'Boris Borenovi?'
2)N'在包含此类字符串的字符串文字的开头使用，例如N'Boris Borenovi?'
3) If you're using a client library (e.g. ADO.Net), it should handle Unicode text, so long as, again, the parameters are marked as being nvarchar/nchar instead of varchar/char
3) 如果您使用客户端库（例如 ADO.Net），它应该处理 Unicode 文本，只要再次将参数标记为 nvarchar/nchar 而不是 varchar/char
4) If you want to query and ignore accents, then you can add a COLLATE clause to your select. E.g.:
4）如果你想查询并忽略重音，那么你可以在你的选择中添加一个 COLLATE 子句。例如：
SELECT * FROM Account
WHERE Name = 'Boris Borenovic' COLLATE Latin1_General_CI_AI
Where _CI_AI means Case Insensitive, Accent Insensitive, should return all rows with all variants of the "c" at the end.
其中 _CI_AI 表示不区分大小写，不区分口音，应返回末尾带有“c”所有变体的所有行。
5) If the column in the table is part of a UNIQUE/PK constraint, and you need it to contain both "Boris Borenovi?" and "Boris Borenovic", then add a COLLATE clause to the column definition, but this time use a collation with "_AS" at the end, which says that it's accent sensitive.
5) 如果表中的列是 UNIQUE/PK 约束的一部分，并且您需要它同时包含“Boris Borenovi？” 和“Boris Borenovic”，然后在列定义中添加一个 COLLATE 子句，但这次使用末尾带有“_AS”的排序规则，表示它对重音敏感。
回答by Andomar
To allow SQL Server to store special characters, use nvarcharinstead of varcharfor the column type.
要允许 SQL Server 存储特殊字符，请对列类型使用nvarchar代替varchar。
When retrieving, you can force a accent-insensitve collation so that it ignores the different C's:
检索时，您可以强制进行不区分重音的排序规则，以便忽略不同的 C：
WHERE Name = 'Boris Borenovi?' COLLATE Cyrillic_General_CI_AI
Here, CI stands for Case Insensitive, and AS for Accent Insensitive.
在这里，CI 代表不区分大小写，而 AS 代表不区分口音。
回答by Taras Melnyk
I've faced with the same problem and after some researching:
我遇到了同样的问题，经过一些研究：
What is the difference between varchar and nvarchar?
I altered type of needed fields:
我改变了所需字段的类型：
ALTER TABLE [table_name] ALTER COLUMN column_name [nvarchar]
GO
And it works!
它有效！
