ERROR 1366 (HY000): Incorrect string value: '\xE9\x83\x91\xE5\xB7\x9E' for column 'aa' at row 1創建表之後不能插入中文字元？為啥呢？瞭解字元集的重要性。它必須在建庫之前要確定好，恢復備份時也需要註意 mys ...
ERROR 1366 (HY000): Incorrect string value: '\xE9\x83\x91\xE5\xB7\x9E' for column 'aa' at row 1
創建表之後不能插入中文字元？為啥呢？
瞭解字元集的重要性。它必須在建庫之前要確定好，恢復備份時也需要註意
mysql> use test
Database changed
mysql> create table zgy(aa char(20));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into zgy values('zgzg');
Query OK, 1 row affected (0.01 sec)
mysql> insert into zgy values('鄭州');
ERROR 1366 (HY000): Incorrect string value: '\xE9\x83\x91\xE5\xB7\x9E' for column 'aa' at row 1
mysql> show variables like '%char%';
+--------------------------+----------------------------------+
| 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/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.00 sec)
這部分已執行
set character_set_client=utf8;
set character_set_connection=utf8;
set character_set_database=utf8;
set character_set_results=utf8;
set character_set_server=utf8;
set character_set_system=utf8;
set collation_connection=utf8;
set collation_database=utf8;
set collation_server=utf8;
set character_set_server=utf8;
mysql> show variables like'%char%';
+--------------------------+----------------------------------+
| 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/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.00 sec)
仍然報錯？
mysql> insert into zgy values('鄭州');
ERROR 1366 (HY000): Incorrect string value: '\xE9\x83\x91\xE5\xB7\x9E' for column 'aa' at row 1
這時候只是將DB的改成utf8了，而zgy表是之前創建的
mysql> alter database test character set utf8;
Query OK, 1 row affected (0.00 sec)
mysql> show create database test;
+----------+---------------------------------------------------------------+
| Database | Create Database |
+----------+---------------------------------------------------------------+
| test | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+---------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create table zgy;
+-------+------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------+
| zgy | CREATE TABLE `zgy` (
`aa` char(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
改變表的字元集，發現還不行
mysql> alter table zgy character set utf8;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> insert into zgy values('鄭州');
ERROR 1366 (HY000): Incorrect string value: '\xE9\x83\x91\xE5\xB7\x9E' for column 'aa' at row 1
mysql>
mysql> show create table zgy;
+-------+-------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------+
| zgy | CREATE TABLE `zgy` (
`aa` char(20) CHARACTER SET latin1 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
修改欄位的字元集
創建庫時預設已經指定了表和欄位等對象的字元集
mysql> alter table zgy aa char(20) CHARACTER SET utf8;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'aa char(20) CHARACTER SET utf8' at line 1
mysql> ALTER TABLE zgy CHANGE aa aa char(20) CHARACTER SET utf8;
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> show create table zgy;
+-------+----------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------+
| zgy | CREATE TABLE `zgy` (
`aa` char(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> insert into zgy values('鄭州');
Query OK, 1 row affected (0.01 sec)
mysql> insert into zgy values('深圳');
Query OK, 1 row affected (0.00 sec)
mysql> select * from zgy;
+--------------+
| aa |
+--------------+
| zgzg |
| asasas121212 |
| 鄭州 |
| 深圳 |
+--------------+
4 rows in set (0.00 sec)
小常識：
看到是gbk和latin1說明不支持中文，所以需要修改字元類型
在latin1中，每個字元只有一個位元組長。在utf8中，一個字元可以由多個位元組組成。因此，utf8具有比latin1更多的字元（並且它們具有的字元不一定由相同的位元組/位元組序列表示）。但是我們的中文漢字是要2個位元組還是3個位元組哦，所有latin1就不能存儲漢字啦。
優點：一勞永逸，在創建資料庫時就設置為utf8格式，以後再次資料庫下創建表時以及添加表數據時都不用擔心中文字元問題。
CREATE database testdb DEFAULT CHARACTER SET utf8;
一些關於查看和修改字元集的MySQL知識：
查看mysql的字元集：show variables where Variable_name like '%char%';
查看某一個資料庫字元集：show create database enterprises;(註：enterprises為資料庫)
查看某一個數據表字元集：show create table employees;(註：employees為數據表)
修改mysql的字元集
set character_set_client=utf8;
set character_set_connection=utf8;
set character_set_database=utf8;
set character_set_results=utf8;
set character_set_server=utf8;
set character_set_system=utf8;
set collation_connection=utf8;
set collation_database=utf8;
set collation_server=utf8;
修改資料庫enterprises的字元集：
alter database enterprises character set utf8
修改數據表employees的字元集：
alter table employees character set utf8
修改欄位的字元集
alter table employees change name name char(10) character set utf-8;
