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

Source: Internet
Author: User
Tags db2 stmt

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.