CODEWITHSUNDEEP
mysqlsqlsyntax
abu-ahmed al-khatiri
abu-ahmed al-khatiri

Reputation: 189

a little bit confusing about a syntax on my databases MySQL

i try to check my databases with : 

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| db1                |
| db2                |
| sys                |
+--------------------+
6 rows in set (0,09 sec)

I also try to check my databases on : 

mysql> SELECT Db FROM mysql.db;
+--------------------+
| Db                 |
+--------------------+
| performance_schema |
| sys                |
| db1                |
| db2                |
| db1                |
| db2                |
+--------------------+
6 rows in set (0,00 sec)

Why are the results different with SHOW DATABASES; and SELECT Db FROM mysql.db; ?

Can i update my databases on mysql.db tables ??

Thanks in advance.

Upvotes: 1

Views: 75

Answers (3)

Chris
Chris

Reputation: 6193

Fun fact: you can delete a database and still find its name on mysql.db. So that is one more point you cannot rely on.

Upvotes: 0

Álvaro González
Álvaro González

Reputation: 146450

You can find database information by three methods:

  1. The mysql database, which is an internal schema used by MySQL Server itself for its own bookkeeping.
  2. The SHOW command, which is the earliest mechanism to expose the information to end users.
  3. The information_schema database, which is a "virtual" database with dynamically generated tables, implemented later as an enhancement to SHOW commands.

You should be using any except the first one (mainly, because it doesn't contain what you think it does, but also because regular users should never have read access on it). In practice, I've found that whether to use SHOW or information_schema depends on the server: they some times have very different performance and you don't always have permissions for the schema.

Upvotes: 1

GMB
GMB

Reputation: 222462

mysql.db id an internal system table, hence it is hard to find official documentation related to it. I found this MariaDB documentation, that states :

mysql.db is a system table that contains information about database-level privileges

This table is about storing user’s GRANTS (which explains why you see the same db more than once). Obviously it is not the good place to lookup available databases.

For that, it’s best to use SHOW DATABASES or the information schema.

Upvotes: 2

Related Questions