欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

解决办法:MySQL在插入含中文字符记录时报错问题

程序员文章站 2024-01-27 08:11:16
...

环境:

Windows10 cmd、mysql-5.5.40

问题描述:

在cmd中插入和更改一条含有中文字符的记录时,MySQL报出错误。
表的数据类型如下:

mysql> desc student;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| name     | varchar(100) | YES  |     | NULL    |       |
| id       | int(11)      | YES  |     | NULL    |       |
| age      | int(11)      | YES  |     | NULL    |       |
| birthday | date         | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
4 rows in set (0.03 sec)

cmd显示出原表内容如下:

mysql> select * from student;#先让cmd显示原表内容
+----------+------+------+------------+
| name     | id   | age  | birthday   |
+----------+------+------+------------+
| nihao    |    1 |   34 | 2000-03-04 |
| zhubajie |    2 |   24 | 2000-03-04 |
+----------+------+------+------------+
2 rows in set (0.00 sec)

在表中插入一条含中文字符的记录,MySQL提示错误如下(在更改表记录时也出现了这个问题):

mysql>  insert into student (name,id,age,birthday)values('唐僧',4,14,'2010-01-01');
ERROR 1366 (HY000): Incorrect string value: '\xCC\xC6\xC9\xAE' for column 'name' at row 1

查看字符集并未发现问题

mysql> show variables like 'character%';
+--------------------------+--------------------------+
| Variable_name            | Value                    |
+--------------------------+--------------------------+
| character_set_client     | utf8                     |
| character_set_connection | utf8                     |
| character_set_database   | utf8                     |
| character_set_filesystem | binary                   |
| character_set_results    | utf8                     |
| character_set_server     | utf8                     |
| character_set_system     | utf8                     |
| character_sets_dir       | D:\MySQL\share\charsets\ |
+--------------------------+--------------------------+
8 rows in set (0.00 sec)

解决办法:

1、先停止MySQL服务:net stop mysql
2、修改my.ini文件,主要是将默认字符集从utf8改为GBK,需要修改两处,修改内容如下:

[client]

port=3306

[mysql]
#对下面这句进行了修改,原句为:default-character-set=utf8
default-character-set=GBK
# The default character set that will be used when a new schema or table is
# created and no character set is defined
#对下面这句进行了修改,原句为:character-set-server=utf8
character-set-server=GBK

3、修改后的重新启动MySQL:net start mysql,再插入一条含有中文字符的记录时效果如下:

mysql>  insert into student (name,id,age,birthday)values('tang',3,14,'2010-01-01');
mysql>  insert into student (name,id,age,birthday)values('白马',4,54,'1910-01-01');
mysql> select * from student;
+----------+------+------+------------+
| name     | id   | age  | birthday   |
+----------+------+------+------------+
| nihao    |    1 |   34 | 2000-03-04 |
| zhubajie |    2 |   24 | 2000-03-04 |
| tang     |    3 |   14 | 2010-01-01 |
| 白马     |    4 |   54 | 1910-01-01 |
+----------+------+------+------------+

问题解决!!!

参考:

https://blog.csdn.net/fly19920602/article/details/72809310

相关标签: Javaweb