不積矽步，無以至千里。
本文記錄在使用MySQL過程中遇到的問題：
mysql> insert into emp values (1, '陽仔');
ERROR 1366 (HY000): Incorrect string value: '\xE9\x98\xB3\xE4\xBB\x94' for column 'name' at row 1
mysql>
mysql> show variables like 'character_set%';
+--------------------------+----------------------------+
| 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.00 sec)
mysql>
character_set_database和character_set_server都是latin1字元集，和使用者端character_set_client不一致，所以出現」錯誤碼 1366：不正確的string值「的報錯。
解決思路：
1、修改當前資料庫字元集；
檢視表emp2所有欄位的字元集collation欄位：
mysql> show full columns from emp2;
+-------+-------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+-------+-------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
| name | varchar(20) | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | |
+-------+-------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
1 row in set (0.01 sec)
修改表emp2的字元集和使用者端一致;
mysql> alter table emp2 convert to character set 'utf8';
Query OK, 0 rows affected (0.15 sec)
Records: 0 Duplicates: 0 Warnings: 0
檢視表emp2修改後的字元集collection欄位collection;
mysql> show full columns from emp2;
+-------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+-------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| name | varchar(20) | utf8_general_ci | YES | | NULL | | select,insert,update,references | |
+-------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
1 row in set (0.03 sec)
修改表emp2所屬資料庫的字元集為utf8，修改後新建的表的字元集就為utf8;
mysql> alter database lbydb character set 'utf8';
Query OK, 1 row affected (0.00 sec)
mysql> create table emp (name varchar(20));
Query OK, 0 rows affected (0.08 sec)
mysql> show full columns from emp;
+-------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+-------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| name | varchar(20) | utf8_general_ci | YES | | NULL | | select,insert,update,references | |
+-------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
1 row in set (0.00 sec)
2、永久修改MySQL預設設定；
如果在建庫建表的時候，沒有明確指定字元集，則採用預設的字元集latin1,其中是不包含中文字元的。可以修改/etc/my.cnf組態檔達到永久修改字元集的目的。新增如下設定：
[mysqld]
character_set_server=utf8
collation-server=utf8_general_ci
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
修改前：
mysql> show variables like 'character_set%';
+--------------------------+----------------------------+
| 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.00 sec)
mysql>
修改後：
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.01 sec)
mysql>
備註：以下修改只是臨時修改，當前有效。
/*建立連線使用的編碼*/
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;
青山不改，綠水長流。