程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> MySQL字符編碼問題,Incorrectstringvalue

MySQL字符編碼問題,Incorrectstringvalue

編輯:MySQL綜合教程

MySQL字符編碼問題,Incorrectstringvalue


MySQL上插入漢字時報錯如下,具體見後面分析。
Incorrect string value: '\xD0\xC2\xC8A\xBEW' for column 'ctnr' at row 1

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)

2
mysql> create table tb_tt (id int, ctnr varchar(60));
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)

4
mysql> insert into tb_tt(id,ctnr) values(1,'新華網');
Query OK, 1 row affected (0.02 sec)

5
mysql> select * from tb_tt;
+------+--------+
| id | ctnr |
+------+--------+
| 1 | 新華網 |
+------+--------+
1 row in set (0.02 sec)

6
mysql> set names 'UTF8';
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)

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

參考:

http://dev.mysql.com/doc/refman/5.5/en/globalization.html 

-----------------

轉載請著明出處:
blog.csdn.net/beiigang

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved