SQLServer与mysql兼容
问题提出
在项目过程中,客户提出要使用SQL Server数据库,而我们之前采用的是mysql数据库。现在问题来了,之前做的用的是mysql,我们想做一个产品,又想满足特定客户的需求,因此要求一个系统既支持mysql数据库,又支持SQLSever数据库的。
我们的架构是Spring + mybatis。
原则上编程的过程中,尽量少使用数据库的方言,尽量采用标准的SQL语句。这也只能是原则上的,还有一个问题简直是避无可避,那就是分页问题!这个问题稍后再讨论。
解决问题
1、添加jdbc 的jar包:用的是jtds.jar。连接数据库这方面不多说了……
2、安装SQLServer,我用的是SQLServer 2008,考虑到如果使用太新的数据库,太先进的SQL,有可能客户那边如果有现成的数据库,存在不兼容的问题。这个也不多说了……
3、SQL方言问题
有些方言问题,我本来想通过在框架上配置,检测哪种数据库,写两套数据库的脚本。这个我没有实现,但是在实现的过程中我发现,其实带方言的还是少数,大部分是通用的数据库语句。因此我放弃了这种配置。
缓存中有存的数据库配置信息,我在service层取得缓存中的配置类型,通过得到的数据,来决定使用哪条查询语句。这样的话,语句稍有重复,但是改动工作量不算特别大。
4、分页
mysql的分页是我见过的,最简单的分页。
limit [start],[size]
SQLServer:
SELECT TOP [size] *
FROM
(
SELECT ROW_NUMBER() OVER(ORDER BY id) AS rownumber,* FROM test
) a
WHERE rownumber > [start]
代码来自于百度,非我原创。这种传说中的兼容SQL Server 2005及以上。而我认为这种是比较简单的,就一致采用了这种的分页方法
5、GROUP_CONCAT
在迁移过程中,有人采用了GROUP_CONCAT这个方法,实现分组的字符串拼接。而SQL Server中显然是没有这种方法的。SQL Server显然也有自己的分组字符串拼接,但是比mysql显嫌复杂一些。
SELECT id,
value = STUFF((SELECT ',' + value FROM tb AS t WHERE t .id = tb.id FOR XML PATH('')), 1, 1, '')
FROM tb
GROUP BY id
以上代码依然来自于网络,非我原创。
有了例子,就可以照葫芦画瓢了。但是依然有不少问题。主要原因还是现实中的代码略复杂,涉及到不只一个表。
mysql代码如下:
SELECT a.*,GROUP_CONCAT(c.name) cnames
FROM a LEFT JOIN b ON b.aid=a.id
LEFT JOIN c ON b.cid=c.id
WHERE a.isDelete=0
GROUP BY a.id
ORDER BY a.addTime asc
LIMIT#{start},#{size}
代码被我简化了一下,简单地说,就是a,c两表是多对多关联,b表是中间表。b中分别有aid与cid与两表对应,记录关联关系。以a表的id主键进行分组,c表的名称存为分组后的 1,2,3 这种格式。
修改后的效果:
SELECT TOP ${size} *
FROM
(
SELECT ROW_NUMBER() OVER(ORDER BY a.startTime asc) as rownumber,
a.*,cnames
FROM a LEFT JOIN
(SELECT b1.aid,
STUFF( (SELECT ',' + c.name FROM b b2
LEFT JOIN c ON b2.cid=c.id where b2.aid = b1.aid FOR XML PATH('') ), 1, 1, '' ) AS cnames
FROM b b1
GROUP BY b1.aid ) st
ON st.aid=a.id
WHERE a.isDelete=0
) v
where rownumber > ${start}
<pre class="sql" name="code"> <p>完全不想再看第二眼,但是为了我自己以后能看懂,还是稍微说几句</p>
<ol>
<li>先取a表的数据
</li><li>GROUP BY分页是以b表的aid,虽然a.id与b.aid的值是相同的,但是在STUFF中用的是b.aid,没有加入a表,因此,只能用b.aid。再详细我也解释不出来……
</li><li>暂时没有了,想起来再说
</li></ol>
<h3>6、SQL Server比mysql语法更严谨一点。</h3>
<p>一位同事笔误写的,SELECT COUNT(*) FROM table GROUP BY column,mysql是可以通过的,而在SQL Server中是没有通过的。</p>