问题概述
执行 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
参考链接
