When the character set of MySQL column is utf8 and the SQL mode (sql_mode) is not strict mode (i.e. sql_mode does not include STRICT_ALL_TABLES nor STRICT_TRANS_TABLES), setting a character that will be 4 bytes when encoded with utf-8 (such as an emoji like \ud83d\ude01) will truncate the remainder of the characters (with a warning).<\/p>\n\n\n\n
To support 4 bytes UTF-8 characters, the columns with utf8mb4 for CHARACTER SET (and utf8mb4_xxx such as utf8mb4_unicode_520_ci, etc for COLLATE) must be used, and the connection character set must also use utf8mb4.<\/p>\n\n\n\n
Incidentally, on Rails, if you try to set 4 bytes UTF-8 characters when the character set of MySQL column is utf8, the following error occurs, so the string will not be truncated unnoticed.<\/p>\n\n\n\n
An ActiveRecord::StatementInvalid occurred in news#update:<\/p>
Mysql2::Error: Incorrect string value: 'xF0x9Fx98x80x0Dx0A' for column 'description' at row 1: UPDATE `news` SET `description` = '\ud83d\ude00rn' WHERE `news`.`id` = 2
app\/controllers\/news_controller.rb:98:in `update'<\/p><\/blockquote>\n\n\n\nThis is because, unless otherwise specified, AbstractMysqlAdapter#configure_connection<\/a> adds STRICT_ALL_TABLES to the session's SQL_MODE. (NO_AUTO_VALUE_ON_ZERO is also added.)<\/p>\n\n\n\n
You can confirm it by doing the following.<\/p>\n\n\n\n
\n
- With the mysql client\n
mysql> show variables like 'sql_mode';\n+---------------+------------------------+\n| Variable_name | Value |\n+---------------+------------------------+\n| sql_mode | NO_ENGINE_SUBSTITUTION |\n+---------------+------------------------+\n1 row in set (0.00 sec)\n<\/pre>\n<\/li>\n- With the Rails console against the same database\n
> con = ActiveRecord::Base.connection\n> con.select_all(\"SHOW VARIABLES LIKE 'sql_mode'\")\n (0.8ms) SHOW VARIABLES LIKE 'sql_mode'\n => #<ActiveRecord::Result:0x00007fc6ca533728 @columns=[\"Variable_name\", \"Value\"], @rows=[[\"sql_mode\", \"NO_AUTO_VALUE_ON_ZERO,STRICT_ALL_TABLES,NO_ENGINE_SUBSTITUTION\"]], @hash_rows=nil, @column_types={}>\n<\/pre>\n<\/li>\n<\/ul>\n\n\n\nWorkaround<\/h2>\n\n\n\n
You can convert the CHARACTER SET of the column to utf8mb4 and COLLATE to utf8mb4_xxx and use utf8mb4 for the connection character set, but if you can't convert the column to utf8mb4 for some reason, you'll probably want to reject 4 bytes UTF-8 characters with validation because it's not good to just shut up and truncate the 4 bytes UTF-8 characters and beyond.
The range of Unicode characters that result in 4 bytes when encoded in UTF-8 is U+10000 to U+10FFFF.<\/p>\n\n\n\n
- https:\/\/en.wikipedia.org\/wiki\/UTF-8#Description<\/a><\/li>
- https:\/\/en.wikipedia.org\/wiki\/Unicode#Code_point_planes_and_blocks<\/a><\/li><\/ul>\n\n\n\n
On PHP<\/h3>\n\n\n\n
if (preg_match('\/[x{10000}-x{10FFFF}]\/u', $s) { \/* ... *\/ }\n<\/pre>\nif (preg_match('\/[xF0-xF7][x80-xBF][x80-xBF][x80-xBF]\/', $s)) { \/* ... *\/ }\n<\/pre>\npreg_match_all('\/[x{10000}-x{10FFFF}]\/u', $s, $matches);\n\/\/ An array of 4-bytes UTF-8 characters is stored in `$matches[0]`.\n<\/pre>\n\n\n\nOn Ruby<\/h3>\n\n\n\n
if \/[u{10000}-u{10FFFF}]\/ =~ s\n # ...\nend\n<\/pre>\n\nchars = s.scan(\/[u{10000}-u{10FFFF}]\/)\n# An array of 4-bytes UTF-8 characters is stored in `chars`.\n<\/pre>\n","protected":false},"excerpt":{"rendered":"When the character set of MySQL column is utf8 and the SQL mode (sql_mode) is not strict mode (i.e. sql_mode d … Continue reading Validating 4 bytes UTF-8 characters<\/span>