Mysql从多表中查询满足条件数据

最新推荐文章于 2023-09-26 10:29:28 发布

devilcry13

最新推荐文章于 2023-09-26 10:29:28 发布

阅读量1.9k

点赞数

CC 4.0 BY-SA版权

分类专栏： mysql

本文链接：https://blog.youkuaiyun.com/devilcry13/article/details/88617705

mysql 专栏收录该内容

5 篇文章

订阅专栏

本文介绍如何创建存储过程，用于从多个基于bizNo哈希路由的表中选取满足条件的数据。通过联合查询和直接执行SQL语句来实现跨表查询。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

创建存储过程从100表中选出满足条件的数据：

通过对bizNo进行hash路由查出所在的（库）表
使用联合查询查找（存储过程拼接sql查询，直接用sql语句进行查询）

drop PROCEDURE if EXISTS find_task;
create procedure find_task(in tblPre varchar(256),in bizNo VARCHAR(256))
BEGIN
declare i int default 0;
set @fulStr='';
set @tblPre=tblPre;

-- 拼接查询字符串
	while i<99 do
		if i<10 then
-- 			set @tbl=concat('task_00', i);
			set @tbl=concat(@tblPre, '_00', i);
		else
-- 			set @tbl=concat('task_0', i);
			set @tbl=concat(@tblPre, '_0', i);
		end if;

		set @fulStr:=CONCAT(@fulStr, "select * from ",@tbl," union all ");

		set i = i+1;
	end while;
	set @fulStr:=concat(@fulstr,"select * from ", @tblPre, "_099");

	set @bizId=bizNo;
	SET @newStr:=CONCAT("select * from (",@fulStr,") as t where t.business_no=?");
-- 	select @newStr;
-- 执行查询
 	prepare stmt1 from @newStr;
 	execute stmt1 using @bizId;
 	deallocate prepare stmt1;  

end;

– call find_task(‘bizNo_201903151239442019031512394406295342’)
call find_task(‘task’,‘bizNo_201903151257512019031512575148987627’);
call find_task(‘task_lazy’,‘bizNo_201903151239442019031512394406295342’)

select * from (select * from task_000 union all select * from task_001 union all select * from task_002 union all select * from task_003 union all select * from task_004 union all select * from task_005 union all select * from task_006 union all select * from task_007 union all select * from task_008 union all select * from task_009 union all select * from task_010 union all select * from task_011 union all select * from task_012 union all select * from task_013 union all select * from task_014 union all select * from task_015 union all select * from task_016 union all select * from task_017 union all select * from task_018 union all select * from task_019 union all select * from task_020 union all select * from task_021 union all select * from task_022 union all select * from task_023 union all select * from task_024 union all select * from task_025 union all select * from task_026 union all select * from task_027 union all select * from task_028 union all select * from task_029 union all select * from task_030 union all select * from task_031 union all select * from task_032 union all select * from task_033 union all select * from task_034 union all select * from task_035 union all select * from task_036 union all select * from task_037 union all select * from task_038 union all select * from task_039 union all select * from task_040 union all select * from task_041 union all select * from task_042 union all select * from task_043 union all select * from task_044 union all select * from task_045 union all select * from task_046 union all select * from task_047 union all select * from task_048 union all select * from task_049 union all select * from task_050 union all select * from task_051 union all select * from task_052 union all select * from task_053 union all select * from task_054 union all select * from task_055 union all select * from task_056 union all select * from task_057 union all select * from task_058 union all select * from task_059 union all select * from task_060 union all select * from task_061 union all select * from task_062 union all select * from task_063 union all select * from task_064 union all select * from task_065 union all select * from task_066 union all select * from task_067 union all select * from task_068 union all select * from task_069 union all select * from task_070 union all select * from task_071 union all select * from task_072 union all select * from task_073 union all select * from task_074 union all select * from task_075 union all select * from task_076 union all select * from task_077 union all select * from task_078 union all select * from task_079 union all select * from task_080 union all select * from task_081 union all select * from task_082 union all select * from task_083 union all select * from task_084 union all select * from task_085 union all select * from task_086 union all select * from task_087 union all select * from task_088 union all select * from task_089 union all select * from task_090 union all select * from task_091 union all select * from task_092 union all select * from task_093 union all select * from task_094 union all select * from task_095 union all select * from task_096 union all select * from task_097 union all select * from task_098 union all select * from task_099) as t where t.business_no='bizNo_201903151257512019031512575148987627';