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.

1 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.

1 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.

1 2 3 4 5 6 7 8 9 10 11 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;