最近在使用springmvc+jdbc查询的工程中出现了这个问题,因为这种框架使用较少,我以为是我sql传参或者jdbc使用有问题,后来研究发现这不是mysql问题,是字符串拼接问题,
开始我的sql是这样的:
String sql = "update exam_" + examid + "_signup_sub
SET KD_ID = " + examSiteCode + "
WHERE SIGNUPID in
(SELECT a.SIGNUPID from
(select SIGNUPID from exam_" + examid + "_signup where BMDS = " + examRegion + " and BKZY = " + examProfess + " and SUBJECTS = " + subjectCode + ") a)";
-
//这是改正后的拼接方式
-
String sql = "update exam_" + examid + "_signup_sub SET KD_ID = " + examSiteCode + " WHERE SIGNUPID in (SELECT a.SIGNUPID from (select SIGNUPID from exam_" + examid + "_signup where BMDS = '" + examRegion + "' and BKZY = '" + examProfess + "' and SUBJECTS = '" + subjectCode + "') a)";
看这个两个sql的最后一行查询为a的sql语句
where后的查询条件一个是 where BMDS = " + examRegion + " and BKZY = " + examProfess + " and SUBJECTS = " + subjectCode + "
一个是 BMDS = '" + examRegion + "' and BKZY = '" + examProfess + "' and SUBJECTS = '" + subjectCode + "'
区别在于第一个查询条件运行出来是BMDS =001,第二个则是BMDS = '001'
如果这些查询条件字段就是数字类型,那没什么好说的,但是在这里的字段都是字符型,
在msql中,如果非要把数字当查询条件传给字符型,msql会截取该字段前几个可以转为数字的值来作为查询条件,如果整个字段的前几个字符都不能转为数字,mysql就会出这个警告“Truncated incorrect DOUBLE value”。
总之,mysql查询时,数据库表里的字段是什么类型,最好查询条件就传什么类型。