mysqlmysql-variables

What is the use of MYSQL8 variable 'character_set_database'?

I use the SQL 'show variables like 'character%';' to show the character set in MySQL variables.I find a variable named 'character_set_database', I want to know the use of it. I get some info from the MySQL doc website. But I don't know the mean of 'default database' in the doc context. The keyword 'default database' makes me confused about the use of the variable 'character_set_database'.
So please help me to explain the keyword 'default database' or the use of variable 'character_set_database' directly.

The desc about attribute 'character_set_database' in mysql website:

· 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.

As of MySQL 8.0.14, setting the session value of this system variable is a restricted operation. The session user must have privileges sufficient to set restricted session variables. See Section 5.1.9.1, “System Variable Privileges”.

The global character_set_database and collation_database system variables are deprecated and will be removed in a future version of MySQL.

Assigning a value to the session character_set_database and collation_database system variables is deprecated and assignments produce a warning. The session variables will become read-only in a future version of MySQL and assignments will produce an error. It will remain possible to access the session variables to determine the database character set and collation for the default database."

The reference link : character_set_database

Solution

  • The character_set_database variable shows you the default character set of the database which is currently in use. To select a default database you use:

    use my_database;

    Consider the following example..

    Create two new databases with different character sets:

    create database test_db1 character set latin1 collate latin1_general_ci;
create database test_db2 character set utf8mb4 collate utf8mb4_unicode_ci;

    Now select a database and see what's in the character_set_database variabale:

    use test_db1;
select @@character_set_database;

    Will return: 'latin1'

    use test_db2;
select @@character_set_database;

    Will return: 'utf8mb4'

    The default character set of a database is used for new tables, when you don't specify it explicitly. Example:

    use test_db2;

create table test1 (c text) default character set latin1 collate latin1_general_ci;
show create table test1;

create table test2 (c text);
show create table test2;

    The first result will be:

    CREATE TABLE `test1` (
  `c` text COLLATE latin1_general_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci

    As you can see, the table test1 is using CHARSET=latin1, which we have set explicitly.

    The second result will be:

    CREATE TABLE `test2` (
  `c` text COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

    Since we didn't specify the character set for the table test2, it is using CHARSET=utf8mb4, which is the default character set of the database test_db2. And test_db2 is currently the default database, since we have selcted it with use test_db2. It is called default databse, because it's used, when you omit the the database prefix in statement.

    Example:

    select * from test_db1.test1;

    will return the same as

    use test_db1;
select select * from test1;