解决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/ |
    +--------------------------+----------------------------+

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> 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/ |
    +--------------------------+----------------------------+

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/ |
    +--------------------------+----------------------------+

    #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，而alenosqloptionslikemongodb，redis和calablesolutionsoluntionsoluntionsoluntionsolundortionsolunsolunsstructureddata.blobobobsimplobissimplobisslowderperformandperformanceperformancewithlararengelitiate;

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 lengengters lengengtings，varchar forbariaible lengength，varchariable length，andtext/blobforlabforlargerdata.2 seterters seterters seterters seterters

See all articles