MySql存储数据报错ER_TRUNCATED_WRONG_VA

ER_TRUNCATED_WRONG_VALUE_FOR_FIELD: Incorrect string value: '\xF0\xAC\x89\xBC'

经过查证，这个原因是MySql编码的问题。
UTF-8编码有可能是两个、三个、四个字节。Emoji表情或者某些特殊字符是4个字节，而Mysql的utf8编码最多3个字节，所以数据插不进去。

解决方法：（Mac）
1:找到my.cnf

sudo find / -name my.cnf

/usr/local/etc/my.cnf
/usr/local/Cellar/mysql/8.0.12/.bottle/etc/my.cnf

2:修改编码方式
把下面配置放到my.cnf中

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_bin  #utf8mb4_unicode_ci 会导致部分字符查询出错

3:重启MySql服务

mysql.server restart

3.1:登陆MySql，查看配置是否生效

mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';

# 如果是下面的就是生效了
+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client     | utf8mb4            |
| character_set_connection | utf8mb4            |
| character_set_database   | utf8mb4            |
| character_set_filesystem | binary             |
| character_set_results    | utf8mb4            |
| character_set_server     | utf8mb4            |
| character_set_system     | utf8               |
| collation_connection     | utf8mb4_unicode_ci |
| collation_database       | utf8mb4_unicode_ci |
| collation_server         | utf8mb4_unicode_ci |
+--------------------------+--------------------+
10 rows in set (0.00 sec)

4:重新创建数据库

create database 我的数据库名称;

5:创建表

create table dictionary(
   number VARCHAR(8) NOT NULL,
   text VARCHAR(255) NOT NULL,
   PRIMARY KEY ( text )
);

6:查看表的编码

mysql> show create table dictionary;  # dictionary 是表名
+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                                                                                |
+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| dictionary | CREATE TABLE `dictionary` (
  `number` varchar(8) COLLATE utf8mb4_unicode_ci NOT NULL,
  `text` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`text`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

https://stackoverflow.com/questions/20411440/incorrect-string-value-xf0-x9f-x8e-xb6-xf0-x9f-mysql

https://mathiasbynens.be/notes/mysql-utf8mb4#character-sets