一文搞定Mysql 乱码问题

系统管理员 2023-05-30 09:06 56阅读 0赞

# **处理****mysql** **编码问题** #

** **

我们经常会遇到一些向MySQL数据库中插入中文，但是select出来的时候，却是乱码。

** **

## **一****、查看库、表字符集命令** ##

**1.****查看数据库支持的所有字符集**

Show  character set (show char set)

[![wKioL1m9zkHDt9iJAAB9UnFgQSM364.png-wh\_50][wKioL1m9zkHDt9iJAAB9UnFgQSM364.png-wh_50]][wKioL1m9zkHDt9iJAAB9UnFgQSM364.png-wh_50_wKioL1m9zkHDt9iJAAB9UnFgQSM364.png-wh_50]

** **

**2.****查看当前状态，里面当然包括字符集的设置**

**Status** **（****/s****）**

[![wKiom1m9znzyP5sBAABboazaVnM666.png-wh\_50][wKiom1m9znzyP5sBAABboazaVnM666.png-wh_50]][wKiom1m9znzyP5sBAABboazaVnM666.png-wh_50_wKiom1m9znzyP5sBAABboazaVnM666.png-wh_50]

** **

**3.****查看系统字符集设置，包括所有的字符集设置**

Show  variables like  ‘%char%’;

[![wKioL1m9zmGAMvXNAAA5k3EFZkA197.png-wh\_50][wKioL1m9zmGAMvXNAAA5k3EFZkA197.png-wh_50]][wKioL1m9zmGAMvXNAAA5k3EFZkA197.png-wh_50_wKioL1m9zmGAMvXNAAA5k3EFZkA197.png-wh_50]

[![wKiom1m9zpCA64ygAALCcrYV-cg330.png-wh\_50][wKiom1m9zpCA64ygAALCcrYV-cg330.png-wh_50]][wKiom1m9zpCA64ygAALCcrYV-cg330.png-wh_50_wKiom1m9zpCA64ygAALCcrYV-cg330.png-wh_50]

**4.****查看数据表中字符集设置**

**Show  full columns  from  tablename ;**

[![wKiom1m9057Q7vAoAADOk-a3ZLs531.jpg-wh\_50][wKiom1m9057Q7vAoAADOk-a3ZLs531.jpg-wh_50]][wKiom1m9057Q7vAoAADOk-a3ZLs531.jpg-wh_50_wKiom1m9057Q7vAoAADOk-a3ZLs531.jpg-wh_50]

**也可以查看表结构**

**Show  create table  tablename\\G;**

[![wKiom1m9zreSb5v5AABvkrs91eM479.png-wh\_50][wKiom1m9zreSb5v5AABvkrs91eM479.png-wh_50]][wKiom1m9zreSb5v5AABvkrs91eM479.png-wh_50_wKiom1m9zreSb5v5AABvkrs91eM479.png-wh_50]

**5.****查看数据库编码（数据库结构）**

**Show  create database  dbname;//****创建数据库指定的字符集**

[![wKioL1m9zpXy3CvyAAAbwJNOKuM224.png-wh\_50][wKioL1m9zpXy3CvyAAAbwJNOKuM224.png-wh_50]][wKioL1m9zpXy3CvyAAAbwJNOKuM224.png-wh_50_wKioL1m9zpXy3CvyAAAbwJNOKuM224.png-wh_50]

** **

## **二、****创建库表列时要指定字符集（要统一）** ##

**1****、****服务器级**

在安装MySQL时可以设置服务器的默认编码格式，也可对my.ini做修改，修改\[mysqld\]里面的character\_set\_server=utf8，则可设置character\_set\_server的值。

**2.****数据库级**

**Create  database   dbname default  character  set utf8;**

[![wKiom1m9zs\_QZYg-AAA6dvRUG3o428.png-wh\_50][wKiom1m9zs_QZYg-AAA6dvRUG3o428.png-wh_50]][wKiom1m9zs_QZYg-AAA6dvRUG3o428.png-wh_50_wKiom1m9zs_QZYg-AAA6dvRUG3o428.png-wh_50]

**注意**，如果不指定默认的字符集，则系统会根据character\_set\_database的值进行设置

**3.****表级**

**Create  table dbname.tbname(id varchar(20) not null, name vharchar(20))engine=innoDB  default  charset=utf8;**

