关于SQL Server数据表中乱码的情况,在涉及不同语言版本的SQL交互中容易碰到,有时间测试了一下,并放上结果与大家讨论。 --[测试一]![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) create database latin_db collate Latin1_General_BIN go![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) use latin_db go![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) set nocount on![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) create table t1 ( id int, notes nchar(5) ) go![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) insert into t1 values (1,'abcde') --ok insert into t1 values (2,'abcdef') --string or binary data would be truncated. the statement has been terminated. insert into t1 values (3,'上海多美n') --ok insert into t1 values (4,'上海多美好') --ok insert into t1 values (5,'上海多美好d') --string or binary data would be truncated. the statement has been terminated. insert into t1 values (6,N'上海多美好') --ok![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) select * from t1![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/ExpandedBlockStart.gif) /**//* id notes ----------- ----- 1 abcde 3 ????n --非unicode下 这部分汉字在db code page没有匹配 随后以?(单字节)替代 4 ????? 6 上海多美好 */![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) create table t2 ( id int, notes char(11) ) go![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) insert into t2 values (1,'abcdeabcdef') --ok insert into t2 values (2,'abcdeabcdefg') --string or binary data would be truncated. the statement has been terminated. insert into t2 values (3,'上海多美好上海多美好好') --ok insert into t2 values (4,'上海多美好上海多美好d') --ok insert into t2 values (5,'上海多美好上海多美好好好') --string or binary data would be truncated. the statement has been terminated. insert into t2 values (6,N'上海多美好上海多美好好') --ok![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) select * from t2![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/ExpandedBlockStart.gif) /**//* id notes ----------- ----------- 1 abcdeabcdef 3 ??????????? --非unicode下 汉字仍无法匹配 4 ??????????d 6 ??????????? --汉字以unicode在db code page中得以解析 但存储的过程中发生困难 */![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) --[测试二]![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) create database chinese_db collate Chinese_PRC_BIN go![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) use chinese_db go![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) set nocount on![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) create table t1 ( id int, notes nchar(5) ) go![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) insert into t1 values (1,'abcde') --ok insert into t1 values (2,'abcdef') --string or binary data would be truncated. the statement has been terminated. insert into t1 values (3,'上海多美n') --ok insert into t1 values (4,'上海多美好') --ok insert into t1 values (5,'上海多美好d') --string or binary data would be truncated. the statement has been terminated. insert into t1 values (6,N'上海多美好') --ok![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) select * from t1![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/ExpandedBlockStart.gif) /**//* id notes ----------- ----- 1 abcde 3 上海多美n 4 上海多美好 --与测试一结果不同,非unicode编码能在db code page中被解析 6 上海多美好 */![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) create table t2 ( id int, notes char(11) ) go![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) insert into t2 values (1,'abcdeabcdef') --ok insert into t2 values (2,'abcdeabcdefg') --string or binary data would be truncated. the statement has been terminated. insert into t2 values (3,'上海多美好上海多美好好') --string or binary data would be truncated. the statement has been terminated. insert into t2 values (4,'上海多美好上海多美好d') --string or binary data would be truncated. the statement has been terminated. insert into t2 values (5,'上海多美') --ok insert into t2 values (6,'上海多美好') --ok insert into t2 values (7,'上海多美好上') --string or binary data would be truncated. the statement has been terminated. insert into t2 values (8,N'上海多美好') --ok insert into t2 values (9,N'上海多美好上') --string or binary data would be truncated. the statement has been terminated.![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) select * from t2![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) ![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/ExpandedBlockStart.gif) /**//* id notes ----------- ----------- 1 abcdeabcdef --2~4不能通过的原因是数据超长 5 ÉϺ£¶àÃÀ --非unicode汉字被解析 但在以char方式存储上发生困难 6 ÉϺ£¶àÃÀºÃ 8 ÉϺ£¶àÃÀºÃ */![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif) --注:以上内容在 Windows 2003 EE ENG SP1 + SQL 2000 EE ENG SP4上测试 OS上已装中文支持通过以上测试,我们似乎可以认为字符类型(char/varchar/nchar/nvarchar)以如下方式被存储 1 字符串本身需要被操作系统解析 2 字符串解析的结果会跟数据库code page做匹配, 无论能否匹配, 数据库需要计算字符串的长度是否可以被当前字段容纳, 如果不可以容纳, 放弃该语句, 如果可以容纳, 以字段类型进行存储 欢迎大家讨论。
SQL Server 2000 字符串数据乱码的讨论
sql server 2000开启后进程占用99%的讨论
有关SQL SERVER 聚集索引的讨论
一个行转列的例子讨论( sql server )
查找乱码字符串的SQL
Sql server 2000导入 Sql server 2005的数据
关于jsp与sql server 2000的中文乱码问题
解决Python 2.6 连接 SQL Server 2000 乱码的问题
SQL Server 2000 导入2005的数据
Sql Server Reporting Service的页面分页讨论
sql server的连接字符串
SQL SERVER字符串的截取
Sql Server 表中看不见的乱码
SQL SERVER 2000连接字符串
在SQL Server 2005(2008)中用T-SQL插入中文数据时出现的问号或乱码的解决方案
在SQL Server 2005(2008)中用T-SQL插入中文数据时出现的问号或乱码的解决方案
asp读sql server数据出现乱码问题解决方法
SQL Server插入中文数据出现乱码问题
Button控件的重绘
什么是飞信
存储过程的入门篇
J2EE架构学习者的6个最佳实践
cp命令使用
SQL Server 2000 字符串数据乱码的讨论
oracle pl/sql入门之案例实践
数据挖掘概念
vs2005无法调试,断点失效,设不住断点
SQLServer2005 公历日期
SQL Server 2005商业智能功能分析
在控制台下锁定系统的方法
源码 源代码下载 - www.pudn.com 程序员联合开发网
VC.NET的多位数组
|