MySQL字符编码问题\x，Incorrect string value
[ 2016-07-06 15:23:49 | 作者: admin ]
MySQL上插入汉字时报错如下，具体见后面分析。
p.s. 如果手工修改了表的字段的字符集，需要导出表后重建该表，不然字符集不会生效。
MySQL字符集相关参数：
character_set_server ： 服务器字符集
collation_server ： 服务器校对规则
character_set_database ： 默认数据库的字符集
collation_database ： 默认数据库的校对规则
character_set_client：服务器使用该变量取得链接中客户端的字符集
character_set_connection：服务器将客户端的query从character_set_client转换到该变量指定的字符集。
character_set_results：服务器发送结果集或返回错误信息到客户端之前应该转换为该变量指定的字符集
有两个语句可以设置连接字符集，如下：
A
SET NAMES 'charset_name' 相当于下面三句：
mysql> SET character_set_client = x;
mysql> SET character_set_results = x;
mysql> SET character_set_connection = x; #这个也设置了collation_connection的默认值x
B
SET CHARACTER SET charset_name 相当于下面三句：
mysql> SET character_set_client = x;
mysql> SET character_set_results = x;
mysql> SET collation_connection = @@collation_database;
character_set_results为NULL时，服务器对返回结果集不做任何转换
mysql> SET character_set_results = NULL;
因为字符集编码引起的问题在pg上报的错是：invalid byte sequence for encoding "UTF8"，具体见参考
http://blog.csdn.net/beiigang/article/details/39582583
MySQL上字符集编码引起的问题报：Incorrect string value，具体见下面实验：
1
2
3
4
5
6
7
8
问题和原因：
如果更改了数据库的默认字符集或排序规则，使用数据库默认值的存储例程必须被删除和重新让他们使用新的默认值。（在存储程序中，
字符数据类型的变量使用数据库默认值，如果字符集或排序规则未显式指定。看到[帮助创建程序]。
If you change the default character set or collation for a database,stored routines that use the database defaults must be dropped and
recreated so that they use the new defaults. (In a stored routine,variables with character data types use the database defaults if the
character set or collation are not specified explicitly. See [HELP CREATE PROCEDURE].)
参考：
http://dev.mysql.com/doc/refman/5.5/en/globalization.html
http://dev.mysql.com/doc/refman/5.5/en/alter-database.html
-----------------
转载请著明出处：
blog.csdn.net/beiigang
评论Feed: http://blog.xg98.com/feed.asp?q=comment&id=2320
Incorrect string value: '\xD0\xC2\xC8A\xBEW' for column 'ctnr' at row 1
p.s. 如果手工修改了表的字段的字符集，需要导出表后重建该表，不然字符集不会生效。
MySQL字符集相关参数：
character_set_server ： 服务器字符集
collation_server ： 服务器校对规则
character_set_database ： 默认数据库的字符集
collation_database ： 默认数据库的校对规则
character_set_client：服务器使用该变量取得链接中客户端的字符集
character_set_connection：服务器将客户端的query从character_set_client转换到该变量指定的字符集。
character_set_results：服务器发送结果集或返回错误信息到客户端之前应该转换为该变量指定的字符集
有两个语句可以设置连接字符集，如下：
A
SET NAMES 'charset_name' 相当于下面三句：
mysql> SET character_set_client = x;
mysql> SET character_set_results = x;
mysql> SET character_set_connection = x; #这个也设置了collation_connection的默认值x
B
SET CHARACTER SET charset_name 相当于下面三句：
mysql> SET character_set_client = x;
mysql> SET character_set_results = x;
mysql> SET collation_connection = @@collation_database;
character_set_results为NULL时，服务器对返回结果集不做任何转换
mysql> SET character_set_results = NULL;
因为字符集编码引起的问题在pg上报的错是：invalid byte sequence for encoding "UTF8"，具体见参考
http://blog.csdn.net/beiigang/article/details/39582583
MySQL上字符集编码引起的问题报：Incorrect string value，具体见下面实验：
1
mysql> show variables like '%character_set%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | gbk |
| character_set_connection | gbk |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | gbk |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | gbk |
| character_set_connection | gbk |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | gbk |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
2
mysql> create table tb_tt (id int, ctnr varchar(60));
Query OK, 0 rows affected (0.06 sec)
Query OK, 0 rows affected (0.06 sec)
3
mysql> show create table tb_tt;
+-------+-----------------------------------------------------------------------
-----------------------------------------------------+
| Table | Create Table
|
+-------+-----------------------------------------------------------------------
-----------------------------------------------------+
| tb_tt | CREATE TABLE `tb_tt` (
`id` int(11) DEFAULT NULL,
`ctnr` varchar(60) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------
-----------------------------------------------------+
1 row in set (0.00 sec)
+-------+-----------------------------------------------------------------------
-----------------------------------------------------+
| Table | Create Table
|
+-------+-----------------------------------------------------------------------
-----------------------------------------------------+
| tb_tt | CREATE TABLE `tb_tt` (
`id` int(11) DEFAULT NULL,
`ctnr` varchar(60) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------
-----------------------------------------------------+
1 row in set (0.00 sec)
4
mysql> insert into tb_tt(id,ctnr) values(1,'新華網');
Query OK, 1 row affected (0.02 sec)
Query OK, 1 row affected (0.02 sec)
5
mysql> select * from tb_tt;
+------+--------+
| id | ctnr |
+------+--------+
| 1 | 新華網 |
+------+--------+
1 row in set (0.02 sec)
+------+--------+
| id | ctnr |
+------+--------+
| 1 | 新華網 |
+------+--------+
1 row in set (0.02 sec)
6
mysql> set names 'UTF8';
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
7
mysql> show variables like '%character_set%';
+--------------------------+----------------------------+
| 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.00 sec)
+--------------------------+----------------------------+
| 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.00 sec)
8
mysql> insert into tb_tt(id,ctnr) values(2,'新華網');
ERROR 1366 (HY000): Incorrect string value: '\xD0\xC2\xC8A\xBEW' for column 'ctnr' at row 1
ERROR 1366 (HY000): Incorrect string value: '\xD0\xC2\xC8A\xBEW' for column 'ctnr' at row 1
问题和原因：
如果更改了数据库的默认字符集或排序规则，使用数据库默认值的存储例程必须被删除和重新让他们使用新的默认值。（在存储程序中，
字符数据类型的变量使用数据库默认值，如果字符集或排序规则未显式指定。看到[帮助创建程序]。
If you change the default character set or collation for a database,stored routines that use the database defaults must be dropped and
recreated so that they use the new defaults. (In a stored routine,variables with character data types use the database defaults if the
character set or collation are not specified explicitly. See [HELP CREATE PROCEDURE].)
参考：
http://dev.mysql.com/doc/refman/5.5/en/globalization.html
http://dev.mysql.com/doc/refman/5.5/en/alter-database.html
-----------------
转载请著明出处：
blog.csdn.net/beiigang
[最后修改由 admin， 于 2016-07-06 15:35:07]
评论Feed: http://blog.xg98.com/feed.asp?q=comment&id=2320
这篇日志没有评论。
此日志不可发表评论。
