If you don’t care about any of that, simply go with the default, latin1. For that you’ll need utf8mb4, available since MySQL 5.5.3. Be careful, this encoding won’t handle smileys. If you need to handle special characters, like letters with accents, people will usually tell you to go for a utf8 encoding. You’ll find SQL commands at the end of this article to check and modify that. Note that you can set both encoding and collation at the server level, at the database level, at the table level or even for particular columns only. The collation is used when comparing data, for example in a WHERE clause (equal or LIKE), or with unicity constraints on text columns. Here’s a quick guide on how to choose your encoding and the collation right from the start, and avoid the pain of debugging your encoding issues. In that case, you can refer to this WordPress Codex section.You generally start with the defaults and fix issues along the way. Specially if you have non-english characters stored in database. Please be very careful for column conversion. Sample output: +-+-+-+Įxample for wordpress’s wp_posts table alter table wp_posts change post_content post_content LONGTEXT CHARACTER SET utf8 mysql db_wordpress 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" If all looks good, run following to convert all mysql tables to InnoDB. If collation.sql is empty, you probably do not have a table using MyISAM engine. If you want to covert all your MySQL tables, then run a command like below on database db_wordpress mysql -e "SELECT concat('alter table ', TABLE_NAME, ' convert to character set utf8 collate utf8_unicode_ci ')ĪND TABLE_COLLATION = 'latin1_swedish_ci'" |Īfter you run above query, check collation.sql content to verify if all rows are correct. alter table wp_posts convert to character set utf8 collate utf8_unicode_ci Īlter table wp_postmeta convert to character set utf8 collate utf8_unicode_ci Changing Tables Character Sets and Collationsīelow is a syntax to covert character set of wp_posts and wp_postmetatables. Also after running query verify if database-level defaults are changed indeed. This is simplest: ALTER DATABASE db_name CHARACTER SET utf8 COLLATE utf8_general_ci Just use mysqldump rather than regretting it later Changing Database Character Sets and Collations +-+-+-+-+ Converting character set and collations MAKE BACKUP | wp_20_rtAccountVerify | code | latin1 | latin1_swedish_ci | | wp_20_rtAccountVerify | type | latin1 | latin1_swedish_ci | | wp_20_rtAccountVerify | email | latin1 | latin1_swedish_ci | | wp_20_rtAccountToken | refreshtoken | latin1 | latin1_swedish_ci | | wp_20_rtAccountToken | accesstoken | latin1 | latin1_swedish_ci | | table_name | column_name | character_set_name | collation_name | WHERE character_set_name != 'NULL' AND table_schema = "db_name" +-+-+-+ For Columns: SELECT table_name, column_name, character_set_name, collation_name FROM information_schema.`COLUMNS` C | wp_20_w3tc_cdn_queue | latin1_swedish_ci | latin1 | | wp_20_rt_crm_mail_messageids | latin1_swedish_ci | latin1 | | wp_20_rtAccountVerify | latin1_swedish_ci | latin1 | | wp_20_rtAccountToken | latin1_swedish_ci | latin1 | | table_name | table_collation | character_set_name | Information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA +-+-+ For Tables: SELECT T.table_name, T.table_collation, CCSA.character_set_name FROM information_schema.`TABLES` T, | default_character_set_name | default_collation_name | | collation_connection | utf8_general_ci |ģ rows in set (0.00 sec) Checking current character set and collation for database/table/columns For Database: SELECT default_character_set_name, default_collation_name FROM information_schema.SCHEMATA | character_sets_dir | /usr/share/mysql/charsets/ | Next, restart mysql and log into mysql shell: mysql> show variables like "%character%" show variables like "%collation%" Init_connect='SET collation_connection = utf8_unicode_ci' Please be careful as some settings might be already present. Put them in /etc/mysql/my.cnf is correct sections. Setting MySQL default character set and collation in my.cnfīelow are settings for MySQL version 5.5.9 and onwards. 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. Using this example, you can change character set and collation for a MySQL database table(s).
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |