mysql - 从多个表名中选择 *

Question

我想从数据库中的每个表中选择 *。我该怎么做？

我尝试了以下脚本:

SELECT * 
  FROM 
     ( SELECT TABLE_NAME 
         FROM INFORMATION_SCHEMA.TABLES 
         WHERE TABLE_CATALOG = 'databasename'
     )

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_CATALOG = 'databasename' 已经返回数据库的每个表名，但是我如何从这个“列表”中选择所有内容？

Answer 1

游标在极少数情况下很有用，因为您需要迭代 information_schema 表、构建准备好的语句并执行它(请注意，您一次只能向动态 sql 提交 1 个语句。

drop procedure if exists p;

delimiter $$

CREATE  PROCEDURE p()

begin 
     declare vtable_name varchar(100);
     declare done int;
    declare c7 cursor for SELECT   TABLE_NAME 
         FROM INFORMATION_SCHEMA.TABLES 
         WHERE TABLE_schema = 'sandbox' and table_type = 'base table' and table_name in ('t', 't1');

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    open c7;
    cursorloop:loop
        fetch c7 into vtable_name;
        if done = true then  
        leave cursorloop;
        end if;  
        set @sql = concat('select * from ', vtable_name,';');
        select @sql;
        prepare sqlstmt from @sql;
        execute sqlstmt;
        deallocate prepare sqlstmt; 
     end loop cursorloop;
    close c7;
 end $$

 delimiter ;

 call p();

注意，出于说明目的，我限制了光标选择，并限制了返回基表的内容(您可能希望也可能不想包括 View )

MariaDB [sandbox]> call p();
+------------------+
| @sql             |
+------------------+
| select * from t; |
+------------------+
1 row in set (0.00 sec)

+------+------+-------+
| id   | user | value |
+------+------+-------+
|    1 | A    | Cool  |
|    2 | A    | Cool  |
|    3 | A    | Cool  |
|    2 | A    | Warm  |
|    3 | A    | Warm  |
|    4 | B    | Cool  |
|    5 | C    | Cool  |
|    5 | C    | Warm  |
+------+------+-------+
8 rows in set (0.00 sec)

+-------------------+
| @sql              |
+-------------------+
| select * from t1; |
+-------------------+
1 row in set (0.04 sec)

+------+-------+
| sku  | stock |
+------+-------+
|    1 |     5 |
|    2 |     5 |
+------+-------+
2 rows in set (0.04 sec)

特别注意构建的 sql 语句。