mysql-commands.md

MySQL commands

• Show table collation from a database:

show table status from <database>;

• Show collation from an entire database;

select default_character_set_name from information_schema.schemata s where schema_name = '<database>';

• Show column collcation:

show full columns from <table>;

• Alter charset and collation of a database:

alter database <database> character set utf8 collate utf8_unicode_ci;

• Alter charset and collation of a table:

alter table <table> convert to character set utf8 collate utf8_unicode_ci;

• Database size on disk:

SELECT table_name AS `Table`, round((data_length / 1024 / 1024), 2) `Size of Data (MB)`, round((index_length / 1024 / 1024), 2) `Size of Index (MB)` FROM information_schema.TABLES WHERE table_schema = "<database>" order by `Size of Data (MB)` desc;