MySQL常見問題總結

不積硅步，無以至千里，

本文記錄在使用MySQL程序中遇到的問題：

1、字符集問題

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;

青山不改，綠水長流，