Determining the default database charset and collation in MySQL

Question

In MySQL, what is the best way of programmatically retrieving the character set and the collation of the current database?

Is the following:

SELECT 
    default_character_set_name, default_collation_name
FROM
    information_schema.SCHEMATA
WHERE
    SCHEMA_NAME = SCHEMA()

identical to the below example?

select @@character_set_database, @@collation_database

Answer 1

According to the documentation:

character_set_database

...

The character set used by the default database. The server sets this variable whenever the default database changes. If there is no default database, the variable has the same value as character_set_server.

...

and

collation_database

...

The collation used by the default database. The server sets this variable whenever the default database changes. If there is no default database, the variable has the same value as collation_server.

...

with both sentences would obtain the same result:

SELECT 
    default_character_set_name, default_collation_name
FROM
    information_schema.SCHEMATA
WHERE
    SCHEMA_NAME = SCHEMA()

and

select @@character_set_database, @@collation_database

demonstrated in the following test:

mysql> DROP DATABASE IF EXISTS `my_database`;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT SCHEMA();
+----------+
| SCHEMA() |
+----------+
| NULL     |
+----------+
1 row in set (0.00 sec)

mysql> SELECT
    ->   @@SESSION.character_set_database,
    ->   @@SESSION.collation_database,
    ->   @@SESSION.character_set_server,
    ->   @@SESSION.collation_server;
+----------------------------------+------------------------------+--------------------------------+----------------------------+
| @@SESSION.character_set_database | @@SESSION.collation_database | @@SESSION.character_set_server | @@SESSION.collation_server |
+----------------------------------+------------------------------+--------------------------------+----------------------------+
| latin1                           | latin1_swedish_ci            | latin1                         | latin1_swedish_ci          |
+----------------------------------+------------------------------+--------------------------------+----------------------------+
1 row in set (0.00 sec)

mysql> CREATE DATABASE IF NOT EXISTS `my_database`
    -> CHARACTER SET utf8mb4
    -> COLLATE utf8mb4_general_ci;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT SCHEMA();
+----------+
| SCHEMA() |
+----------+
| NULL     |
+----------+
1 row in set (0.00 sec)

mysql> SELECT
    ->   @@SESSION.character_set_database,
    ->   @@SESSION.collation_database,
    ->   @@SESSION.character_set_server,
    ->   @@SESSION.collation_server;
+----------------------------------+------------------------------+--------------------------------+----------------------------+
| @@SESSION.character_set_database | @@SESSION.collation_database | @@SESSION.character_set_server | @@SESSION.collation_server |
+----------------------------------+------------------------------+--------------------------------+----------------------------+
| latin1                           | latin1_swedish_ci            | latin1                         | latin1_swedish_ci          |
+----------------------------------+------------------------------+--------------------------------+----------------------------+
1 row in set (0.00 sec)

mysql> USE `my_database`;
Database changed

mysql> SELECT SCHEMA();
+-------------+
| SCHEMA()    |
+-------------+
| my_database |
+-------------+
1 row in set (0.00 sec)

mysql> SELECT
    ->   @@SESSION.character_set_database,
    ->   @@SESSION.collation_database,
    ->   @@SESSION.character_set_server,
    ->   @@SESSION.collation_server;
+----------------------------------+------------------------------+--------------------------------+----------------------------+
| @@SESSION.character_set_database | @@SESSION.collation_database | @@SESSION.character_set_server | @@SESSION.collation_server |
+----------------------------------+------------------------------+--------------------------------+----------------------------+
| utf8mb4                          | utf8mb4_general_ci           | latin1                         | latin1_swedish_ci          |
+----------------------------------+------------------------------+--------------------------------+----------------------------+
1 row in set (0.00 sec)

mysql> SELECT
    ->   `DEFAULT_CHARACTER_SET_NAME`,
    ->   `DEFAULT_COLLATION_NAME`
    -> FROM
    ->   `information_schema`.`SCHEMATA`
    -> WHERE
    ->   SCHEMA_NAME = SCHEMA();
+----------------------------+------------------------+
| DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+----------------------------+------------------------+
| utf8mb4                    | utf8mb4_general_ci     |
+----------------------------+------------------------+
1 row in set (0.00 sec)

however, a union of both sentences would not be wrong:

mysql> USE `my_database`;
Database changed

mysql> SELECT
    ->   `DEFAULT_CHARACTER_SET_NAME`,
    ->   `DEFAULT_COLLATION_NAME`
    -> FROM
    ->   `information_schema`.`SCHEMATA`
    -> WHERE
    ->   SCHEMA_NAME = SCHEMA() AND
    ->   `DEFAULT_CHARACTER_SET_NAME` = @@SESSION.character_set_database AND
    ->   `DEFAULT_COLLATION_NAME` = @@SESSION.collation_database;
+----------------------------+------------------------+
| DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+----------------------------+------------------------+
| utf8mb4                    | utf8mb4_general_ci     |
+----------------------------+------------------------+
1 row in set (0.00 sec)