1.测试插入脚本

<?php
$conn = mysql_connect("127.0.0.1","root","ESBecs00!@#") or die ("wrong!");
$sel=mysql_select_db("test",$conn);
$contents = <<<EOT
"\ud83d\udca5"
EOT;
$contents = json_decode($contents). "hello";
echo $contents, "\t\n";
$sql="insert into testtab (`contents`) VALUES('$contents')";
for($i = 0;$i<1;$i++)
{
$que=mysql_query($sql,$conn);
while ($row = mysql_fetch_assoc($que)) {
var_dump($row);
}
}
mysql_close($conn);
?>

在utf8的背景下

数据库端字符集为
1. mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
2. +--------------------------+-----------------+
3. | Variable_name | Value |
4. +--------------------------+-----------------+
5. | character_set_client | utf8 |
6. | character_set_connection | utf8 |
7. | character_set_database | utf8 |
8. | character_set_filesystem | binary |
9. | character_set_results | utf8 |
10. | character_set_server | utf8 |
11. | character_set_system | utf8 |
12. | collation_connection | utf8_general_ci |
13. | collation_database | utf8_unicode_ci |
14. | collation_server | utf8_unicode_ci |
15. +--------------------------+-----------------+

mysql> show create table testtab\G;
*************************** 1. row ***************************
Table: testtab
Create Table: CREATE TABLE `testtab` (
`contents` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

[root@host-192-168-1-56 ~]# php test.php
hello

检查数据
1. MySQL [test]> select * from testtab;
  +----------+
  | contents |
  +----------+
  | |
  | |
  +----------+
3. 空字符串有时会报这种错： Incorrect string value: '\xF0\x9F\x92\x94' for column 'name' at row 1

若在线修改字符集

mysql> ALTER TABLE testtab CONVERT TO CHARACTER SET utf8mb4;
Query OK, 0 rows affected (0.34 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table testtab modify column `contents` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Query OK, 0 rows affected (0.36 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> show create table testtab\G;
*************************** 1. row ***************************
Table: testtab
Create Table: CREATE TABLE `testtab` (
`contents` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

运行插入脚本
检查数据
MySQL [test]> select * from testtab;
+-----------+
| contents |
+-----------+
| |
| |
| ????hello | --插进去，因为没有前端测试，不知道是乱码还是正常的
+-----------+
3 rows in set (0.00 sec)

修改配置文件重启数据库

[client]
port=3306
socket=/var/lib/mysql/mysql.sock
default-character-set=utf8mb4
[mysqld]
character_set_server = utf8mb4
collation-server = utf8mb4_unicode_ci

MySQL [test]> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
+--------------------------+--------------------+
| Variable_name | Value |
+--------------------------+--------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| collation_connection | utf8_general_ci |
| collation_database | utf8mb4_unicode_ci |
| collation_server | utf8mb4_unicode_ci |
+--------------------------+--------------------+

再转换表

mysql> ALTER TABLE testtab CONVERT TO CHARACTER SET utf8mb4;
Query OK, 0 rows affected (0.34 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table testtab modify column `contents` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Query OK, 0 rows affected (0.36 sec)
Records: 0 Duplicates: 0 Warnings: 0

运行插入脚本
查看数据
mysql> select * from testtab;
+-----------+
| contents |
+-----------+
| |
| |
| ????hello |
| ?hello | ---这条应该是正常的，四个？的应该是乱码
+-----------+

注意：这样是对的。
说下前提，utf8mb4和utf8的区别就是utf8才用3位字节码存储数据，而utf8mb4用4位字节码存储数据。
emoji表情是4位字节码通过前段转义出来的，它本身是不可读的字符，所以在数据库客户端中会显示“？”。
在前端支持emoji的地方就会显示emoji表情。

如果想在utf8的背景下插入emoji表情，得修改配置文件，重启数据库啊！
参考：
https://segmentfault.com/q/1010000005996045
http://blog.csdn.net/dqchouyang/article/details/51473544
http://www.tuicool.com/articles/zAnEV3
http://www.innomysql.com/article/25722.html -----姜成尧
https://mp.weixin.qq.com/s/VWVKy16gMJ_kFeICsyMiVw ---老叶茶馆
http://mp.weixin.qq.com/s/HjzVoWe1fky0VfzhHi8uCg ----ACUMG mysql使用utf8mb4 经验吐血总结

ITPub博客

【Mysql】关于mysql存入emoji表情的问题

最新文章