MySQL设置各类字符集

客官°小女子只卖身不卖艺 2021-11-17 05:02 261阅读 0赞

一、查看字符集编码：

登录mysql

`show variables like ``'%character%'``;`

二、修改编码：

编辑/etc/my.cnf ，设置后的配置文件如下：

\[root@node03 /\]\# cat /etc/my.cnf  
\[mysql\]  
default-character-set=utf8  
\[mysqld\]  
datadir=/var/lib/mysql  
socket=/var/lib/mysql/mysql.sock  
user=mysql  
\# Disabling symbolic-links is recommended to prevent assorted security risks  
symbolic-links=0  
default-character-set=utf8  
character\_set\_server=utf8  
\#lower\_case\_table\_names=1 忽略大小写

\[mysqld\_safe\]  
log-error=/var/log/mysqld.log  
pid-file=/var/run/mysqld/mysqld.pid  
default-character-set=utf8  
\[mysql.server\]  
default-character-set=utf8  
\[client\]  
default-character-set=utf8

三、重启MySQL服务：

service mysql restart

四、如果上面的都修改了还乱码，那剩下问题就一定在connection连接层上。解决方法是在发送查询前执行一下下面这句（直接写在SQL文件的最前面）：

`SET NAMES 'utf8';`

它相当于下面的三句指令：`SET character_set_client = utf8; SET character_set_results = utf8; SET character_set_connection = utf8;`

执行show variables like 'character%' 跟下面一样说明修改成功

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div>
      1 
    </div> 
    <div>
      2 
    </div> 
    <div>
      3 
    </div> 
    <div>
      4 
    </div> 
    <div>
      5 
    </div> 
    <div>
      6 
    </div> 
    <div>
      7 
    </div> 
    <div>
      8 
    </div> 
    <div>
      9 
    </div> 
    <div>
      10 
    </div> 
    <div>
      11 
    </div> 
    <div>
      12 
    </div> 
    <div>
      13 
    </div> 
    <div>
      14 
    </div> </td> 
   <td> 
    <div> 
     <div> 
      <code>mysql&gt; show variables like&nbsp;</code> 
      <code>'character%'</code> 
      <code>;</code> 
     </div> 
     <div> 
      <code>+--------------------------+----------------------------+</code> 
     </div> 
     <div> 
      <code>| Variable_name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Value&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |</code> 
     </div> 
     <div> 
      <code>+--------------------------+----------------------------+</code> 
     </div> 
     <div> 
      <code>| character_set_client&nbsp;&nbsp;&nbsp;&nbsp; | utf8&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |</code> 
     </div> 
     <div> 
      <code>| character_set_connection | utf8&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |</code> 
     </div> 
     <div> 
      <code>| character_set_database&nbsp;&nbsp; | utf8&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |</code> 
     </div> 
     <div> 
      <code>| character_set_filesystem | binary&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |</code> 
     </div> 
     <div> 
      <code>| character_set_results&nbsp;&nbsp;&nbsp; | utf8&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |</code> 
     </div> 
     <div> 
      <code>| character_set_server&nbsp;&nbsp;&nbsp;&nbsp; | utf8&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |</code> 
     </div> 
     <div> 
      <code>| character_set_system&nbsp;&nbsp;&nbsp;&nbsp; | utf8&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |</code> 
     </div> 
     <div> 
      <code>| character_sets_dir&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | /usr/share/mysql/charsets/ |</code> 
     </div> 
     <div> 
      <code>+--------------------------+----------------------------+</code> 
     </div> 
     <div> 
      <code>8</code>&nbsp; 
      <code>rows in set (</code> 
      <code>0.00</code>&nbsp; 
      <code>sec)</code> 
     </div> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

转载于:https://www.cnblogs.com/mediocreWorld/p/11093191.html