ERROR 1366 (HY000): Incorrect string value错误解决办法
通过命令查看Mysql默认字符集的相关设置:
mysql> SHOW VARIABLES LIKE 'character%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)
可以看到,我虽然设置了部分为utf8,但是还有一个默认为拉丁文,可以通过下面方法修改:
方法一:使用配置文件
在Mysql的安装根目录下,my.ini中:
default-character-set = utf8
character_set_server = utf8
Mysql默认字符集,可以在安装时配置,也可以通过Mysql自带的MySQL Server Instance Config Wizard重新配置,配置后重启服务才可生效。
方法二:使用命令修改
mysql> SET character_set_client = utf8 ;
mysql> SET character_set_connection = utf8 ;
mysql> SET character_set_database = utf8 ;
mysql> SET character_set_results = utf8 ;
mysql> SET character_set_server = utf8 ;
mysql> SET collation_connection = utf8 ;
mysql> SET collation_database = utf8 ;
mysql> SET collation_server = utf8 ;
由于版本不同,可能后面命令会执行错误,请忽略即可。修改完成后重启服务即可。
重启服务后再次查看:
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 | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)