1. 问题描述

数据库中有多张结构相同的表，这些表的名称具有相同的前缀。比如：

xxxxxxxxxx
mysql> use test
Database changed

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| url_01         |
| url_02         |
| url_03         |
| url_04         |
| url_05         |
+----------------+
5 rows in set (0.00 sec)

mysql> show create table url_01\G
*************************** 1. row ***************************
       Table: url_01
Create Table: CREATE TABLE `url_01` (
  `url` varchar(1024) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

现在向其中插入若干测试数据：

xxxxxxxxxx
insert into url_01(url) values("http://01.com/100"),("http://01.com");
insert into url_02(url) values("http://02.com/100"),("http://02.com");
insert into url_03(url) values("http://03.com/100"),("http://03.com");
insert into url_04(url) values("http://04.com/100"),("http://04.com");
insert into url_05(url) values("http://05.com/100"),("http://05.com");

要求：查出所有表中 url 字段中包含 100 的记录。

下面提供一个纯 SQL 实现。

2. 使用存储过程实现

xxxxxxxxxx
delimiter $$

create procedure testproc(in result_sql text)
begin
  declare expected_table_name varchar(1024); -- 期望的表名
  declare tmp text default ""; -- 临时字符串
  declare done int default false; -- 是否遍历到结束
  -- 从 information_schema 中查找符合模式的表
  declare testcur cursor for select Table_Name from information_schema.TABLES where TABLE_SCHEMA="test" and TABLE_NAME regexp "^url_";
  declare continue handler for not found set done = True;

  open testcur;

  read_loop: loop
    fetch testcur into expected_table_name;
    
    if done then
      leave read_loop;
    end if;

    select concat("select * from ", expected_table_name, " where url like '%100%'") into tmp;
    if @result_sql = "" then
      select tmp into @result_sql;
    else
      select concat(@result_sql, " union all ", tmp) into @result_sql;
    end if;
  end loop;
  
  close testcur;

  prepare stmt from @result_sql;
  execute stmt;
end$$

delimiter ;

set @result_sql = "";
call testproc(@result_sql);
select @result_sql\G

drop procedure testproc; -- 删除