你需要UNION/UNION ALL来完成这个操作，但是有一种方法可以让你只写一次查询，然后在需要的地方进行编辑，你可以使用PREPARED STATEMENT。
下面是一个例子：

SET @generatedQuery := NULL;
SELECT GROUP_CONCAT( CONCAT('SELECT * FROM ',table_name) SEPARATOR ' UNION ALL ')
         INTO @generatedQuery
 FROM information_schema.tables
WHERE 
 table_schema='Myserver' AND
 table_name LIKE 'mytable_%';
/*Check the generated query*/
SELECT @generatedQuery;
PREPARE statement FROM @generatedQuery;
EXECUTE statement;
DEALLOCATE PREPARE statement;

字符串
在上面的例子中，你只需要关注查询来生成实际的查询。假设你只想获取特定的日期范围，那么你可以这样做：

SELECT GROUP_CONCAT(CONCAT('SELECT * FROM ',table_name,' WHERE date="2023-11-11"')) 
                 SEPARATOR ' UNION ALL ')
         INTO @generatedQuery
 FROM information_schema.tables
WHERE 
 table_schema='Myserver' AND
 table_name LIKE 'mytable_%';

型
或者可能数据库/服务器（table_schema）已经改变，或者table_name前缀现在不同了，那么：

SELECT GROUP_CONCAT( CONCAT('SELECT * FROM ',table_name) SEPARATOR ' UNION ALL ')
         INTO @generatedQuery
 FROM information_schema.tables
WHERE 
 table_schema='newserver' AND
 table_name LIKE 'myNewtableName_%';

型
Demo fiddle

这很重要，因为我的表是每天创建的，我不想为了获取数据而在CTE中手动添加它们
然后你需要动态地生成SQL。
避免这种情况的另一种方法是创建一些标准视图，这些视图从表中返回数据，但让创建表的进程重新创建视图，这样就可以有一个不需要更改的查询。

• 无论哪种方式，某些进程都需要更改视图或更改查询以引用新表。动态SQL是数据库内部的常见解决方案，但否则您可以在应用程序层生成查询，这将是相同的主体。

在我看来，Union是完美的：
从手动查询开始：
https://sqlfiddle.com/#!9/afd29a/2
请注意，这里我使用表名的后缀构造了一个日期列

SELECT STR_TO_DATE(RIGHT('Mytable_name.05112021',8),'%d%m%Y') as "date"
     , `id`
     , `revenue`
FROM `Mytable_name.05112021`
UNION ALL
SELECT STR_TO_DATE(RIGHT('Mytable_name.06112021',8),'%d%m%Y') as "date"
     , `id`
     , `revenue`
FROM `Mytable_name.06112021`
UNION ALL
SELECT STR_TO_DATE(RIGHT('Mytable_name.07112021',8),'%d%m%Y') as "date"
     , `id`
     , `revenue`
FROM `Mytable_name.07112021`;

字符串
我不知道在MySQL中有什么函数可以用来返回给定列引用的表名，但是如果我们要使用动态或 * 准备好的语句 *，那就没关系了。
首先，我们可以使用这个查询生成上面的SQL：

SELECT GROUP_CONCAT(CONCAT("SELECT STR_TO_DATE(RIGHT('", table_name, "',8),'%d%m%Y') as \"date\", `id`, `revenue` FROM `", table_name ,"`") SEPARATOR ' UNION ALL ') as Query 
FROM information_schema.tables
WHERE table_name like 'MyTable_name.%';

型
它返回以下内容：

SELECT STR_TO_DATE(RIGHT('mytable_name.05112021',8),'%d%m%Y') as "date", `id`, `revenue` FROM `mytable_name.05112021` UNION ALL SELECT STR_TO_DATE(RIGHT('mytable_name.06112021',8),'%d%m%Y') as "date", `id`, `revenue` FROM `mytable_name.06112021` UNION ALL SELECT STR_TO_DATE(RIGHT('mytable_name.07112021',8),'%d%m%Y') as "date", `id`, `revenue` FROM `mytable_name.07112021`

型
因此，我们可以将所有这些功能整合到一个SP中，下面是一个完整的示例，您可以按照该示例进行操作
https://www.db-fiddle.com/f/4Xiv7XtbhP7d2N5EdqsYg4/0的

CREATE PROCEDURE dynamicQuery()
BEGIN
SET @query = (SELECT GROUP_CONCAT(CONCAT("SELECT STR_TO_DATE(RIGHT('", table_name, "',8),'%d%m%Y') as \"date\", `id`, `revenue` FROM `", table_name ,"`") SEPARATOR ' UNION ALL ') as Query 
              FROM information_schema.tables
              WHERE table_name like 'MyTable_name.%');
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END

型

Schema（MySQL v5.7）

DELIMITER //
CREATE TABLE `Mytable_name.05112021` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `revenue` decimal(10,2)
)//
CREATE TABLE `Mytable_name.06112021` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `revenue` decimal(10,2)
)//
CREATE TABLE `Mytable_name.07112021` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `revenue` decimal(10,2)
)//
INSERT INTO `Mytable_name.05112021` (`revenue`) VALUES
( 50.10 ),
(135.10),
(35.75 )//
INSERT INTO `Mytable_name.06112021` (`revenue`) VALUES
( 60.10 ),
(136.10),
(36.75 )//
INSERT INTO `Mytable_name.07112021` (`revenue`) VALUES
( 70.10 ),
(137.10),
(37.75 )//
CREATE PROCEDURE dynamicQuery()
BEGIN
SET @query = (SELECT GROUP_CONCAT(CONCAT("SELECT STR_TO_DATE(RIGHT('", table_name, "',8),'%d%m%Y') as \"date\", `id`, `revenue` FROM `", table_name ,"`") SEPARATOR ' UNION ALL ') as Query 
              FROM information_schema.tables
              WHERE table_name like 'MyTable_name.%');
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END
//

CALL dynamicQuery;

| 日期|ID|收入|
| --|--|--|
| 2021-11-05| 1 |50.10|
| 2021-11-05| 2 |135.10|
| 2021-11-05| 3 |三十五点七五|
| 2021-11-06 2021-11-06 2021-11-06| 1 |60.10|
| 2021-11-06 2021-11-06 2021-11-06| 2 |136.10|
| 2021-11-06 2021-11-06 2021-11-06| 3 |三十六点七五|
| 2021-11-07 2021-11-07| 1 |70.10|
| 2021-11-07 2021-11-07| 2 |137.10|
| 2021-11-07 2021-11-07| 3 |三十七点七五|
View on DB Fiddle的