Hugh's Blog

MySQL 保存 4 字节字符问题

 

今天在保存内容到数据库的时候报如下错误:

Incorrect string value: '\xF0\xA2\xB9\x82\xE5\xB8...' for column 'content' at row 1

本来还以为是多出来非法字符或者字符编码的问题,但试过了都不是,而 \xF0\xA2\xB9\x82\xE5\xB8 这些都是由字符转为 16 进制得出来的,最后还是谷歌找到了问题原因,还是编码的原因,只不过是 MySQL 的编码。

一般来说,正常汉字不会超过 3 个字节,但内容由于是繁体字,总会出现一些生僻字为 4 个字节 (当然,有的 emoji 表情符号也是 4 个字节),而 MySQL 的 UTF-8 编码只能支持 1-3 个字节,如果想保存 4 个字节的字符,则需要把字符集修改为 utf8mb4,而且 MySQL 的版本要高于 5.5.3。

根据前面 4 个字节 \xF0\xA2\xB9\x82,可以得到一个不常见的繁体字,可以到这个网站进行转换。

修改字符集:

ALTER DATABASE `database_name` CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
ALTER TABLE `table_name` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `table_name` CHANGE `column_name` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

注意,也要修改数据库连接所选择的字符集为 utf8mb4,例如 Yii:

return [
    'components' => [
        'db' => [
            'class' => 'yii\db\Connection',
            'dsn' => 'mysql:host=localhost;dbname=yii2',
            'username' => 'root',
            'password' => '',
            'charset' => 'utf8mb4',
            'tablePrefix' => 'yii2_',
        ],
    ],
];

2017-11-10

今天在尝试导出内容到 Excel 也是出现 4 字节字符识别不了的情况,打开文件后提示说识别不了某些字符,导致该字符后面所有行的数据大多数单元格为空。

处理方法有两种,但并不完美。

第一种是把 4 字节的字符去掉或者用空格代替,对于字符不多的内容倒无所谓,但是字符多或者不允许丢失内容的话,这种方法就不好了。

// 去除 4 字节字符
$newContent = preg_replace('/[\xF0-\xF7].../s', ' ', $content);

第二种是把导出格式更换为 Excel2007(xlsx),但是我打开导出的文件却弹出错误提示 Calculation is incomplete,不知道是不是个例,如果是个例的话这应该是比较好的方法了,参考下面有 PHPExcel 导出 xlsx 的例子。


参考

Incorrect string value: \xF0\x9F\x98\x84\xF0\x9F

Can Support emoji expression Export to excel?

PHPExcel download xlsx example