Change the character set & collation in MySQL/MariaDB

Note: For check the character set in MySQL/MariaDB. Please click on it.

Change the character set & collation in MySQL/MariaDB for the table:

ALTER TABLE table_name
 CONVERT TO CHARACTER SET charset_name [COLLATE collation_name];

OR 

CREATE TABLE table_name (column1  datatype1, column2 datatype2 .....) 
  CHARACTER SET 'character_name' 
  COLLATE 'collation_name';

Change or set character set & collation at the database level:

CREATE DATABASE database_name 
  CHARACTER SET = 'character_name'
  COLLATE = 'collation_name';

OR

ALTER DATABASE database_name COLLATE = 'collation_name';

Change character set & collation at the Server level:

SET character_set_server = 'character_name';


SET collation_server = 'collation_name';

Note: Both parameter are dynamic

Change character set & collation at column level:

ALTER TABLE table_name MODIFY column_name DATA_TYPE CHARACTER SET character_name COLLATE collation_name;

Script to change multiple columns for a table or all tables:

---Change column with VARCHAR datatype
SELECT CONCAT('ALTER TABLE `', table_name,
        '` MODIFY `', column_name, '` ', DATA_TYPE, '(', CHARACTER_MAXIMUM_LENGTH, 
        ') CHARACTER SET UTF8 COLLATE utf8_bin',
        (CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE '' END), ';')
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = 'my_database_name'
AND DATA_TYPE = 'varchar'
AND
(
    CHARACTER_SET_NAME != 'utf8'
    OR
    COLLATION_NAME != 'utf8_bin'
);


--Change columns other than Varchar
SELECT CONCAT('ALTER TABLE `', table_name,
    '` MODIFY `', column_name, '` ', DATA_TYPE,
    ' CHARACTER SET UTF8 COLLATE utf8_bin',
    (CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE '' END), ';')
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = 'my_database_name'
AND DATA_TYPE != 'varchar'
AND
(
    CHARACTER_SET_NAME != 'utf8'
    OR
    COLLATION_NAME != 'utf8_bin'
);

Script for changing all the tables character set or collation

SELECT CONCAT('ALTER TABLE ',  table_name, ' CHARACTER SET utf8 COLLATE utf8_bin;')
FROM information_schema.TABLES AS T, information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` AS C
WHERE C.collation_name = T.table_collation
AND T.table_schema = 'my_database_name'
AND
(
    C.CHARACTER_SET_NAME != 'utf8'
    OR
    C.COLLATION_NAME != 'utf8_bin'
);