mysql设置字符集utf8mb4

阳光穿透心脏的1/2处 2021-06-24 14:35 617阅读 0赞

**为什么要把数据库的字符集设置成utf8mb4呢？以前一直用的都是utf8啊？**  
答案在这里：utf8适用于不使用移动设备的互联网交互，utf8mb4适用于当前的移动设备互联网开发，因为移动设备中常常会有表情符号（emoji）的存储，它占用4个字节的存储空间，而utf8是3个字节，这样，用3个字节去存储4个字节的东西，很明显是存不下的，会报错，所以要用utf8mb4，并且utf8mb4是兼容utf8的，那么，就没有理由不用utf8mb4字符集了。

介绍设置mysql的字符集：  
**1 在ubuntu安装mysql （密码自己设置）**

sudo apt-get install mysql-server
    sudo apt-get install mysql-client
    sudo apt-get install libmysqlclient-dev

**2 进入mysql，查看信息**

zhangl@zhangl:~$ mysql -uroot -p
    Enter password: 
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 5
    Server version: 5.7.12-0ubuntu1 (Ubuntu)
    
    Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    mysql> 
    mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
    +--------------------------+-------------------+
    | 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 |
    | collation_connection | utf8_general_ci |
    | collation_database | latin1_swedish_ci |
    | collation_server | latin1_swedish_ci |
    +--------------------------+-------------------+
    10 rows in set (0.02 sec)

可以看到我的mysql版本是5.7的，utf8mb4有一个使用限制，mysql版本必须是5.5以上，大家需要注意，我目前用的ubuntu系统是16.04的。当前mysql的字符集配置如上表，我们的目的是更改成utf8mb4。

**3 找到mysql的配置文件，可用命令**

sudo find / -name my.cnf

我的配置文件位置如下，个别的位置有所不同  
/etc/mysql/mysql.conf.d/mysqld.cnf

**4 修改配置文件**  
在原文中添加以下内容：

[client]
    default-character-set = utf8mb4 [mysql] default-character-set = utf8mb4 [mysqld] character-set-client-handshake = FALSE character-set-server = utf8mb4 collation-server = utf8mb4_unicode_ci init_connect='SET NAMES utf8mb4'

原文件中无“\[client\]”和“\[mysql\]”，需要手动添加上。

......
    # Here is entries for some specific programs
    # The following values assume you have at least 32M ram
    
    [client]
    default-character-set = utf8mb4
    
    [mysql]
    default-character-set = utf8mb4
    
    [mysqld_safe]
    socket      = /var/run/mysqld/mysqld.sock
    nice        = 0
    
    [mysqld]
    #
    # * Basic Settings
    #
    user        = mysql
    pid-file    = /var/run/mysqld/mysqld.pid
    socket      = /var/run/mysqld/mysqld.sock
    port        = 3306
    basedir     = /usr
    datadir     = /var/lib/mysql
    tmpdir      = /tmp
    lc-messages-dir = /usr/share/mysql
    skip-external-locking
    
    character-set-client-handshake = FALSE
    character-set-server = utf8mb4
    collation-server = utf8mb4_unicode_ci
    init_connect='SET NAMES utf8mb4'
    #
    # Instead of skip-networking the default is now to listen only on
    ......

**5 重启mysql服务**

sudo service mysql restart

**6 查看结果**

ok了，这就是我想要的结果，现象可以去连项目测试了。