解決MySQL會出現中文亂碼問題的方法-mysql教程-PHP中文網

首頁

資料庫

mysql教程

解決MySQL會出現中文亂碼問題的方法

coldplay.xixi

Jul 06, 2020 pm 04:20 PM

mysql中文亂碼

1、MySQL會出現中文亂碼的原因
當我們在使用MySQL資料庫的時候，常常會碰到亂碼的問題，看下面的程式碼。

    mysql> create table test(id int,name varchar(10));
Query OK, 0 rows affected (0.01 sec)

    mysql> insert into test values(1,&#39;宋蔚然&#39;);
    ERROR 1366 (HY000): Incorrect string value: &#39;\xE5\xAE\x8B\xE8\x94\x9A...&#39; for column &#39;name&#39; at row 1
    mysql>

相關學習推薦：mysql影片教學

#很顯然，當插入中文的時候報錯，原因在哪呢？

    mysql> show variables like &#39;%CHARACTER%&#39;;
    +--------------------------+----------------------------+
    | 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/ |
    +--------------------------+----------------------------+

character_set_client 用戶端使用的字元編碼
character_set_connection 資料庫連結使用的編碼
character_set_database 資料庫使用的字元編碼
原來是字元編碼與服務端、資料庫的編碼不統一而造成的。

2、MySQL會出現中文亂碼的解決方法
#方法一：設定names

    mysql> set names latin1;
    
    mysql> set names latin1;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select * from test;
    Empty set (0.00 sec)
    
    mysql> insert into test values(1,&#39;宋蔚然&#39;);
    Query OK, 1 row affected (0.01 sec)
    
    mysql> select * from test;
    +------+-----------+
    | id   | name      |
    +------+-----------+
    |    1 | 宋蔚然    |
    +------+-----------+
    1 row in set (0.00 sec)

再來看一下字元集的設定

     mysql> show variables like &#39;%CHARACTER%&#39;;
    +--------------------------+----------------------------+
    | Variable_name            | Value                      |
    +--------------------------+----------------------------+
    | character_set_client     | latin1                     |
    | character_set_connection | latin1                     |
    | character_set_database   | latin1                     |
    | character_set_filesystem | binary                     |
    | character_set_results    | latin1                     |
    | character_set_server     | latin1                     |
    | character_set_system     | utf8                       |
    | character_sets_dir       | /usr/share/mysql/charsets/ |
    +--------------------------+----------------------------+

字元編碼統一就不會出現亂碼。
解決亂碼問題其實就是將客戶端的字元編碼與服務端、資料庫的編碼統一。這裡的服務端與資料庫編碼都是latin1，所有set names latin1即可暫時解決亂碼問題。

方法二：修改資料庫設定檔字元集為UTF8
#UTF8支援許多語言體系，因此在生產中強烈建議將字元編碼設定UTF8。開啟資料庫的設定文件，分別在[client]，[mysql]，[mysqld]下新增如下內容。

    #vi /mysql/data/3306/my.cnf
    
    [client]
    default-character-set=utf8
    
    [mysql]
    default-character-set=utf8
    
    [mysqld]
    default-storage-engine=INNODB
    character-set-server=utf8
    collation-server=utf8_general_ci

重啟資料庫

   [root@test ~]# systemctl restart mysqld

重寫建立函式庫與表格

    mysql> create database test;
    Query OK, 1 row affected (0.00 sec)
    
    mysql> use test;
    Database changed
    mysql> create table test(id int,name varchar(10));
    Query OK, 0 rows affected (0.02 sec)
    
    mysql> insert into test values(1,&#39;宋蔚然&#39;);
    Query OK, 1 row affected (0.00 sec)
    
    mysql> select * from test;
    +------+-----------+
    | id   | name      |
    +------+-----------+
    |    1 | 宋蔚然    |
    +------+-----------+
    1 row in set (0.00 sec)

再看一下字元集的設定

    mysql> show variables like &#39;%CHARACTER%&#39;;
    +--------------------------+----------------------------+
    | 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/ |
    +--------------------------+----------------------------+

當我們在使用MySQL資料庫的時候，常常會碰到亂碼的問題，看下面的程式碼。

    mysql> create table test(id int,name varchar(10));
Query OK, 0 rows affected (0.01 sec)

    mysql> insert into test values(1,&#39;宋蔚然&#39;);
    ERROR 1366 (HY000): Incorrect string value: &#39;\xE5\xAE\x8B\xE8\x94\x9A...&#39; for column &#39;name&#39; at row 1
    mysql>

很顯然，當插入中文的時候報錯，原因在哪呢？

    mysql> show variables like &#39;%CHARACTER%&#39;;
    +--------------------------+----------------------------+
    | 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/ |
    +--------------------------+----------------------------+

character_set_client 用戶端使用的字元編碼

character_set_connection 資料庫連結使用的編碼

character_set_database 資料庫使用的字元編碼

原來是字元編碼與服務端、資料庫的編碼不統一而造成的。

2、MySQL會出現中文亂碼的解決方法

#方法一：設定names

    mysql> set names latin1;
    
    mysql> set names latin1;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select * from test;
    Empty set (0.00 sec)
    
    mysql> insert into test values(1,&#39;宋蔚然&#39;);
    Query OK, 1 row affected (0.01 sec)
    
    mysql> select * from test;
    +------+-----------+
    | id   | name      |
    +------+-----------+
    |    1 | 宋蔚然    |
    +------+-----------+
    1 row in set (0.00 sec)

再來看一下字元集的設定

     mysql> show variables like &#39;%CHARACTER%&#39;;
    +--------------------------+----------------------------+
    | Variable_name            | Value                      |
    +--------------------------+----------------------------+
    | character_set_client     | latin1                     |
    | character_set_connection | latin1                     |
    | character_set_database   | latin1                     |
    | character_set_filesystem | binary                     |
    | character_set_results    | latin1                     |
    | character_set_server     | latin1                     |
    | character_set_system     | utf8                       |
    | character_sets_dir       | /usr/share/mysql/charsets/ |
    +--------------------------+----------------------------+

字元編碼統一就不會出現亂碼。

解決亂碼問題其實就是將客戶端的字元編碼與服務端、資料庫的編碼統一。這裡的服務端與資料庫編碼都是latin1，所有set names latin1即可暫時解決亂碼問題。

方法二：修改資料庫設定檔字元集為UTF8

#UTF8支援許多語言體系，因此在生產中強烈建議將字元編碼設定UTF8。開啟資料庫的設定文件，分別在[client]，[mysql]，[mysqld]下新增如下內容。

    #vi /mysql/data/3306/my.cnf
    
    [client]
    default-character-set=utf8
    
    [mysql]
    default-character-set=utf8
    
    [mysqld]
    default-storage-engine=INNODB
    character-set-server=utf8
    collation-server=utf8_general_ci

重啟資料庫

   [root@test ~]# systemctl restart mysqld

重寫建立庫和表格

    mysql> create database test;
    Query OK, 1 row affected (0.00 sec)
    
    mysql> use test;
    Database changed
    mysql> create table test(id int,name varchar(10));
    Query OK, 0 rows affected (0.02 sec)
    
    mysql> insert into test values(1,&#39;宋蔚然&#39;);
    Query OK, 1 row affected (0.00 sec)
    
    mysql> select * from test;
    +------+-----------+
    | id   | name      |
    +------+-----------+
    |    1 | 宋蔚然    |
    +------+-----------+
    1 row in set (0.00 sec)

再來看字元集的設定

    mysql> show variables like &#39;%CHARACTER%&#39;;
    +--------------------------+----------------------------+
    | 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/ |
    +--------------------------+----------------------------+

以上是解決MySQL會出現中文亂碼問題的方法的詳細內容。更多資訊請關注PHP中文網其他相關文章！

陳述

本文轉載於：CSDN。如有侵權，請聯絡admin@php.cn刪除

在MySQL中使用視圖的局限性是什麼？May 14, 2025 am 12:10 AM

mysqlviewshavelimitations：1）他們不使用Supportallsqloperations，限制DatamanipulationThroughViewSwithJoinsOrsubqueries.2）他們canimpactperformance，尤其是withcomplexcomplexclexeriesorlargedatasets.3）

確保您的MySQL數據庫：添加用戶並授予特權May 14, 2025 am 12:09 AM

porthusermanagementinmysqliscialforenhancingsEcurityAndsingsmenting效率databaseoperation.1）usecReateusertoAddusers，指定connectionsourcewith@'localhost'or@'％'。

哪些因素會影響我可以在MySQL中使用的觸發器數量？May 14, 2025 am 12:08 AM

mysqldoes notimposeahardlimitontriggers，butacticalfactorsdeterminetheireffactective：1）serverConfiguration impactactStriggerGermanagement; 2）複雜的TriggerSincreaseSySystemsystem load; 3）largertablesslowtriggerperfermance; 4）highConconcConcrencerCancancancancanceTigrignecentign; 5）; 5）

mysql：存儲斑點安全嗎？May 14, 2025 am 12:07 AM

Yes,it'ssafetostoreBLOBdatainMySQL,butconsiderthesefactors:1)StorageSpace:BLOBscanconsumesignificantspace,potentiallyincreasingcostsandslowingperformance.2)Performance:LargerrowsizesduetoBLOBsmayslowdownqueries.3)BackupandRecovery:Theseprocessescanbe

mySQL：通過PHP Web界面添加用戶May 14, 2025 am 12:04 AM

通過PHP網頁界面添加MySQL用戶可以使用MySQLi擴展。步驟如下：1.連接MySQL數據庫，使用MySQLi擴展。 2.創建用戶，使用CREATEUSER語句，並使用PASSWORD()函數加密密碼。 3.防止SQL注入，使用mysqli_real_escape_string()函數處理用戶輸入。 4.為新用戶分配權限，使用GRANT語句。

mysql：blob和其他無-SQL存儲，有什麼區別？May 13, 2025 am 12:14 AM

mysql'sblobissuitableForStoringBinaryDataWithInareLationalDatabase，而ilenosqloptionslikemongodb，redis和calablesolutionsolutionsolutionsoluntionsoluntionsolundortionsolunsonstructureddata.blobobobissimplobisslowdeperformberbutslowderformandperformancewithlararengedata;

mySQL添加用戶：語法，選項和安全性最佳實踐May 13, 2025 am 12:12 AM

toaddauserinmysql，使用：createUser'username'@'host'Indessify'password'; there'showtodoitsecurely：1）choosethehostcarecarefullytocon trolaccess.2）setResourcelimitswithoptionslikemax_queries_per_hour.3）usestrong，iniquepasswords.4）Enforcessl/tlsconnectionswith

MySQL：如何避免字符串數據類型常見錯誤？May 13, 2025 am 12:09 AM

toAvoidCommonMistakeswithStringDatatatPesInMysQl，CloseStringTypenuances，chosethirtightType，andManageEngencodingAndCollationsEttingSefectery.1）usecharforfixed lengengtrings，varchar forvariable-varchar forbariaible length，andtext/blobforlargerdataa.2 seterters seterters seterters

See all articles