/**
* 生成分表查询语句
* sqls:原始sql,带有占位符
* tableNums: 分表个数
* skipType: 跳过分表类型 0.不跳过 1.跳过奇数表 2.跳过偶数表
* replaceStr: 替换占位符
*
* @date 2021-11-08
* @author fdh
*/
CREATE DEFINER=`root`@`%` FUNCTION `buildSql`(sqls varchar(1000), tableNums int(3), skipType int(1), replaceStr varchar(100)) RETURNS text CHARSET utf8
BEGIN
set @oriSql = sqls;
set @oriReplaceStr = replaceStr;
set @i = 0;
set @tempSql = '';
set @replaceSql = '';
-- 循环开始
while @i<tableNums DO
if (skipType = 1 and mod(@i, 2) = 0) then
set @replaceSql := replace(@oriSql, @oriReplaceStr, @i);
elseif (skipType = 2 and mod(@i, 2) = 1) then
set @replaceSql := replace(@oriSql, @oriReplaceStr, @i);
end if;
if (skipType = 0 and @replaceSql = '') then
set @replaceSql := replace(@oriSql, @oriReplaceStr, @i);
end if;
if (@tempSql = '' and @replaceSql != '') then
set @tempSql := CONCAT(@replaceSql, '\n');
elseif (@tempSql != '' and @replaceSql != '') then
set @tempSql := CONCAT(@tempSql, 'union all\n', @replaceSql, '\n');
end if;
-- 重置
set @i := @i + 1;
set @replaceSql := '';
end while;
-- 循环结束
RETURN @tempSql;
END
经常给领导查数据,但是数据库不少表是分表的,查询起来粘贴复制很麻烦,这里仅作记录,之前已经介绍过Mysql存储过程的使用,和变量声明,以及分支判断等。
mysql 存储过程简单使用 :https://www.cnblogs.com/jefferyfeng/p/14479399.html