[![wKioL1m91H2S-jqJAADJFLl2rCA216.jpg-wh\_50][wKioL1m91H2S-jqJAADJFLl2rCA216.jpg-wh_50]][wKioL1m91H2S-jqJAADJFLl2rCA216.jpg-wh_50_wKioL1m91H2S-jqJAADJFLl2rCA216.jpg-wh_50]

**注**：定义表的默认字符集为utf8，即使character\_set\_database为gbk，但是表的列都为utf8。如果没有定义表的默认字符集，则他会按照character\_set\_database的值来设置

** **

**4.****列级**

**Create  table db1.tb2(id  varchar(20)  not null, name  varchar(20)  character set utf8);**

[![wKiom1m9zt7DdDvSAABoblrR9gk273.png-wh\_50][wKiom1m9zt7DdDvSAABoblrR9gk273.png-wh_50]][wKiom1m9zt7DdDvSAABoblrR9gk273.png-wh_50_wKiom1m9zt7DdDvSAABoblrR9gk273.png-wh_50]

**注：如查没有指定列字符集、就使用表字符集、如查指定了就使用指定的。**

**图中：****show  create table db1.tb2//****是查看表结构**

** **

## **三、修改字符集** ##

**1.****修改****character\_set\_connection****、****character\_set\_client****、****character\_set\_results****三值**

**对于一个连接来说、可以用**

**Set names  utf8****来使用上面三个值改成****utf8;**

[![wKioL1m9zrejI8kVAABCfVtrvX8561.png-wh\_50][wKioL1m9zrejI8kVAABCfVtrvX8561.png-wh_50]][wKioL1m9zrejI8kVAABCfVtrvX8561.png-wh_50_wKioL1m9zrejI8kVAABCfVtrvX8561.png-wh_50]

**Set name  ‘charset\_name’;** **相当于**

**Set character\_set\_client = charset\_name;**

SET character\_set\_results = charset\_name;

SET character\_set\_connection = charset\_name;

** **

**2.****修改****character\_set\_database****字段****(****也就是改数据库字符集****)**

**Alter  database db\_name  default  character set  charset\_name;**

** **

[![wKiom1m9zu3irdigAABG1PbfAmY829.png-wh\_50][wKiom1m9zu3irdigAABG1PbfAmY829.png-wh_50]][wKiom1m9zu3irdigAABG1PbfAmY829.png-wh_50_wKiom1m9zu3irdigAABG1PbfAmY829.png-wh_50]

** **

**3.****修改****character\_set\_server****字段**

最简单的方法是直接改my.ini配置文件里面\[mysqld\]的字段，增加character-set-server=gbk，然后重启mysqld，则可改为你想要的字符集。

** **

**4.****修改表的字符集**

**Alter  table tbname  default  character set  charsetname;**

**Alter  table tb1  default  character set  utf8**

** **

**5.****修改列的字符集**

**Alter  table tbname  modify  age varchar(30)  character  set utf8;**

** **

**总结：**

**1、****建库、建表、建字段****设置统一编码。**

**2、****PHP****作为****mysqle****服务器客户端、连接编码****set names utf8/gbk;**

**3、****设置****php****返回给浏览器数据的编码。（****Content-Type,header(),<meta>****）**

header("Content-type:text/html;charset=utf-8");

**4、****Php****文件本身保存的编码（文件编码、通过文本编辑器设置）**

**5、****安装****mysql****时设置好编码。（装好后可修改****my.ini****）**

### **公众号  关注一波  （一叶知秋博客）** ###

### **         不定期分享视频资料** ###

**![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Nzd3F6eA_size_16_color_FFFFFF_t_70][]**

