我编写了一个存储过程,定义如下:CREATE PROCEDURE Proc_sp(begindate datetime,enddate datetime,out asCount varchar(128) UNICODE,out asExpound varchar(255) UNICODE) ...
其中asCount和asExpound 使用了concat()合并汉字字符串。 调用如下:
call Proc_sp(now(),now(),@a ,@b );
select @a,@b;结果执行出错:
SQL ERROR(1271):illegal mix of collations for operation 'concat';我按网上查的,修改字符集
依次执行:
set character_set_client =gb2312;
set character_set_connection =gb2312;
set character_set_database =gb2312;
set character_set_results =gb2312;
set character_set_server =gb2312;
set character_set_system =gb2312; --此处utf-8也可以
然后执行:
SET collation_server = gb2312_chinese_ci
SET collation_database = gb2312_chinese_ci
SET collation_connection =gb2312_chinese_ci
仍然不行。
这问题怎么解决?
其中asCount和asExpound 使用了concat()合并汉字字符串。 调用如下:
call Proc_sp(now(),now(),@a ,@b );
select @a,@b;结果执行出错:
SQL ERROR(1271):illegal mix of collations for operation 'concat';我按网上查的,修改字符集
依次执行:
set character_set_client =gb2312;
set character_set_connection =gb2312;
set character_set_database =gb2312;
set character_set_results =gb2312;
set character_set_server =gb2312;
set character_set_system =gb2312; --此处utf-8也可以
然后执行:
SET collation_server = gb2312_chinese_ci
SET collation_database = gb2312_chinese_ci
SET collation_connection =gb2312_chinese_ci
仍然不行。
这问题怎么解决?
你将UNICODE->gb2312试试
SET NAMES GB2312;
CALL Proc_sp(...)
set names GB2312;
call Proc_sp(now(),now(),@a ,@b );
select @a,@b;执行出错:
SQL Error(1366):
Incorrect string value :'\xE4\xBE\x8B\xEB\x8c...'for column 'asExpound' at row0
http://blog.csdn.net/ACMAIN_CHM/archive/2009/05/12/4174186.aspx
MySQL 中文显示乱码
CREATE PROCEDURE Proc_sp(begindate datetime,enddate datetime,out asCount varchar(128) unicode ,out asExpound varchar(255) unicode)
begin
declare iCount int;
declare iCounttmp int;
SET iCount = 0;
SET iCounttmp = 0; SELECT COUNT(*) into iCount FROM tblname1 WHERE InDatetime >= begindate and InDatetime < enddate;
SET asExpound = CONCAT('共有:', Rtrim(cast(iCount as char(12))) , ' 份;'); SELECT COUNT(*) into iCounttmp FROM tblname2 WHERE InDatetime >= begindate and InDatetime < enddate;
SET iCount = iCount + iCounttmp;
SET asExpound = CONCAT(asExpound , '共有:' , Rtrim(cast(iCounttmp as char(12))) , ' 份;'); SELECT COUNT(*) into iCounttmp FROM tblname3 WHERE InDatetime >= begindate and InDatetime < enddate;
SET iCount = iCount +iCounttmp;
SET asExpound = CONCAT(asExpound , '共有:' , Rtrim( cast(iCounttmp as char(12))) , ' 份;'); SELECT COUNT(*) into iCounttmp FROM tblname4 WHERE InDatetime >= begindate and InDatetime < enddate;
SET iCount = iCount + iCounttmp;
SET asExpound = CONCAT(asExpound , '共有:' , Rtrim( cast(iCounttmp as char(12))) , ' 份。');
SET asCount = CONCAT('共:' , Rtrim(cast(iCount as char(128))) , ' 份。');
end//
我用的是UNICODE,不能用?