Is there a way to set a default schema for each user in MySQL and if so how? Where user x would default to schema y and user z would default to schema a.

shareimprove this question

asked Sep 14 '12 at 14:13 Robert Louis Murphy 7551928

I have a script file for MySQL that needs to be run on about 30 databases. Here's a snippet:

ALTER TABLE <whatever_database>.`tblusers` ADD COLUMN `availability` ...

ALTER TABLE <whatever_database>.`tblusers` ADD COLUMN `reliability` INTEGER ...

There are many more lines in this script and I'd like to automate it in a loop of current databases to be updated. I've got the list and the loop down using cursors, but here's where I'm running into trouble:

When I'm on a particular database in the cursor, that database name is in a variable. I can't just say ALTER TABLE curschema.tblusers because the script complains that there is no database named curschema (the name of the variable containing the database name that I'd like to run operations on). I've been able to work around this by creating and executing a statement using parameters:

SET @curschema = curschema;
SET @query = NULL;
SET @email = emailAddress;
SET @pass = pass;
SET @statement = CONCAT('SELECT userid INTO @query FROM ', @curschema, '.tbluser 
PREPARE stmt FROM @statement;
EXECUTE stmt;

The problem is, setting up executable strings (like above) will become an extremely tedious task with the dozens of lines of code that I have to run. I was hoping that there was a way that I could set the current database for operations and then just reset that database each loop pass so my generic statements might be run:

(start of my loop)

SET DATABASE database0 -- (through to database29)

-- run statements with database0 .... 29 being implied with the above command

ALTER TABLE `tblusers` ADD COLUMN `availability` TINYINT(1) UNSIGNED ...

ALTER TABLE `tblusers` ADD COLUMN `reliability` INTEGER UNSIGNED NOT ...

(end of my loop)

Does such a command exist to set the current database on which operations may be performed? If no such command exists, is there an easier way to accomplish my task? I figure that at this juncture it's just easier to do a find/replace on all the database names 30 times than it is to rewrite my entire script file to be executable/parameter-ized strings.

Thanks!

shareimprove this question

edited Feb 14 '13 at 12:55 casperOne 57.4k10125202

asked Nov 30 '09 at 16:49 afilbert 45115