Mysql ER_TRUNCATED_WRONG_VALUE_FOR_FIELD: Incorrect string value

chuchur

发布于 2022-10-25 15:03:09

2.1K0

文章被收录于专栏：禅境花园禅境花园

mysql 错误

ER_TRUNCATED_WRONG_VALUE_FOR_FIELD: Incorrect string value

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

1:找到my.cnf

sudo find / -name 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_unicode_ci

3:重启MySql服务

mysql.server restart  #for  mac
#or
systemctl restart mysqld.service  # for linux

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

mysql -u root -p

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)

重新创建数据库

create database 我的数据库名称;

创建表

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

查看表的编码

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)

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2021/11/29 ，如有侵权请联系 cloudcommunity@tencent.com 删除

数据库