How do i get all table names who match a specific regex in mysql 8.0?

Question

I'm trying to retrieve all tables with specific name format, for performing union among those tables. I'm using mysql Ver 8.0.13, and i wrote this following query for retrieving the relevant tables:

show tables LIKE REGEX '^table_.+_class$';

I couldn't figure out the correct syntax for this query :/

Afterwards i'm planning to union all those tables.

I would like to avoid writing this code since it doesn't scale nicely:

SELECT * FROM table_french_class
UNION
SELECT * FROM table_history_class
UNION
SELECT * FROM table_pingpong_class
UNION
SELECT * FROM table_math_class
UNION
SELECT * FROM table_literature_class

Can someone suggest me how to handle this issue?

Thank you

Answer 1

You could use INFORMATION_SCHEMA catalog:

SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME REGEXP '^table_.+_class$';