DELIMITER $$USE `vgos_qw0529`$$DROP PROCEDURE IF EXISTS `pr_queryshieldoperatehisparam`$$CREATE DEFINER=`root`@`%` PROCEDURE `pr_queryshieldoperatehisparam`(
IN v_mobile VARCHAR(60),
IN v_sendNo VARCHAR(60),
IN v_currentPage INT,
IN v_pageSize INT -- ,
-- out v_totalRecordNumber int
)
BEGIN
-- declare v_nums int;
-- set v_nums = 0;
-- set v_nums=(v_pageSize-1)*v_currentPage;
SELECT DISTINCT bb.mobile AS mobile,aa.grp_code AS grp_code,aa.grp_name AS grp_name,
'' AS belongCompany,'' AS platformName,bb.sendno AS sendNo,bb.operation AS operateType,
bb.jobnumber AS jobNumber,bb.reason AS reason,bb.tim AS operateTime
FROM shieldstate_log bb
LEFT JOIN
(SELECT c.grp_code AS grp_code,c.grp_name AS grp_name,a.`tel_num` AS tel_num
FROM user_info a,user_grp b,grp_info c
WHERE a.user_id=b.user_id
AND b.grp_id=c.grp_id
AND a.tel_num=v_mobile) aa
ON bb.mobile=aa.tel_num
WHERE bb.sendno=v_sendNo
ORDER BY operateTime;
-- LIMIT v_nums,v_currentPage;
END$$DELIMITER ;以上这个过程 在调用的时候报:
Query : call pr_queryshieldoperatehisparam ('13911669296','234',1,1) Error Code : 1267
Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='调用语句是:
CALL pr_queryshieldoperatehisparam ('1390000000','234',1,1)
这个错误 是数据库的字符编码和表的字符编码不符的错误
但是本人把 过程中的 sql语句单独提出来运行的时候 是没有问题的。。 例如:
SELECT DISTINCT bb.mobile AS mobile,aa.grp_code AS grp_code,aa.grp_name AS grp_name,
'' AS belongCompany,'' AS platformName,bb.sendno AS sendNo,bb.operation AS operateType,
bb.jobnumber AS jobNumber,bb.reason AS reason,bb.tim AS operateTime
FROM shieldstate_log bb
LEFT JOIN
(SELECT c.grp_code AS grp_code,c.grp_name AS grp_name,a.`tel_num` AS tel_num
FROM user_info a,user_grp b,grp_info c
WHERE a.user_id=b.user_id
AND b.grp_id=c.grp_id
AND a.tel_num='1390000000') aa
ON bb.mobile=aa.tel_num
WHERE bb.sendno='267'
ORDER BY operateTime;
这样是没有问题的....
为什么 把这个语句封装成存储过程之后 就报错了呢? 难道是入参的问题? 我入参也是定义的字符类型啊
为什么呢???
IN v_mobile VARCHAR(60),
IN v_sendNo VARCHAR(60),
IN v_currentPage INT,
IN v_pageSize INT -- ,
-- out v_totalRecordNumber int
)
BEGIN
-- declare v_nums int;
-- set v_nums = 0;
-- set v_nums=(v_pageSize-1)*v_currentPage;
SELECT DISTINCT bb.mobile AS mobile,aa.grp_code AS grp_code,aa.grp_name AS grp_name,
'' AS belongCompany,'' AS platformName,bb.sendno AS sendNo,bb.operation AS operateType,
bb.jobnumber AS jobNumber,bb.reason AS reason,bb.tim AS operateTime
FROM shieldstate_log bb
LEFT JOIN
(SELECT c.grp_code AS grp_code,c.grp_name AS grp_name,a.`tel_num` AS tel_num
FROM user_info a,user_grp b,grp_info c
WHERE a.user_id=b.user_id
AND b.grp_id=c.grp_id
AND a.tel_num=v_mobile) aa
ON bb.mobile=aa.tel_num
WHERE bb.sendno=v_sendNo
ORDER BY operateTime;
-- LIMIT v_nums,v_currentPage;
END$$DELIMITER ;以上这个过程 在调用的时候报:
Query : call pr_queryshieldoperatehisparam ('13911669296','234',1,1) Error Code : 1267
Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='调用语句是:
CALL pr_queryshieldoperatehisparam ('1390000000','234',1,1)
这个错误 是数据库的字符编码和表的字符编码不符的错误
但是本人把 过程中的 sql语句单独提出来运行的时候 是没有问题的。。 例如:
SELECT DISTINCT bb.mobile AS mobile,aa.grp_code AS grp_code,aa.grp_name AS grp_name,
'' AS belongCompany,'' AS platformName,bb.sendno AS sendNo,bb.operation AS operateType,
bb.jobnumber AS jobNumber,bb.reason AS reason,bb.tim AS operateTime
FROM shieldstate_log bb
LEFT JOIN
(SELECT c.grp_code AS grp_code,c.grp_name AS grp_name,a.`tel_num` AS tel_num
FROM user_info a,user_grp b,grp_info c
WHERE a.user_id=b.user_id
AND b.grp_id=c.grp_id
AND a.tel_num='1390000000') aa
ON bb.mobile=aa.tel_num
WHERE bb.sendno='267'
ORDER BY operateTime;
这样是没有问题的....
为什么 把这个语句封装成存储过程之后 就报错了呢? 难道是入参的问题? 我入参也是定义的字符类型啊
为什么呢???
试试
CREATE DEFINER=`root`@`%` PROCEDURE `pr_queryshieldoperatehisparam`(
IN v_mobile VARCHAR(60) CHARACTER SET utf8,
IN v_sendNo VARCHAR(60) CHARACTER SET utf8,
IN v_currentPage INT,
IN v_pageSize INT -- ,
-- out v_totalRecordNumber int
)
IN v_mobile VARCHAR(60),
IN v_sendNo VARCHAR(60),
这2个变量上面,需要手动的设置,默认的是 utf8_general_ci 排序规则,而表utf8_unicode_ci
COLLATE 引起的,
把 user_info表(或则其join的字段)的排序规则改成 utf8_general_ci 就可以了。
表的字符集确实是utf8_unicode_ci 请问‘把 user_info表(或则其join的字段)的排序规则改成 utf8_general_ci 就可以了’ 这句话是啥意思? 如何改? 我不太懂。。 谢谢
show create table 你所用的表...;
贴结果
MySQL 中文显示乱码