mysql - 从名称中具有常见字符的表中选择*

我有以常见字符开头的表名，例如

mytab_user
mytab_group
mytab_mode
mytab_blah

我有很多类似的表格。 mysql中是否有任何命令行会执行类似的操作，

select * from mytab_*;

并立即显示所有这些表中的所有数据。

最佳答案

使用stored procedure来执行任务。下面是一个示例。

    use mydatabase;

    DROP PROCEDURE IF EXISTS showalldata;

    delimiter //
    CREATE PROCEDURE showalldata()
    BEGIN
      DECLARE done INT DEFAULT FALSE;
      DECLARE a CHAR(255);
      DECLARE cur1 CURSOR FOR (
        SELECT table_name
        FROM information_schema.tables
        WHERE table_schema = DATABASE()
            AND table_name like 'mytab\_%'
        ORDER BY table_name ASC);
      DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

      OPEN cur1;
      read_loop: LOOP
        FETCH cur1 INTO a;
        IF done THEN
          LEAVE read_loop;
        END IF;

        SET @sql = CONCAT('SELECT * from ', a);
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

      END LOOP;

      CLOSE cur1;
    END //
    delimiter ;

    CALL showalldata();