USE: Choosing a Database

A connection works inside one default database at a time. USE switches it.

1
2
3

CREATE DATABASE shop;
USE shop;
SELECT DATABASE() AS current_database;

1
2
3
4
5
6

+------------------+
| current_database |
+------------------+
| shop             |
+------------------+
1 row in set

DATABASE() (or SCHEMA()) returns the name of the current database. It returns NULL when none is selected.

Without a default database

If no database is selected, a statement with an unqualified table name fails. You can see it by dropping the database your session is using:

1
2
3

DROP DATABASE learn;
SELECT DATABASE() AS now_in;
CREATE TABLE notebooks (id INT);

1
2
3
4
5
6

+--------+
| now_in |
+--------+
| shop   |
+--------+
1 row in set

DATABASE() is NULL, and CREATE TABLE notebooks fails with error 1046, No database selected. Fix it with USE database_name; or by writing the qualified name (shop.notebooks). If you start mysql without a database name, you get the same error until you run USE.

Qualified names

database.table lets you use any database you have privileges on, without switching:

1
2
3
4

CREATE DATABASE blog;
CREATE TABLE blog.posts (id INT, title VARCHAR(50));
INSERT INTO blog.posts VALUES (1, 'Hello');
SELECT * FROM blog.posts;

1
2
3
4
5
6

+------+-------+
| id   | title |
+------+-------+
|    1 | Hello |
+------+-------+
1 row in set

Qualified names are explicit and clear, so they are good for scripts that touch more than one database.

In applications

When an application connects, it names the database in the connection settings (dbname=shop in PHP’s PDO, or database: 'shop' in Node.js drivers). That sets the default database for the whole connection. Avoid running USE in application code, especially with connection pools, because the next request might get a connection that was switched by an earlier one.

Connection-level settings are per session

USE, SET SESSION and similar commands apply only to the current connection. Another client connected at the same moment has its own default database.