PHP+MySQL 存储 emoji 字符

问题概述

执行 SQL 语句 INSERT INTO comment (`content`) VALUES ( '😁😄🙂👩'); 程序提示（😭）

SQLSTATE[HY000]: General error: 1366 Incorrect string value: '\\xF0\\x9F\\x98\\x81\\xF0\\x9F...' for column 'content' at row 1\n ....

一般我们建库建表时默认的字符编码是 utf8 或者是 latin1； 通过下面SQL语句知 MySQL的 latin 字符集是 1 字节， utf8 字符集是 3 字节。但是 存储一个emoji 是需要 4 字节。使得使用 utf8 编码存储 emoji 异常！

select * from information_schema.CHARACTER_SETS where CHARACTER_SET_NAME like 'latin1%';

select * from information_schema.CHARACTER_SETS where CHARACTER_SET_NAME like 'utf8%';

字符编码字节大小查看

为了满足 emoji 存储所需的 4 个字节，就需要将 数据库 / 表的的字符编码设置为 utf8mb4；
这里是一个myTest数据库和comment数据表的新建实例

create database myTest default character set utf8mb4;

create table info(
id int(11) not null auto_increment primary key,
content VARCHAR(255) not null
)ENGINE=InnoDB DEFAULT  CHARSET=utf8mb4 ;

PHP 测试程序

<?php

class test
{

    public $host;
    public $user;
    public $password;
    public $dbname;

    public $conn = null;

    public function __construct()
    {
        $this->host = "localhost";
        $this->user = "root";
        $this->password = "11019";
        $this->dbname   = 'myTest';

        $this->conn = new mysqli($this->host, $this->user, $this->password, $this->dbname);

        if ($this->conn->connect_error) {
            die("连接失败: " . $this->conn->connect_error);
        }
        $this->conn->query('set names utf8mb4');


    }

    public function mysql()
    {
        $sql = 'INSERT INTO info(id,content) VALUES(1,"😄呵呵😁")';

        if ($this->conn->query($sql) === TRUE) {
            $rest = $this->conn->insert_id;
        } else {
            $errorInfo = $this->conn->error;
            $rest = $errorInfo;
        }
        print_r($rest);
    }

}

$mongoData = new test();

$mongoData->mysql();

?>

特别说明：程序中必须要设置 "set names utf8mb4"。如果是使用的一些PHP 框架，（这里以 Yii2 为例），需要设置 数据库 collection 为 utf8mb4

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

PHPMyAdmin 查看插入的 emoji

PHPMyAdmin

程序打印查看

public function getData()
    {
        $sql = 'select * from info';
        $result = $this->conn->query($sql);
        if ($result->num_rows > 0) {
            while($row = $result->fetch_assoc()) {
                echo '<pre>';
                print_r($row);
            }
        } else {
            echo "0 results";
        }
        $this->conn->close();
    }

内容打印

数据导出时字符编码设置

mysqldump --default-charater-set=utf8mb4

参考链接

segmentfault：如何转义emoji表情，让它可以存入utf8的数据库？

V2EX：关于用 MySQL 存储 Emoji