mybatis中支持mysql中的变量吗?

Question

@rownum这样的变量是写在sql里面的不是存储过程里面,在mybatis的mapper.xml里面,应该怎么写?
加了CDATA也不行

Answer 1

mysql连接url + allowMultiQueries=true

jdbc:mysql://localhost:3306/DBS?allowMultiQueries=true

MyBatis MySql -Stack Overflow

Answer 2

以前Mysql官方网站上有一个图形软件叫“MySQL GUI Tools”，它里面有一个工具“MySQLMigrationTool”就是干这个活的。
MySQL GUI Tools现在的oracle官方网站上已经找不到了，但它很有名，其它一些网站上也有下的。

Answer 3

支持
SET @ROW_NUMBER:=0,@median_group:='';
SELECT
<if test="intervalTime == 'day' ">
a.year,a.month,a.day,
</if>
<if test="intervalTime == 'month' ">
a.year,a.month,
</if>
<if test="intervalTime == 'year' ">
a.year,
</if>
ROUND(AVG(avgResponseTime)) AS median
FROM(
SELECT @ROW_NUMBER:=CASE
<if test="intervalTime == 'day' ">
WHEN @median_group = a.day
</if>
<if test="intervalTime == 'month' ">
WHEN @median_group = a.month
</if>
<if test="intervalTime == 'year' ">
WHEN @median_group = a.year
</if>
THEN @ROW_NUMBER + 1
ELSE 1
END AS count_of_group,
<if test="intervalTime == 'day' ">
@median_group:=a.day AS median_group,
</if>
<if test="intervalTime == 'month' ">
@median_group:=a.month AS median_group,
</if>
<if test="intervalTime == 'year' ">
@median_group:=a.year AS median_group,
</if>
a.*,b.total_of_group
FROM (
SELECT a.year,a.month,a.day,a.avgResponseTime
FROM analysis_base_service_invoke_by_hour AS a
WHERE createTime BETWEEN #{startTime} AND #{endTime}
AND deleteFlag=0
AND serviceId=#{serviceId}
<if test="userId != null">
AND userId=#{userId}
</if>
<if test="intervalTime == 'day' ">
ORDER BY a.year,a.month,a.day,a.avgResponseTime ASC
</if>
<if test="intervalTime == 'month' ">
ORDER BY a.year,a.month,a.avgResponseTime ASC
</if>
<if test="intervalTime == 'year' ">
ORDER BY a.year,a.avgResponseTime ASC
</if>
) AS a,
(
SELECT COUNT(*) AS total_of_group,a.year,a.month,a.day FROM(
SELECT a.year,a.month,a.day,a.avgResponseTime
FROM analysis_base_service_invoke_by_hour AS a
WHERE createTime BETWEEN #{startTime} AND #{endTime}
AND deleteFlag=0
AND serviceId=#{serviceId}
<if test="userId != null">
AND userId=#{userId}
</if>
<if test="intervalTime == 'day' ">
ORDER BY a.year,a.month,a.day,a.avgResponseTime ASC
</if>
<if test="intervalTime == 'month' ">
ORDER BY a.year,a.month,a.avgResponseTime ASC
</if>
<if test="intervalTime == 'year' ">
ORDER BY a.year,a.avgResponseTime ASC
</if>
) AS a
<if test="intervalTime == 'day' ">
GROUP BY a.year,a.month,a.day
</if>
<if test="intervalTime == 'month' ">
GROUP BY a.year,a.month
</if>
<if test="intervalTime == 'year' ">
GROUP BY a.year
</if>
) AS b
<if test="intervalTime == 'day' ">
WHERE a.year=b.year AND a.month=b.month AND a.day=b.day
</if>
<if test="intervalTime == 'month' ">
WHERE a.year=b.year AND a.month=b.month
</if>
<if test="intervalTime == 'year' ">
WHERE a.year=b.year
</if>
) AS a
WHERE a.count_of_group
BETWEEN a.total_of_group / 2.0
AND a.total_of_group / 2.0 + 1
<if test="intervalTime == 'day' ">
GROUP BY a.year,a.month,a.day
</if>
<if test="intervalTime == 'month' ">
GROUP BY a.year,a.month
</if>
<if test="intervalTime == 'year' ">
GROUP BY a.year
</if>

Answer 4

也遇到这个问题貌似不支持，建议写个通用存储过程，直接用java代码拼好sql传进去执行