MySQL Charsets

mysql-charsets.sql

-- Database
SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
FROM information_schema.SCHEMATA
WHERE schema_name = "database_name";

-- Tables
SELECT DISTINCT TABLE_COLLATION, COLLATION_NAME, CHARACTER_SET_NAME
FROM information_schema.`TABLES` AS t
INNER JOIN information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` AS ccsa ON ccsa.collation_name = t.table_collation
AND t.table_schema = "database_name";

-- Columns
SELECT DISTINCT CHARACTER_SET_NAME, COLLATION_NAME
FROM information_schema.`COLUMNS`
WHERE table_schema = "database_name";

-- ----------------------------------------

-- 1. Make a backup!
-- 2. Set the default char sets on the database.
--    This does not convert existing tables, it only sets the default for newly created tables.
-- 3. Convert the char set on all existing tables and their columns.
--    This assumes that your current data is actually in the current char set.

-- Example: Update charset to UTF-8 General
ALTER DATABASE database_name CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

-- Example: Update charset to UTF-8 Unicode
ALTER DATABASE database_name CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;