Sometime ago I wrote a blog post about how to properly create custom tables in WordPress that are also compatible with WordPress Multisite.
One thing that I didn’t mention in that article is setting the correct character set and collation for those custom tables.
What are character set and collation?
From MySQL docs:
A character set is a set of symbols and encodings. A collation is a set of rules for comparing characters in a character set.
You will be specifying the character set and collation as part of the
CREATE TABLE sql query.
What values should be used for character set and collation?
WordPress has a complex logic to figure out the correct character set and collation for each instance and provides a way to override them in
WordPress also does a lot of security checks and rejects text-based on the character set and collation of the tables. So if you are creating custom tables in WordPress then instead of hard coding the character set and collation (or leaving it blank) you should use the one that WordPress had determined.
You can use the
get_charset_collate() function in
wpdb class to get this value. Append the value returned by this function at the end of your
CREATE TABLE sql.