CODEWITHSUNDEEP
mysqlsql
Yusuf Devranlı
Yusuf Devranlı

Reputation: 159

MYSQL Select tables starting with "x"

I have a bunch of tables in my "stats" database.

tcl20151w1d1
tcl20151w1d2
tcl20151w2d1
tcl20151w2d2
tcl20151w3d1
tcl20151w3d2
tcl20151w4d1
eu20151w1d1
eu20151w1d2
eu20151w2d1
eu20151w2d2
eu20151w3d1
eu20151w3d2
eu20151w4d1
..

How can i select all tables that starts with "tcl" in "stats" database. Is it possible? Do I have to union them manually?

Upvotes: 0

Views: 828

Answers (1)

Shadow
Shadow

Reputation: 34231

You can query information_schema.tables table to get a list of tables where the table name start with tcl.

You can use the list to dynamically create a union query in a stored procedure using string concatenation and prepared statements.

If those tables are all myisam tables with the same structure, you may consider creating a merge table on them:

The MERGE storage engine, also known as the MRG_MyISAM engine, is a collection of identical MyISAM tables that can be used as one. “Identical” means that all tables have identical column and index information.

Upvotes: 1

Related Questions