DELIMITER $$;DROP PROCEDURE IF EXISTS `web_oa_database`.`search_problem`$$CREATE PROCEDURE `web_oa_database`.`search_problem` (
in p_id int,
in tm1 varchar(50),
in tm2 varchar(50),
in k_ft varchar(100),
in p_tp varchar(50),
in k_pb varchar(200),
in p_gd varchar(20),
in p_ic varchar(200),
in s_t varchar(30)
)
BEGIN
declare str varchar(2000);
set str='';
if p_id<>0 then
set str=concat(' where data083.pid=',p_id);
end if;
if tm1<>'' then
if str='' then
set str=concat(' where data083.ftm>=',tm1,' and data083.ftm<=',tm2);
else
set str=concat(str,' and data083.ftm>=',tm1,' and data083.ftm<=',tm2);
end if;
end if;
if k_ft<>'' then
if str='' then
set str=concat(' where data083.kft like',' %',k_ft,'%');
else
set str=concat(str,' and data083.kft like',' %',k_ft,'%');
end if;
end if;
if p_tp<>'' then
if str='' then
set str=concat(' where data083.ptp=',p_tp);
else
set str=concat(str,' and data083.ptp=',p_tp);
end if;
end if;
if k_pb<>'' then
if str='' then
set str=concat(' where data083.kpb like', ' %',k_pb,'%');
else
set str=concat(str,' and data083.kpb like',' %',k_pb,'%');
end if;
end if;
if p_gd<>'' then
if str='' then
set str=concat(' where data083.pgd=',p_gd);
else
set str=concat(str,' and data083.pgd=',p_gd);
end if;
end if;
if p_ic<>'' then
if str='' then
set str=concat(' where data083.pic like',' %',p_ic,'%');
else
set str=concat(str,' and data083.pic like',' %',p_ic,'%');
end if;
end if;
if s_t<>'' then
if str='' then
set str=concat(' where data083.st=',s_t);
else
set str=concat(str,' and data083.st=',s_t);
end if;
end if;
set str=concat('select * from data083 ',str);
SET @sql=str; PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; END$$DELIMITER ;$$//错误提示很明了:DBAccess.execQuery()Unknown column '??“???è??è??' in 'where clause',但是检查了半天,没有发现表列名有误。就怀疑是sql语句拼接出了问题,一时间也找不出来,望高手能看下,指点下错误,不胜感激,谢谢!
in p_id int,
in tm1 varchar(50),
in tm2 varchar(50),
in k_ft varchar(100),
in p_tp varchar(50),
in k_pb varchar(200),
in p_gd varchar(20),
in p_ic varchar(200),
in s_t varchar(30)
)
BEGIN
declare str varchar(2000);
set str='';
if p_id<>0 then
set str=concat(' where data083.pid=',p_id);
end if;
if tm1<>'' then
if str='' then
set str=concat(' where data083.ftm>=',tm1,' and data083.ftm<=',tm2);
else
set str=concat(str,' and data083.ftm>=',tm1,' and data083.ftm<=',tm2);
end if;
end if;
if k_ft<>'' then
if str='' then
set str=concat(' where data083.kft like',' %',k_ft,'%');
else
set str=concat(str,' and data083.kft like',' %',k_ft,'%');
end if;
end if;
if p_tp<>'' then
if str='' then
set str=concat(' where data083.ptp=',p_tp);
else
set str=concat(str,' and data083.ptp=',p_tp);
end if;
end if;
if k_pb<>'' then
if str='' then
set str=concat(' where data083.kpb like', ' %',k_pb,'%');
else
set str=concat(str,' and data083.kpb like',' %',k_pb,'%');
end if;
end if;
if p_gd<>'' then
if str='' then
set str=concat(' where data083.pgd=',p_gd);
else
set str=concat(str,' and data083.pgd=',p_gd);
end if;
end if;
if p_ic<>'' then
if str='' then
set str=concat(' where data083.pic like',' %',p_ic,'%');
else
set str=concat(str,' and data083.pic like',' %',p_ic,'%');
end if;
end if;
if s_t<>'' then
if str='' then
set str=concat(' where data083.st=',s_t);
else
set str=concat(str,' and data083.st=',s_t);
end if;
end if;
set str=concat('select * from data083 ',str);
SET @sql=str; PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; END$$DELIMITER ;$$//错误提示很明了:DBAccess.execQuery()Unknown column '??“???è??è??' in 'where clause',但是检查了半天,没有发现表列名有误。就怀疑是sql语句拼接出了问题,一时间也找不出来,望高手能看下,指点下错误,不胜感激,谢谢!
解决方案 »
- 为什么查询出了多余而且数据库没有的记录
- mysql登入时出错ERROR 2003:Can't connect to MySQL server on 'localhost'<10061>
- mysql4.0.16中的sql-bin-update-same参数是什么意思呢?
- select语句的优化问题,求高手解决
- myslq的 expt 是否可以做
- MsMql用DTS导出数据到MySql问题
- MYSQL lEFT JOIN用法。
- mysql 每个连接的 最长时间是多少?
- 关于 mysql REGEXP
- mysql通过命令行能导入中文正常显示,而导入sql文件却出现乱码?
- 急啊,请教用mysql语句怎么导入*.sql文件?
- mysql中有无像sqlserver一样的得到字符unicode码的函数?
show variables like 'char%';
select @sql;
PREPARE stmt FROM @sql; EXECUTE stmt;
看看生成的SQL语句是什么。
select * from data083 where data083.ptp=结构设计
上面sql不能正常执行,字符编码错误,下面sql加上单引号后可正常执行了,
select * from data083 where data083.ptp='结构设计'
由此是否可推断sql在拼接时传入的变量值是要加单引号的呢?
要加,示例
set str=concat(str,' and data083.st=','\'',s_t,'\'');
当然需要加单引号。 确保生成的SQL本身是正确的。