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

definedfunctionMySQLstored-procedures

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.

Best Answer

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

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

Related Solutions

Mysql – How to quickly rename a MySQL database (change schema name)

For InnoDB, the following seems to work: create the new empty database, then rename each table in turn into the new database:

RENAME TABLE old_db.table TO new_db.table;

You will need to adjust the permissions after that.

For scripting in a shell, you can use either of the following:

mysql -u username -ppassword old_db -sNe 'show tables' | while read table; \ 
    do mysql -u username -ppassword -sNe "rename table old_db.$table to new_db.$table"; done

for table in `mysql -u root -ppassword -s -N -e "use old_db;show tables from old_db;"`; do mysql -u root -ppassword -s -N -e "use old_db;rename table old_db.$table to new_db.$table;"; done;

Notes:

There is no space between the option -p and the password. If your database has no password, remove the -u username -ppassword part.
If some table has a trigger, it cannot be moved to another database using above method (will result Trigger in wrong schema error). If that is the case, use a traditional way to clone a database and then drop the old one:

mysqldump old_db | mysql new_db
If you have stored procedures, you can copy them afterwards:

mysqldump -R old_db | mysql new_db

Python – How to get a function name as a string

my_function.__name__

Using __name__ is the preferred method as it applies uniformly. Unlike func_name, it works on built-in functions as well:

>>> import time
>>> time.time.func_name
Traceback (most recent call last):
  File "<stdin>", line 1, in ?
AttributeError: 'builtin_function_or_method' object has no attribute 'func_name'
>>> time.time.__name__ 
'time'

Also the double underscores indicate to the reader this is a special attribute. As a bonus, classes and modules have a __name__ attribute too, so you only have remember one special name.

Best Answer

Related Solutions

Mysql – How to quickly rename a MySQL database (change schema name)

Python – How to get a function name as a string

Related Topic