53

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.

Robert Louis Murphy
  • 1,558
  • 1
  • 16
  • 29
  • 1
    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? – eggyal Sep 14 '12 at 14:40
  • MySQL does not support schemas. Do you mean "database" instead? –  Sep 14 '12 at 15:22
  • 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 at 15:40

2 Answers2

89

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.

Devart
  • 119,203
  • 23
  • 166
  • 186
  • Will that user now always have database1 as their default schema every time they log in, or you have to say USE database1; each time you connect? – Robert Louis Murphy Sep 14 '12 at 15:39
  • 1
    You should set default database every time using `USE database` or connection string. The MySQL creates new session without default database. – Devart Sep 17 '12 at 06:19
  • Is it possible to utilize `USE` dynamically? Something like `USE (SELECT schema FROM blahblah WHERE blahblah)` – hpaknia May 05 '19 at 04:10
19

If your user has a local folder e.g. Linux, in your users home folder you could create a .my.cnf file and provide the credentials to access the server there. for example:- 

[client]
host=localhost
user=yourusername
password=yourpassword or exclude to force entry
database=mygotodb

Mysql would then open this file for each user account read the credentials and open the selected database.

Not sure on Windows, I upgraded from Windows because I needed the whole house not just the windows (aka Linux) a while back.

Gary
  • 1,917
  • 3
  • 19
  • 19
  • I believe that both answers are correct in some way. @Gary has pointed the solution that avoid us to set the schema every time we want to access our database and Devart give the solution to use it once. – FelipeCaparelli May 17 '20 at 00:55