ITPub博客

首页 > 数据库 > SQL Server > 数据库 SQLServer转MySQL数据库

数据库 SQLServer转MySQL数据库

原创 SQL Server 作者:Tybyq 时间:2018-11-14 17:25:57 0 删除 编辑

bug简述:sql由SQLServer转换成MySQL后,navicat下可以执行,项目中却报错。

报错如下:

数据库 SQLServer转MySQL数据库(一)

1-1


### Error querying database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Create TEMPORARY TABLE temp_table3

select * from (

select * from VIEW_UserMe' at line 2

### The error may exist in XX/XXX/XXXX/XXXXX/mapper/XXXXXMapper.xml

### The error may involve defaultParameterMap

### The error occurred while setting parameters

### SQL: Drop TEMPORARY TABLE IF exists temp_table3; Create TEMPORARY TABLE temp_table3 select * from ( select * from VIEW_UserMeter where supplierID =1 and SampleTime between date_sub(NOW(),interval 1 day) and date_add(NOW(),interval 1 day) ) as a; select * from (SELECT ROW_NUMBER() OVER ( ORDER BY sampleTime ) AS rownumber , building,entrance,doorplate,village,userName,remark1,remark2,meterId,meterSize,total,pressure,flowRate,valveStatus,status,updateTime,timeInp,vol,operate_ValveStatus,sampleTime,t1Inp FROM temp_table3 ) cc WHERE cc.rownumber > 0 limit 50; Drop TEMPORARY TABLE IF exists temp_table3;

### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Create TEMPORARY TABLE temp_table3

select * from (

select * from VIEW_UserMe' at line 2

; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Create TEMPORARY TABLE temp_table3

select * from (

select * from VIEW_UserMe' at line 2

.....................................................


将sql粘出执行,如下图,可成功执行,查询出结果,排除转换出错的可能:

数据库 SQLServer转MySQL数据库(一)

1-2

回过头查看控制台error:

### The error may involve defaultParameterMap

### The error occurred while setting parameters

考虑到是不是MyBatis不支持在XML配置的SQL中带有分号“;”。

经查询,开启了allowMultiQueries=true,MyBatis即可支持批量处理sql.

如下图:

数据库 SQLServer转MySQL数据库(一)

1-3

重新启动项目,可成功执行,问题解决。


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31557424/viewspace-2219998/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 数据库基础
请登录后发表评论 登录
全部评论

注册时间:2018-10-31

  • 博文量
    206
  • 访问量
    159949