CODEWITHSUNDEEP
mysqlsqlregexbash
pku
pku

Reputation: 89

selecting N particular tables from MySQL

I have a mysql db and have 100 tables in it named, table_1, table_2, table_3 etc... In my bash script I only want to select 10 tables at a time say from table_20 to table_29. I tried the following code:

show tables where Tables_in_db REGEXP '^table_[20-29]'

but it treats 2,0,9 as separate digits rather than identifying 20 and 29 as numbers.

How should I modify my query?

Upvotes: 1

Views: 59

Answers (3)

Lukasz Szozda
Lukasz Szozda

Reputation: 175756

You could use INFORMATION_SCHEMA.TABLES:

SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE 'table_%'
ORDER BY CAST(REPLACE(TABLE_NAME, 'table_', '') AS INT)
LIMIT 20,10;

How it works:

  1. Get all tables that have specific name convention i.e. starting with table_

  2. Sort by number suffix

  3. Use LIMIT offset, count

I do not know your specific schema but multiple tables with common prefix could indicate poor design.

Related: SELECT * FROM sales + @yymm

Upvotes: 0

sjsam
sjsam

Reputation: 21965

If you could use an external tool, then awk is your friend, Example :

mysql -u root -p -se "show tables from db_name" | awk '/^table_2[0-9]$/'

This gives you table_(20-29) as you expect. Note -s with mysql to get rid of the formatting.

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521437

The regex to match the numerical sequence of table names from table_20 to table_29 is:

^table_2[0-9]

So your query should be:

SHOW TABLES WHERE Tables_in_db REGEXP '^table_2[0-9]'

Upvotes: 5

Related Questions