Change database and all tables character set

In some cases, the database character set might be ‘latin1’ and OpenSpecimen needs it ‘utf8’.

SQL to change the character set of database.

ALTER DATABASE <database_name> CHARACTER SET utf8 COLLATE utf8_unicode_ci;;

Note: This does not convert existing tables, it only sets the default for newly created tables.

SQL to change the character set of single table.

ALTER TABLE <table_name> CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Follow below steps to change charter set and collation of all tables present into database.

Step 1 : Create ‘change_charset.sql’ file which will convert the character set and collation of all tables present in database.

mysql> use <database_name>;
mysql> tee change_charset.sql
mysql> SELECT CONCAT("ALTER TABLE `", TABLE_NAME,"` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;") 
  as charset_change_query
FROM 
  INFORMATION_SCHEMA.TABLES
WHERE 
  TABLE_SCHEMA="<database_name>" AND
  TABLE_TYPE="BASE TABLE";

mysql> notee;

Step 2 : Open the file and remove unwanted table structure from the change_charset.sql file.

Step 3 : Again login into database and run source change_charset.sql command.

Step 4: SQL to check if table’s collation or character set is changed.

1
2
3

show create table <table_name>;

Example: show create table catissue_user;