mysql存储value_MySql存储数据报错ER_TRUNCATED_WRONG_VALUE_FOR_FIELD: Incorrect string value

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)