1、mysql 插入中文數(shù)據(jù)亂碼解決方案MySQL 4.1的字符集支持(Character Set Support)有兩個方面：字符集(Character set)和排序方式(Collation)。對于字符集的支持細化到四個層次: 服務(wù)器(server)，數(shù)據(jù)庫(database)，數(shù)據(jù)表(table)和連接(connection)。下面將分兩部分，分別設(shè)置服務(wù)器編碼和數(shù)據(jù)庫、數(shù)據(jù)表與連接部分的編碼，從而杜絕中文亂碼的出現(xiàn)。 一、服務(wù)器編碼設(shè)置 服務(wù)器編碼設(shè)置方法有二：一是安裝mysql時，其中會有一個步驟選擇編碼方式，此時選擇utf8即可。如果不選擇，默認的編碼是latin1；二是在安裝玩mys

2、ql之后，手動修改其配置文件，如下：(1)修改 MySql安裝目錄下面的my.ini(MySQL Server Instance Configuration 文件)， (需要關(guān)閉mysql服務(wù))，(如果本身沒有該文件，自己添加一個亦可)my.ini文件進行如下修改： =修改my.ini前顯示結(jié)果=mysql> show variables like 'character%'+-+-+| Variable_name| Value|+-+-+| character_set_client | gbk| character_set_connection| gbk| charact

3、er_set_database| latin1| character_set_| binary| character_set_results | gbk | character_set_server| latin1 | character_set_system | utf8 | character_sets_dir | D:mysql-5.5.10sharecharsets |+-+-+8 rows in set (0.02 sec)mysql> show variables like 'collation%'+-+-+| Variable_name| Value |+-

4、+-+| collation_connection| gbk_chinese_ci | collation_database | latin1_swedish_ci | collation_server | latin1_swedish_ci |+-+-+3 rows in set (0.00 sec) =關(guān)閉mysql服務(wù)，在mysql安裝目錄下，my.ini文件進行如下修改，沒有則直接進行添加= mysqld #修改服務(wù)器端默認字符編碼格式為utf8 character-set-server=utf8 client #修改客戶端默認字符編碼格式為utf8 default-character

5、-set=utf8=修改后，再次輸入命令查看，顯示結(jié)果如下=mysql> show variables like 'character%'+-+-+| Variable_name| Value|+-+-+| character_set_client | utf8| character_set_connection| utf8| character_set_database| utf8| character_set_| binary| character_set_results | utf8| character_set_server| utf8| character_se

6、t_system | utf8 | character_sets_dir | D:mysql-5.5.10sharecharsets |+-+-+8 rows in set (0.02 sec)mysql> show variables like 'collation%'+-+-+| Variable_name| Value |+-+-+| collation_connection| utf8_general_ci | collation_database | utf8_general_ci | collation_server | utf8_general_ci |+-

7、+-+3 rows in set (0.00 sec)=(2) （對于已經(jīng)創(chuàng)建了的數(shù)據(jù)庫）修改data目錄中相應(yīng)數(shù)據(jù)庫目錄下的db.opt配置文件 default-character-set=utf8 default-collation=utf8_general_ci重啟數(shù)據(jù)庫，關(guān)閉控制臺窗口重新登錄數(shù)據(jù)庫即可。通過MySQL命令行修改:set character_set_client=utf8;set character_set_connection=utf8;set character_set_database=utf8;set character_set_results=utf8;set

8、 character_set_server=utf8;set character_set_system=utf8;set collation_connection=utf8_general_ci;set collation_database=utf8_general_ci;set collation_server=utf8_general_ci; 二、數(shù)據(jù)庫、數(shù)據(jù)表和連接部分的編碼設(shè)置 2.1 設(shè)置數(shù)據(jù)庫和數(shù)據(jù)表編碼（默認的latin1）修改用戶密碼D:mysql-advanced-5.6.11-win32bin>mysqladmin -u root -p password（回車）Ent

9、er password:（回車，用戶root的默認密碼為空）New password: *（回車，新密碼111111）Confirm new password: *（回車）連接與斷開服務(wù)器D:mysql-advanced-5.6.11-win32bin>mysqld（啟動mysql服務(wù)程序,用來執(zhí)行客戶程序提交的任務(wù)）D:mysql-advanced-5.6.11-win32bin>mysql -uroot -p（回車）Enter password:（輸入密碼，用戶root的默認密碼為空）mysql> quit;（回車）Bye創(chuàng)建數(shù)據(jù)庫mysql> create dat

10、abase mydatabase CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'Query OK, 1 row affected (0.00 sec)(注：后面一段是顯式的設(shè)置數(shù)據(jù)庫的字符集，如果已經(jīng)設(shè)置了全局的字符集，則可以省略)顯示數(shù)據(jù)庫列表mysql> show databases;+-+| Database |+-+| information_schema| mydatabase | mysql | performance_schema| test |+-+5 rows in set (0.05 sec

11、)修改數(shù)據(jù)庫字符集：mysql> show create database test;+-+-+| Database | Create Database |+-+-+| test | CREATE DATABASE test /*!40100 DEFAULT CHARACTER SET latin1 */ |+-+-+1 row in set (0.00 sec)mysql> alter database test default character set utf8 collate utf8_general_ci;Query OK, 1 row affected (0.04 se

12、c)mysql> show create database test;+-+-+| Database | Create Database |+-+-+| test | CREATE DATABASE test /*!40100 DEFAULT CHARACTER SET utf8 */ |+-+-+1 row in set (0.00 sec)使用某個數(shù)據(jù)庫mysql> use eosdb;Database changed顯示數(shù)據(jù)表列表mysql> show tables;Empty set (0.00 sec)創(chuàng)建數(shù)據(jù)表mysql> CREATE TABLE mysq

13、lcode (-> id TINYINT( 255 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,-> content VARCHAR( 255 ) NOT NULL-> ) TYPE = MYISAM CHARACTER SET gbk COLLATE gbk_chinese_ci;Query OK, 0 rows affected, 1 warning (0.03 sec)插入數(shù)據(jù)mysql> insert into mysqlcode values(null,'java愛好者');按回車，結(jié)果如下：

14、ERROR 1406 (22001): Data too long for column 'content' at row 1解決方案：設(shè)置連接編碼，設(shè)置如下：mysql> SET character_set_client='gbk'mysql> SET character_set_connection='gbk'mysql> SET character_set_results='gbk'設(shè)置好連接編碼，下面便可以成功插入中文了：mysql> insert into mysqlcode values(null,'java愛好者');Query OK, 0 rows affected (0.02 sec)其實，上面設(shè)置連接編碼的三條命令可以簡化為一條