要想導入時避免不出現亂碼,只要保證數據文件的編碼和導入時甚至的編碼一直即可,注意:不是和數據庫的編碼一致。例如:數據文件data.sql,編碼為UTF-8,使用如下命令:
代碼如下 復制代碼mysql -uroot -p --default-character-set=utf8 db < data.sql
上面的辦法不一定有效果,因為不知道mysql編碼,我們可以先查看mysql編碼
代碼如下 復制代碼mysql> show variables like "%char%";
+--------------------------+---------------------------------------------------------------------------------+
| Variable_name | Value |
+--------------------------+---------------------------------------------------------------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/Percona-Server-5.1.57-rel12.8-233--x86_64/share//charsets/ |
+--------------------------+---------------------------------------------------------------------------------+
8 rows in set (0.01 sec)
mysql> show variables like "%coll%";
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database | utf8_bin |
| collation_server | utf8_bin |
+----------------------+-------------------+
修改sql文件
代碼如下 復制代碼SET collation_connection = utf8_bin;
SET character_set_client = utf8;
SET character_set_connection = utf8;
再執行導入
代碼如下 復制代碼mysql --socket=/opt/mydata/my3306/my3306.sock -A < xxx.sql 就好了...
還發現一個程序導入亂碼解決辦法,就是在mysql_connect後面加一句SET NAMES UTF8,即可使得UTF8的數據庫消除亂碼,對於GBK的數據庫則使用SET NAMES GBK,代碼如下:
代碼如下 復制代碼 $mysql_mylink = mysql_connect($mysql_host, $mysql_user, $mysql_pass);
數據庫字符集為utf-8
連接語句用這個
代碼如下 復制代碼 mysql_query("SET NAMES 'UTF8'");