解决办法: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