How does I set the default schema for a user in MySQL

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

Up vote16down votefavorite2

Is there a-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 the default to schema A. MySQL User default Database-schema ShareImprove this question Asked Sep ' at 14:13Robert Louis Murphy75519

are user  x and  z  mysql users, or users of some other system (e.g. your OS)? Need to specify a default database when using one type of the client (e.g. MySQL CLI, or PHP Data Objects), or do You need it to is for all clients? Do I need/want to override any default schema specified by the client on connecting? Want to disable changing of the default schema after one have been Selected? – eggyalsep" at 14:40   &nbs P;   mysql does not support schemas. Do you mean "database" Instead? – a_horse_with_no_name sep 14 ' At 15:22 1 Oracle MySQL refers to databases as schemas in their tools. So where Microsoft have schemas and databases, MySQL just have schemas, but we call them databases. –robert Louis Murphy Sep ' at 15:40 Add a Comment

2 Answersactiveoldestvotes

Up vote27down voteaccepted

There is no the default database for user. There is the 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.

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

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

6down votefavorite1

I have a script file for MySQL This needs to is run on about databases. Here's a snippet: Alter TABLE <whatever_database>. ' tblusers ' add column availability ...< Span class= "PLN" >alter table<whatever_database>. ' tblusers< Span class= "pun" > ' add column ' reliability ' INTEGER ... There is many more lines in the this script and I ' d like to automate it in a loop of the 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 was 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 n Ame 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, ' .from @statementexecute Stmt The problem is, setting up executable strings (like above) would become an extremely tedious task with the dozens of lines of code that I had to run. I was hoping this there was a a-to-I could set the current database for operations and then just reset that database E Ach loop Pass so my generic statements might is run: (Start of my loop) SET DATABASE Database0--(through to database29) --run statements with database0 .... Being implied with the above command Alter TABLE `Tblusers add column ' availability ' (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 could be performed? If No such command exists, is the there an easier-to accomplish my task? I figure so at this juncture it's just easier to does a find/replace on all the database names times than it's to REWR Ite my entire script file to is executable/parameter-ized strings. thanks! MySQL Database ShareImprove this question edited  Feb ' at 12:55 casperone57.4k< Span title= "Ten gold Badges" >10125 202 Asked at 16:49afilbert115

Add a Comment

1 answeractiveoldestvotes

Up vote16down voteaccepted

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 Stateme Nts. The database remains the default until the end of the session or another use statement is issued. Example: Use Db1;select Count (*) from< Span class= "PLN" > Mytable;# selects from Db1.< Span class= "PLN" >mytableuse Db2; select Count (*) from< Span class= "PLN" > Mytable;# selects from Db2.< Span class= "PLN" >mytable

How does I set the default schema for a user in MySQL