MySQL Function to return table name to be used in a query

Question

Can I use a MySQL function or procedure to return table names that can be used in a query?

Something like

SELECT * FROM get_db_table_name(2342352412);

where get_db_table_name() is a user defined function which accepts a user_id as a parameter and returns the db.table where that user resides. I have not been able to do this this way because MySQL complains about the syntax (my guess is it's because I had to define a return type for the function, which I tried as VARCHAR(30) and it is wrong or impossible). So, is this possible?

The story is that I have a few databases that are essentially shards. Let's call them user_1, user_2, etc.

I also have a single table, say emails that contains records with user_ids from tables user_1.users, user_2.users, etc.

Since I know a way to compute which table an id is from just by looking at the id, I just want a function that would accept an input and return a db.table name to be used.

Thank you!

P.S. I'd like to use the above SELECT query as a VIEW definition that would bring the user's name from the proper db/table by doing a JOIN across the proper table.

Answer 1

You can use prepared statements for this, e.g.:

....
SET @tbl = 'nameofmytable';
SET @sql = CONCAT( 'SELECT * FROM ', @tbl );
EXECUTE @sql;
...

Answer 2

When defining a view you must specify the tables. You can not define a view on "SELECT FROM whereever needed" ...

The view definition is “frozen” at creation time, so changes to the underlying tables afterward do not affect the view definition. For example, if a view is defined as SELECT * on a table, new columns added to the table later do not become part of the view.

You must specify my view is on this, this and this table. Looking at this you can not do SELECT * FROM func(id) because you can not specify what the results of this function will be (depending on the input parameter).

The syntax of SELECT is

SELECT * FROM table_references

The only way of "dynamic" SELECT is with dynamic SQL, what @Zed answered...

I'm sorry if my answer is incomplete, but I don't know your problem in details. Good Luck!

Answer 3

Please help me this function mysql_pconnect() when i call to database not complete don't show error ,don't show my data what happend

my email [email protected]

Answer 4

Parameterising the table name is considered to be usually because of bad database design. Is it really not possible to have all the entities of the same type (in this case, your users) in one table?