How to SELECT from multiple tables in MySQL?

Question

I have a lot of tables with the same _ips endings. Example:

first-domain.com_ips
secons-domain.com_ips
...

I'm trying to get UNION result table which will contains all rows from all _ips tables. For this I use:

SELECT id, expirationDate FROM `first-domain.com_ips` WHERE isBlocked = 1
UNION
SELECT id, expirationDate FROM `secons-domain.com_ips` WHERE isBlocked = 1
...;

I have an array which consists of domain names. So I'm looking for a way to use this domain array in SQL query. Maybe something like we use with SELECT. Example:

const ids = [3, 4, 6, 8];
const query = 'SELECT * FROM table WHERE id IN (' + ids.join() + ')';

Is there a way to use array for tables names in SQL? Thank you in advance!

Fix your data structure! Don't store a separate table for each domain. Include the domain as a column in a single table. — Gordon Linoff
– Gordon Linoff, Commented Dec 30, 2018 at 14:20
If there is any chance that the values from the ids array could come from the outside, unsterilized, then you'll want to use a prepared statement to build your query. — Tim Biegeleisen
– Tim Biegeleisen, Commented Dec 30, 2018 at 14:22
Please take a look at my posted answer, mark it as accepted answer if it solves your problem. — Tarreq
– Tarreq, Commented Dec 30, 2018 at 15:08

Tarreq · Accepted Answer · 2018-12-30 14:43:47Z

2

You can do this by using dynamic queries and regexps:

This dynamic query does what you want :

SELECT
  GROUP_CONCAT(
    CONCAT(
      'SELECT * FROM `',
      TABLE_NAME,
      '`') SEPARATOR ' UNION ALL ')
FROM
  `INFORMATION_SCHEMA`.`TABLES` 
WHERE
  `TABLE_NAME` REGEXP '_ips$'
INTO @sql;

SELECT @sql;

PREPARE stmt FROM @sql;
EXECUTE stmt;

This query gives two outputs, first is the final SQL query string which looks like :

SELECT * FROM `first-domain.com_ips` UNION ALL SELECT * FROM `second-domain.com_ips`

and the other output is the actual data from all tables, if you want only the final data, you can remove this statement:

SELECT @sql;

answered Dec 30, 2018 at 14:43

Tarreq

1,3551 gold badge10 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Nastro Over a year ago

Will try it in nearest time, when get to my computer. Thank you!

Nastro Over a year ago

yes, it works! but could you explain where TABLE_NAME comes from? And what is INTO and @sql?

Tarreq Over a year ago

TABLE_NAME is a placeholder variable, that gets its value from INFORMATION_SCHEMA.TABLES , which holds all table names in your DB, so when you run this, it replaces it with the actual table name, putting a where clause defining regular expression "REGEXP '_ips$' that means, get all tables from table list that ends with '_ips'. The @sql is also variable that the select puts its result to, so it holds your final query string.

Collectives™ on Stack Overflow

How to SELECT from multiple tables in MySQL?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related