Using this example, you can change character set and collation for a MySQL database table(s). Most likely you will be need to do this if you haven’t specified character set and collation at the time of database/table creation and default character set/collation applied are not desirable. Below are settings for MySQL version
5.5.9 and onwards. Put them in [mysqld] character-set-server=utf8 collation-server = utf8_unicode_ci init-connect='SET NAMES utf8' init_connect='SET collation_connection = utf8_unicode_ci' skip-character-set-client-handshake Next, restart mysql and log into mysql shell: mysql> show variables like "%character%";show variables like "%collation%"; Sample output as: +--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+ 8 rows in set (0.00 sec) +----------------------+-----------------+ | Variable_name | Value | +----------------------+-----------------+ | collation_connection | utf8_general_ci | | collation_database | utf8_general_ci | | collation_server | utf8_general_ci | +----------------------+-----------------+ 3 rows in set (0.00 sec) Checking current character set and collation for database/table/columnsFor Database:SELECT default_character_set_name, default_collation_name FROM information_schema.SCHEMATA WHERE schema_name = "databasename"; It will show output as: +----------------------------+------------------------+ | default_character_set_name | default_collation_name | +----------------------------+------------------------+ | latin1 | latin1_swedish_ci | +----------------------------+------------------------+ For Tables:SELECT T.table_name, T.table_collation, CCSA.character_set_name FROM information_schema.`TABLES` T, information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA WHERE CCSA.collation_name = T.table_collation AND T.table_schema = "databasename"; Sample output as below: +-----------------------------------------------------+-------------------+--------------------+ | table_name | table_collation | character_set_name | +-----------------------------------------------------+-------------------+--------------------+ | wp_20_rtAccountToken | latin1_swedish_ci | latin1 | | wp_20_rtAccountVerify | latin1_swedish_ci | latin1 | | wp_20_rt_crm_mail_messageids | latin1_swedish_ci | latin1 | | wp_20_w3tc_cdn_queue | latin1_swedish_ci | latin1 | | gp_meta | utf8_general_ci | utf8 | +-----------------------------------------------------+-------------------+--------------------+ For Columns:SELECT table_name, column_name, character_set_name, collation_name FROM information_schema.`COLUMNS` C WHERE character_set_name != 'NULL' AND table_schema = "db_name" Sample Output: +------------------------+--------------+--------------------+-------------------+ | table_name | column_name | character_set_name | collation_name | +------------------------+--------------+--------------------+-------------------+ | wp_20_rtAccountToken | accesstoken | latin1 | latin1_swedish_ci | | wp_20_rtAccountToken | refreshtoken | latin1 | latin1_swedish_ci | | wp_20_rtAccountVerify | email | latin1 | latin1_swedish_ci | | wp_20_rtAccountVerify | type | latin1 | latin1_swedish_ci | | wp_20_rtAccountVerify | code | latin1 | latin1_swedish_ci | +------------------------+--------------+--------------------+-------------------+ Converting character set and collationsMAKE BACKUPWe are serious. Just use mysqldump rather than regretting it later Changing Database Character Sets and CollationsThis is simplest: ALTER DATABASE db_name CHARACTER SET utf8 COLLATE utf8_general_ci; Replace your database name with db_name. Also after running query verify if database-level defaults are changed indeed. Changing Tables Character Sets and CollationsBelow is a syntax to covert character set of alter table wp_posts convert to character set utf8 collate utf8_unicode_ci; alter table wp_postmeta convert to character set utf8 collate utf8_unicode_ci; If you want to covert all your MySQL tables, then run a command like below on database mysql -e "SELECT concat('alter table ', TABLE_NAME , ' convert to character set utf8 collate utf8_unicode_ci;') FROM information_schema.TABLES WHERE table_schema = 'db_wordpress' AND TABLE_COLLATION = 'latin1_swedish_ci'" | tail -n+2 > collation.sql After you run above query, check If all looks good, run following to convert all mysql tables to InnoDB. mysql db_wordpress < collation.sql Changing Column Character Sets and CollationsBelow is syntax to convert columns to utf8 alter table table_name change col_name col_name col_data_type character set utf8; Please note that we have to use same col_name twice! mysql> SELECT table_name, column_name, data_type, character_set_name, collation_name FROM information_schema.`COLUMNS` WHERE table_schema = "db_name" AND table_name = "table_name" AND column_name = "col_name"; Sample output: +--------------+--------------+-----------+ | table_name | column_name | data_type | +--------------+--------------+-----------+ | wp_posts | post_content | longtext | +--------------+--------------+-----------+ Example for wordpress’s wp_posts table alter table wp_posts change post_content post_content LONGTEXT CHARACTER SET utf8; Please be very careful for column conversion. Specially if you have non-english characters stored in database. In that case, you can refer to this WordPress Codex section. How do I change MySQL encoding to UTFTo change the character set encoding to UTF-8 for the database itself, type the following command at the mysql> prompt. Replace dbname with the database name: Copy ALTER DATABASE dbname CHARACTER SET utf8 COLLATE utf8_general_ci; To exit the mysql program, type \q at the mysql> prompt.
How do I change a character set from latin1 to utf8 in MySQL?Similarly, here's the command to change character set of MySQL table from latin1 to UTF8. Replace table_name with your database table name. mysql> ALTER TABLE table_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci; Hopefully, the above tutorial will help you change database character set to utf8mb4 (UTF-8).
How do I change utf8mb4 to utf8?To solve the problem open the exported SQL file, search and replace the utf8mb4 with utf8 , after that search and replace the utf8mb4_unicode_520_ci with utf8_general_ci . Save the file and import it into your database. After that, change the wp-config. php charset option to utf8 , and the magic starts.
How do I change the default character set in MySQL?The MySQL server has a compiled-in default character set and collation. To change these defaults, use the --character-set-server and --collation-server options when you start the server.
|