Mysql query to discover current default database collation (via command line client)

Question

I can't figure out which is the query to run in order to see the current collation set for a mysql database.

I know I can change it running a query like this:

ALTER DATABASE db_name
[[DEFAULT] CHARACTER SET charset_name]
[[DEFAULT] COLLATE collation_name]

but I don't want to do it without knowing the current value.

I think it's actually possible because in phpmyadmin you can see the current value in the operation tab of your database, but I need to do it via the command line client and after googling a little bit I found no answers.

Probably I will open phpmyadmin code to discover it.. before doing it, does anybody know which is the right query?

Thanks in advance.

Ravel

Answer 1

Figured out by myself, looking at phpmyadmin code.

Depending on mysql version, the query to run is this:

SELECT DEFAULT_COLLATION_NAME FROM data_dictionary.SCHEMAS WHERE SCHEMA_NAME = 'nameOfMyDb' LIMIT 1;

or

SELECT DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = 'nameOfMyDb' LIMIT 1;

Ravel

Answer 2

show variables like "%collat%"

Complicate

Thanks, it's interesting but I think these are the default values of the mysql configuration, used as default when you're creating a new db (I guess). What for a specific database? – Ravel

Answer 3

Option 1

SELECT @@character_set_database, @@collation_database;

* see MySQL: what does @@ mean?

Option 2

SHOW VARIABLES LIKE 'character_set_database';
SHOW VARIABLES LIKE 'collation_database';

Option 3

SELECT 
   default_character_set_name, default_collation_name
FROM information_schema.schemata 
WHERE schema_name = '<database_name>';

Damiondamita