How do I set the default schema for a user in MySQL, schemamysql

How do I set the default schema for a user in MySQL, schemamysql
 

Http://stackoverflow.com/questions/12426320/how-do-i-set-the-default-schema-for-a-user-in-mysql

 

 

Up vote16down votefavorite2

Is there a way to set a default schema for each user in MySQL and if so how? Where user x wocould default to schema y and user z wocould default to schema. Mysql user default database-schema Using improve this question Asked Sep 14 '12 at 14: 13 Robert Louis Murphy7551928

1   Are userxAndzMySQL users, or users of some other system (e.g. your OS )? Do you only need to specify a default database when using one type of client (e.g. mysql CLI, or PHP Data Objects), or do you need it to be for all clients? Do you need/want to override any default schema specified by the client on connecting? Do you want to disable changing of the default schema after one has been selected? -EggyalSep 14' 12 MySQL does not support schemas. Do you mean "database" instead? -A_horse_with_no_name Sep 14' 12 1   Oracle MySQL refers to databases as schemas in their tools. So where Microsoft has schemas and databases, MySQL just has schemas, but we call them databases.-Robert Louis Murphy Sep 14' 12 Add a comment

2 Answersactiveoldestvotes

Up vote27down voteaccepted

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 shoshould set it manually-USE db_name, Or in the connection string.

 

======================================

Http://stackoverflow.com/questions/1820971/set-current-database-in-mysql-script

 

 

6 down votefavorite1

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 too 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 participating database in the cursor, that database name is in a variable. I can't just say ALTER TABLE curschema.tblusersBecause 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 und 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 cocould 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 saved med? 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! Mysql database Using improve this question Edited Feb 14 '13 at 12: 55casperOne57. 4k10125202 Asked Nov 30 '09 at 16: 49afilbert45115

Add a comment

1 Answeractiveoldestvotes

Up vote16down voteaccepted

Did you tryUSE? 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