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 Murphy7551928

There is no default database for user. There is default database for current session.

You can get it using DATABASE() function -

SELECT DATABASE();

And you can set it using USE statement -

USE database1;

You should set it manually - USE db_name, or in the connection string.

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 casperOne57.4k10125202

asked Nov 30 '09 at 16:49 afilbert45115

Did you try USE ?

USE db_name

The USE db_name statement tells MySQL to use the db_name database as the default (current) database for subsequent statements. The database remains the default until the end of the session or another USE statement is issued.

Example:

USE db1;SELECT COUNT(*) FROM mytable;  # selects from db1.mytableUSE db2;SELECT COUNT(*) FROM mytable;  # selects from db2.mytable