linux下設定mysql資料庫字符集utf8

記憶殘留發表於2016-09-23
LinuxMySql資料庫

mysql中文亂碼解決方法：將mysql資料庫編碼統一utf8

檢視資料庫編碼：

show variables like 'character%';

 

編輯/etc/my.cnf

[mysql]
default-character-set=utf8
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
default-character-set = utf8
character_set_server = utf8
#lower_case_table_names=1  忽略大小寫
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
default-character-set = utf8
[mysql.server]
default-character-set = utf8
[client]
default-character-set = utf8

　　

 

修改完成後，service mysql restart重啟mysql服務就生效。注意：[mysqld]欄位與[mysql]欄位是有區別的。　　

如果上面的都修改了還亂碼，那剩下問題就一定在connection連線層上。解決方法是在傳送查詢前執行一下下面這句（直接寫在SQL檔案的最前面）：
SET NAMES 'utf8';

它相當於下面的三句指令：

SET character_set_client = utf8;
SET character_set_results = utf8;
SET character_set_connection = utf8;

執行show variables like 'character%' 跟下面一樣說明修改成功

mysql> show variables like 'character%';
+--------------------------+----------------------------+
| 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)

　　

 

相關文章