[wKioL1m9zkHDt9iJAAB9UnFgQSM364.png-wh_50]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9zMi41MWN0by5jb20vd3lmczAyL00wMi9BNS83MC93S2lvTDFtOXprSER0OWlKQUFCOVVuRmdRU00zNjQucG5nLXdoXzUwMHgwLXdtXzMtd21wXzQtc18zOTY2OTgwNzMwLnBuZw?x-oss-process=image/format,png
[wKioL1m9zkHDt9iJAAB9UnFgQSM364.png-wh_50_wKioL1m9zkHDt9iJAAB9UnFgQSM364.png-wh_50]: https://s2.51cto.com/wyfs02/M02/A5/70/wKioL1m9zkHDt9iJAAB9UnFgQSM364.png-wh_500x0-wm_3-wmp_4-s_3966980730.png
[wKiom1m9znzyP5sBAABboazaVnM666.png-wh_50]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9zMy41MWN0by5jb20vd3lmczAyL00wMi8wNi9DMC93S2lvbTFtOXpuenlQNXNCQUFCYm9hemFWbk02NjYucG5nLXdoXzUwMHgwLXdtXzMtd21wXzQtc18zNTE0NDY0MzMyLnBuZw?x-oss-process=image/format,png
[wKiom1m9znzyP5sBAABboazaVnM666.png-wh_50_wKiom1m9znzyP5sBAABboazaVnM666.png-wh_50]: https://s3.51cto.com/wyfs02/M02/06/C0/wKiom1m9znzyP5sBAABboazaVnM666.png-wh_500x0-wm_3-wmp_4-s_3514464332.png
[wKioL1m9zmGAMvXNAAA5k3EFZkA197.png-wh_50]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9zNS41MWN0by5jb20vd3lmczAyL00wMS9BNS83MC93S2lvTDFtOXptR0FNdlhOQUFBNWszRUZaa0ExOTcucG5nLXdoXzUwMHgwLXdtXzMtd21wXzQtc18xMjgyOTQ0NzcyLnBuZw?x-oss-process=image/format,png
[wKioL1m9zmGAMvXNAAA5k3EFZkA197.png-wh_50_wKioL1m9zmGAMvXNAAA5k3EFZkA197.png-wh_50]: https://s5.51cto.com/wyfs02/M01/A5/70/wKioL1m9zmGAMvXNAAA5k3EFZkA197.png-wh_500x0-wm_3-wmp_4-s_1282944772.png
[wKiom1m9zpCA64ygAALCcrYV-cg330.png-wh_50]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9zNS41MWN0by5jb20vd3lmczAyL00wMC8wNi9DMC93S2lvbTFtOXpwQ0E2NHlnQUFMQ2NyWVYtY2czMzAucG5nLXdoXzUwMHgwLXdtXzMtd21wXzQtc18yMjU1NTgxNjg2LnBuZw?x-oss-process=image/format,png
[wKiom1m9zpCA64ygAALCcrYV-cg330.png-wh_50_wKiom1m9zpCA64ygAALCcrYV-cg330.png-wh_50]: https://s5.51cto.com/wyfs02/M00/06/C0/wKiom1m9zpCA64ygAALCcrYV-cg330.png-wh_500x0-wm_3-wmp_4-s_2255581686.png
[wKiom1m9057Q7vAoAADOk-a3ZLs531.jpg-wh_50]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9zNS41MWN0by5jb20vd3lmczAyL00wMC8wNi9DMC93S2lvbTFtOTA1N1E3dkFvQUFET2stYTNaTHM1MzEuanBnLXdoXzUwMHgwLXdtXzMtd21wXzQtc18xNTk4MTkyNzQ1LmpwZw?x-oss-process=image/format,png
[wKiom1m9057Q7vAoAADOk-a3ZLs531.jpg-wh_50_wKiom1m9057Q7vAoAADOk-a3ZLs531.jpg-wh_50]: https://s5.51cto.com/wyfs02/M00/06/C0/wKiom1m9057Q7vAoAADOk-a3ZLs531.jpg-wh_500x0-wm_3-wmp_4-s_1598192745.jpg
[wKiom1m9zreSb5v5AABvkrs91eM479.png-wh_50]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9zNS41MWN0by5jb20vd3lmczAyL00wMS8wNi9DMC93S2lvbTFtOXpyZVNiNXY1QUFCdmtyczkxZU00NzkucG5nLXdoXzUwMHgwLXdtXzMtd21wXzQtc183NTkzOTQzMTYucG5n?x-oss-process=image/format,png
[wKiom1m9zreSb5v5AABvkrs91eM479.png-wh_50_wKiom1m9zreSb5v5AABvkrs91eM479.png-wh_50]: https://s5.51cto.com/wyfs02/M01/06/C0/wKiom1m9zreSb5v5AABvkrs91eM479.png-wh_500x0-wm_3-wmp_4-s_759394316.png
[wKioL1m9zpXy3CvyAAAbwJNOKuM224.png-wh_50]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9zMS41MWN0by5jb20vd3lmczAyL00wMC9BNS83MC93S2lvTDFtOXpwWHkzQ3Z5QUFBYndKTk9LdU0yMjQucG5nLXdoXzUwMHgwLXdtXzMtd21wXzQtc181MDU4NTc3MzkucG5n?x-oss-process=image/format,png
[wKioL1m9zpXy3CvyAAAbwJNOKuM224.png-wh_50_wKioL1m9zpXy3CvyAAAbwJNOKuM224.png-wh_50]: https://s1.51cto.com/wyfs02/M00/A5/70/wKioL1m9zpXy3CvyAAAbwJNOKuM224.png-wh_500x0-wm_3-wmp_4-s_505857739.png
[wKiom1m9zs_QZYg-AAA6dvRUG3o428.png-wh_50]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9zMS41MWN0by5jb20vd3lmczAyL00wMS8wNi9DMC93S2lvbTFtOXpzX1FaWWctQUFBNmR2UlVHM280MjgucG5nLXdoXzUwMHgwLXdtXzMtd21wXzQtc18xMDQzMTc2NzcucG5n?x-oss-process=image/format,png
[wKiom1m9zs_QZYg-AAA6dvRUG3o428.png-wh_50_wKiom1m9zs_QZYg-AAA6dvRUG3o428.png-wh_50]: https://s1.51cto.com/wyfs02/M01/06/C0/wKiom1m9zs_QZYg-AAA6dvRUG3o428.png-wh_500x0-wm_3-wmp_4-s_104317677.png
[wKioL1m91H2S-jqJAADJFLl2rCA216.jpg-wh_50]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9zNC41MWN0by5jb20vd3lmczAyL00wMS9BNS83MS93S2lvTDFtOTFIMlMtanFKQUFESkZMbDJyQ0EyMTYuanBnLXdoXzUwMHgwLXdtXzMtd21wXzQtc18zMTAwNzUyNjg5LmpwZw?x-oss-process=image/format,png
[wKioL1m91H2S-jqJAADJFLl2rCA216.jpg-wh_50_wKioL1m91H2S-jqJAADJFLl2rCA216.jpg-wh_50]: https://s4.51cto.com/wyfs02/M01/A5/71/wKioL1m91H2S-jqJAADJFLl2rCA216.jpg-wh_500x0-wm_3-wmp_4-s_3100752689.jpg
[wKiom1m9zt7DdDvSAABoblrR9gk273.png-wh_50]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9zMS41MWN0by5jb20vd3lmczAyL00wMi8wNi9DMC93S2lvbTFtOXp0N0RkRHZTQUFCb2JsclI5Z2syNzMucG5nLXdoXzUwMHgwLXdtXzMtd21wXzQtc18zNTM3OTU2MzQ4LnBuZw?x-oss-process=image/format,png
[wKiom1m9zt7DdDvSAABoblrR9gk273.png-wh_50_wKiom1m9zt7DdDvSAABoblrR9gk273.png-wh_50]: https://s1.51cto.com/wyfs02/M02/06/C0/wKiom1m9zt7DdDvSAABoblrR9gk273.png-wh_500x0-wm_3-wmp_4-s_3537956348.png
[wKioL1m9zrejI8kVAABCfVtrvX8561.png-wh_50]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9zMy41MWN0by5jb20vd3lmczAyL00wMi9BNS83MC93S2lvTDFtOXpyZWpJOGtWQUFCQ2ZWdHJ2WDg1NjEucG5nLXdoXzUwMHgwLXdtXzMtd21wXzQtc18xNTg2MzczNjg4LnBuZw?x-oss-process=image/format,png
[wKioL1m9zrejI8kVAABCfVtrvX8561.png-wh_50_wKioL1m9zrejI8kVAABCfVtrvX8561.png-wh_50]: https://s3.51cto.com/wyfs02/M02/A5/70/wKioL1m9zrejI8kVAABCfVtrvX8561.png-wh_500x0-wm_3-wmp_4-s_1586373688.png
[wKiom1m9zu3irdigAABG1PbfAmY829.png-wh_50]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9zMi41MWN0by5jb20vd3lmczAyL00wMi8wNi9DMC93S2lvbTFtOXp1M2lyZGlnQUFCRzFQYmZBbVk4MjkucG5nLXdoXzUwMHgwLXdtXzMtd21wXzQtc182NTE3MjIyMTMucG5n?x-oss-process=image/format,png
[wKiom1m9zu3irdigAABG1PbfAmY829.png-wh_50_wKiom1m9zu3irdigAABG1PbfAmY829.png-wh_50]: https://s2.51cto.com/wyfs02/M02/06/C0/wKiom1m9zu3irdigAABG1PbfAmY829.png-wh_500x0-wm_3-wmp_4-s_651722213.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Nzd3F6eA_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20191108193508587.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Nzd3F6eA==,size_16,color_FFFFFF,t_70