CODEWITHSUNDEEP
mysqldefaultdatabase-schema
Robert Louis Murphy
Robert Louis Murphy

Reputation: 1628

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

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.

Upvotes: 59

Views: 164899

Answers (3)

Vy Do
Vy Do

Reputation: 52526

Exactly, the question should be Set the default database for session (not for user) .

Updated with MySQL 9.2.0

mysql> show databases;

use foo;

Verify it works: Simple list all tables of database foo

show tables;

Upvotes: 0

Gary
Gary

Reputation: 1969

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.

Upvotes: 19

Devart
Devart

Reputation: 121932

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.

Upvotes: 93

Related Questions