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

Question

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.

Are user x and z MySQL 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 at 14:40 — eggyal, Sep 14 '12 at 14:40
MySQL does not support schemas. Do you mean "database" instead? – a_horse_with_no_name Sep 14 '12 at 15:22 — a_horse_with_no_name, Sep 14 '12 at 15:22
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 at 15:40 — Robert Louis Murphy, Sep 14 '12 at 15:40

casperOne · Accepted Answer · 2013-02-14 12:55:02Z

up vote 27down 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 should set it manually - USE db_name, or in the connection string.

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

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

6down vote favorite

1

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!

edited Feb 14 '13 at 12:55

casperOne

57.4k10125202

asked Nov 30 '09 at 16:49

afilbert

45115

add a comment

1 Answer

active oldest votes

up vote 16down 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 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.mytable USE db2; SELECT COUNT(*) FROM mytable; # selects from db2.mytable

score 16down voteaccepted · Accepted Answer

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.mytable USE db2; SELECT COUNT(*) FROM mytable; # selects from db2.mytable

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

2 Answers

1 Answer