Change Character Set and Collation for MariaDB.

What are character sets and collations

  • A character set is a set of symbols and encodings.
  • A collation is the rules for comparing and sorting a particular character set.
  • A case-insensitive collation treats the lowercase letters “a” as equivalent to “A”.

In MariaDB

  • The default character set is latin1.
  • The default collation is latin1_swedish_ci.

The following steps will show how to change character set and collation for mariadb.

1) View full list of character sets and collations supported by Mariadb.

MariaDB [(none)]> SHOW CHARACTER SET;

Charset_Collation001

 

MariaDB [(none)]> SHOW COLLATION;

Charset_Collation002

Note: Here I only list a few collations in a long list.

2) How to determines and sets a character set and collation for a database.

2.1 Determine the default character set for a database name of testdb01.

MariaDB [(none)]>SHOW CREATE DATABASE testdb01;

Charset_Collation01

2.2 Set Charset and collation when creating a database.

MariaDB [(none)]>CREATE DATABASE testdb02 CHARACTER SET = ‘utf8mb4’ COLLATE = ‘utf8mb4_general_ci’;

2.3 Check both character set and collation of a database.

MariaDB [(none)]> SELECT * FROM INFORMATION_SCHEMA.SCHEMATA;

Charset_Collation02

2.4 Change charset and collation for a database by altering it.

MariaDB [(none)]> ALTER DATABASE testdb01 CHARACTER SET = ‘utf8’ COLLATE = ‘utf8_general_ci’;

2.5  Check the result after altering the database.

MariaDB [(none)]> SELECT * FROM INFORMATION_SCHEMA.SCHEMATA;

Charset_Collation03

 2.6 Check the Collation of a table;

Mariadb> SHOW TABLE STATUS LIKE ‘Persons’ \G ;

Charset_Collation04

2.7 View character set and collation of each column in a table.

MariaDB [testdb01]> SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME,CHARACTER_SET_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS;

Charset_Collation05

2.8 Convert Character set and collation of a table.

MariaDB [testdb01]> ALTER TABLE Persons CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Query OK, 3 rows affected (0.00 sec)

Records: 3 Duplicates: 0 Warnings: 0

2.9 Check the character set and collation again.

MariaDB [testdb01]> SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME,CHARACTER_SET_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS;

Charset_Collation06

2.10 Get table list of a database.

MariaDB [(none)]> SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=”Your_Database_Name” AND TABLE_TYPE=”BASE TABLE”;

Charset_Collation07

2.11 Create command to convert character set and collation of all tables in a database.

MariaDB [(none)]> SELECT CONCAT(‘ALTER TABLE `’, TABLE_NAME,’` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;’) AS sqlString FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA= “Your_Database_Name” AND TABLE_TYPE=”BASE TABLE”;

Charset_Collation08

2.11.1 If you have data with foreign key on non utf8 column running the following command before converting.

MariaDB [(none)]> SET foreign_key_checks = 0;

2.11.2 Convert character set and collation.

Mariadb> ALTER TABLE `Companies` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

Mariadb> ALTER TABLE `Persons` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

2.11.3 Enable foreign key check again.

MariaDB [(none)]> SET foreign_key_checks = 1;

 

1 Comment

Leave a Reply