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;