MySQL Selecting From a Table name matching string

Question

I'm relatively new to SQL and I've been having trouble trying to figure out how to select rows from a table that matches a string. The name of the table uses the current month and year. It looks like this:

XXXX.xxx_YY_MM

where XXXX is the database, YY is year, and MM is month. So normally the query would just look like this:

select * from XXXX.xxx_16_05;

However, I want it to be able to change depending on the date. So I thought this would work:

select * from (select concat('XXXX.xxx_',date_format(now(), '%y_%m'))));

The concat bit gives me something that looks exactly like the name of the table. But it doesn't work and I'm not sure why. It says every table must have it's own alias. I'm not sure what to do about it.

Alternatively, I was thinking maybe something like this would be ok

 select * from  (select * from information_schema.tables where table_name like concat('%logsms_',date_format(now(), '%y_%m'),'%'));

But it doesn't work either. What should I do? There is only one table with a name that matches the string.

What are you using to manipulate the DB? In php, I would take the current date with a function of php then trim it down to what you desire, and just use the variable in the query. This, however does not work if the month changes, and you want to access the table for the previous month. — Mihail Ivanchev
– Mihail Ivanchev, Commented May 25, 2016 at 21:03

Barmar · Accepted Answer · 2016-05-26 15:27:09Z

3

You can't use an expression for the table name in SQL. You need to use a stored procedure that creates dynamic SQL and executes it using PREPARE and EXECUTE.

SET @sql = CONCAT('SELECT * FROM XXXX.xxx_', DATE_FORMAT(NOW(), '%y_%m'));
PREPARE stmt FROM @sql;
EXECUTE stmt;

A database design that requires this seems like a poor decision. You should have a single table where the date is in a column, not separate tables for each month.

If you're running the query from a programming language, you can use its own date functions to constructure SQL.

edited May 26, 2016 at 15:27

answered May 25, 2016 at 21:04

Barmar

789k57 gold badges555 silver badges669 bronze badges

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

1 Comment

Will Over a year ago

Thanks, it worked like a charm (after I took out the '@' in '@stmt'). I wasn't familiar with the whole PREPARE and EXECUTE statements yet but I guess now I'll have to look into them because the seem very useful.

Radadiya Nikunj · Accepted Answer · 2019-07-10 14:05:09Z

0

Please find the following query to retrieve all table by matching string.

select table_schema as database_name,table_name from information_schema.tables
where table_name like '%your_table_name%' order by table_schema, table_name;

answered Jul 10, 2019 at 14:05

Radadiya Nikunj

1,11513 silver badges12 bronze badges

Collectives™ on Stack Overflow

MySQL Selecting From a Table name matching string

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related