MySQL字符集

1、各级别的字符集

• MySQL有4个级别的字符集和比较规则，分别是：

  • 服务器级别。
  • 数据库级别。
  • 表级别。
  • 列级别。

• 执行如下SQL语句：

  mysql> show variables like 'character%';
  +--------------------------+--------------------------------+
  | Variable_name            | Value                          |
  +--------------------------+--------------------------------+
  | character_set_client     | utf8mb4                        |
  | character_set_connection | utf8mb4                        |
  | character_set_database   | utf8mb4                        |
  | character_set_filesystem | binary                         |
  | character_set_results    | utf8mb4                        |
  | character_set_server     | utf8mb4                        |
  | character_set_system     | utf8mb3                        |
  | character_sets_dir       | /usr/share/mysql-8.0/charsets/ |
  +--------------------------+--------------------------------+
  8 rows in set (0.00 sec)

  • character_set_server：服务器级别的字符集。
  • character_set_database：当前数据库的字符集。
  • character_set_client：服务器解码请求时使用的字符集。
  • character_set_connection：服务器处理请求时会把请求字符串从character_set_client转为character_set_connection。
  • character_set_results：服务器向客户端返回数据时使用的字符集。

1.1 服务器级别

• character_set_server：服务器级别的字符集。

  • 我们可以在启动服务器程序时通过启动选项或者在服务器程序运行过程中使用SET语句修改这两个变量的值。比如我们可以在配置文件中这样写：

    [server]
    character_set_server=gbk # 默认字符集
    collation_server=gbk_chinese_ci # 对应的默认的比较规则

    当服务器启动的时候读取这个配置文件后这两个系统变量的值便修改了。

1.2 数据库级别

• character_set_server：服务器级别的字符集。

  • 我们在创建和修改数据库的时候可以指定该数据库的字符集和比较规则，具体语法如下：

    CREATE DATABASE 数据库名
        [[DEFAULT] CHARACTER SET 字符集名称]
        [[DEFAULT] COLLATE 比较规则名称];
        
    ALTER DATABASE 数据库名
        [[DEFAULT] CHARACTER SET 字符集名称]
        [[DEFAULT] COLLATE 比较规则名称];

1.3 表级别

• 我们也可以在创建和修改表的时候指定表的字符集和比较规则，语法如下：

  CREATE TABLE 表名 (列的信息)
      [[DEFAULT] CHARACTER SET 字符集名称]
      [COLLATE 比较规则名称]]
      
  ALTER TABLE 表名
      [[DEFAULT] CHARACTER SET 字符集名称]
      [COLLATE 比较规则名称]

  如果创建和修改表的语句中没有指明字符集和比较规则，将使用该表所在数据库的字符集和比较规则作为该表的字符集和比较规则。

1.4 列级别

• 对于存储字符串的列，同一个表中的不同的列也可以有不同的字符集和比较规则。我们在创建和修改列定义的时候可以指定该列的字符集和比较规则，语法如下：

  CREATE TABLE 表名(
      列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称],
      其他列...
  );
  
  ALTER TABLE 表名 MODIFY 列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称];

  对于某个列来说，如果在创建和修改的语句中没有指明字符集和比较规则，将使用该列所在表的字符集和比较规则作为该列的字符集和比较规则。

  在转换列的字符集时需要注意，如果转换前列中存储的数据不能用转换后的字符集进行表示会发生错误。比方说原先列使用的字符集是utf8，列中存储了一些汉字，现在把列的字符集转换为ascii的话就会出错，因为ascii字符集并不能表示汉字字符。

1.5 小结

• 我们介绍的这4个级别字符集和比较规则的联系如下：

  • 如果创建或修改列时没有显式的指定字符集和比较规则，则该列默认用表的字符集和比较规则。
  • 如果创建表时没有显式的指定字符集和比较规则，则该表默认用数据库的字符集和比较规则。
  • 如果创建数据库时没有显式的指定字符集和比较规则，则该数据库默认用服务器的字符集和比较规则。

• 知道了这些规则之后，对于给定的表，我们应该知道它的各个列的字符集和比较规则是什么，从而根据这个列的类型来确定存储数据时每个列的实际数据占用的存储空间大小了。比方说我们向表t中插入一条记录：

  mysql> INSERT INTO t(col) VALUES('我们');
  Query OK, 1 row affected (0.00 sec)
  
  mysql> SELECT * FROM t;
  +--------+
  | s      |
  +--------+
  | 我们    |
  +--------+
  1 row in set (0.00 sec)

  首先列col使用的字符集是gbk，一个字符’我’在gbk 中的编码为0xCED2，占用两个字节，两个字符的实际数据就占用4个字节。如果把该列的字符集修改为utf8的话，这两个字符就实际占用6个字节。

2、字符集与比较规则

2.1 utf8与utf8mb4

• utf8字符集表示一个字符需要使用1～4个字节，但是我们常用的一些字符使用1～3个字节就可以表示 了。而字符集表示一个字符所用的最大字节长度，在某些方面会影响系统的存储和性能，所以设计MySQL的设计者偷偷的定义了两个概念：

  • utf8mb3：阉割过的utf8字符集，只使用1～3个字节表示字符。
  • utf8mb4：正宗的utf8字符集，使用1～4个字节表示字符。

• 在MySQL中utf8是utf8mb3的别名，所以之后在MySQL中提到utf8就意味着使用1~3个字节来表示一个字符。如果大家有使用4字节编码一个字符的情况，比如存储一些emoji表情，那请使用utf8mb4。

• 此外，通过如下指令可以查看MySQL支持的字符集：

  mysql> SHOW CHARSET;
  +----------+---------------------------------+---------------------+--------+
  | Charset  | Description                     | Default collation   | Maxlen |
  +----------+---------------------------------+---------------------+--------+
  | armscii8 | ARMSCII-8 Armenian              | armscii8_general_ci |      1 |
  | ascii    | US ASCII                        | ascii_general_ci    |      1 |
  | big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
  | binary   | Binary pseudo charset           | binary              |      1 |
  | cp1250   | Windows Central European        | cp1250_general_ci   |      1 |
  | cp1251   | Windows Cyrillic                | cp1251_general_ci   |      1 |
  | cp1256   | Windows Arabic                  | cp1256_general_ci   |      1 |
  | cp1257   | Windows Baltic                  | cp1257_general_ci   |      1 |
  | cp850    | DOS West European               | cp850_general_ci    |      1 |
  | cp852    | DOS Central European            | cp852_general_ci    |      1 |
  | cp866    | DOS Russian                     | cp866_general_ci    |      1 |
  | cp932    | SJIS for Windows Japanese       | cp932_japanese_ci   |      2 |
  | dec8     | DEC West European               | dec8_swedish_ci     |      1 |
  | eucjpms  | UJIS for Windows Japanese       | eucjpms_japanese_ci |      3 |
  | euckr    | EUC-KR Korean                   | euckr_korean_ci     |      2 |
  | gb18030  | China National Standard GB18030 | gb18030_chinese_ci  |      4 |
  | gb2312   | GB2312 Simplified Chinese       | gb2312_chinese_ci   |      2 |
  | gbk      | GBK Simplified Chinese          | gbk_chinese_ci      |      2 |
  | geostd8  | GEOSTD8 Georgian                | geostd8_general_ci  |      1 |
  | greek    | ISO 8859-7 Greek                | greek_general_ci    |      1 |
  | hebrew   | ISO 8859-8 Hebrew               | hebrew_general_ci   |      1 |
  | hp8      | HP West European                | hp8_english_ci      |      1 |
  | keybcs2  | DOS Kamenicky Czech-Slovak      | keybcs2_general_ci  |      1 |
  | koi8r    | KOI8-R Relcom Russian           | koi8r_general_ci    |      1 |
  | koi8u    | KOI8-U Ukrainian                | koi8u_general_ci    |      1 |
  | latin1   | cp1252 West European            | latin1_swedish_ci   |      1 |
  | latin2   | ISO 8859-2 Central European     | latin2_general_ci   |      1 |
  | latin5   | ISO 8859-9 Turkish              | latin5_turkish_ci   |      1 |
  | latin7   | ISO 8859-13 Baltic              | latin7_general_ci   |      1 |
  | macce    | Mac Central European            | macce_general_ci    |      1 |
  | macroman | Mac West European               | macroman_general_ci |      1 |
  | sjis     | Shift-JIS Japanese              | sjis_japanese_ci    |      2 |
  | swe7     | 7bit Swedish                    | swe7_swedish_ci     |      1 |
  | tis620   | TIS620 Thai                     | tis620_thai_ci      |      1 |
  | ucs2     | UCS-2 Unicode                   | ucs2_general_ci     |      2 |
  | ujis     | EUC-JP Japanese                 | ujis_japanese_ci    |      3 |
  | utf16    | UTF-16 Unicode                  | utf16_general_ci    |      4 |
  | utf16le  | UTF-16LE Unicode                | utf16le_general_ci  |      4 |
  | utf32    | UTF-32 Unicode                  | utf32_general_ci    |      4 |
  | utf8     | UTF-8 Unicode                   | utf8_general_ci     |      3 |
  | utf8mb4  | UTF-8 Unicode                   | utf8mb4_0900_ai_ci  |      4 |
  +----------+---------------------------------+---------------------+--------+
  41 rows in set (0.00 sec)
  
  # 或者
  SHOW CHARACTER SET;

2.2 比较规则

• 上表中，MySQL版本一共支持41种字符集，其中的Default collation列表示这种字符集中一种默认的比较规则，里面包含着该比较规则主要作用于哪种语言，比如utf8_polish_ci表示以波兰语的规则比较，utf8_spanish_ci是以西班牙语的规则比较，utf8_general_ci是一种通用的比较规则。

• 后缀表示该比较规则是否区分语言中的重音、大小写。具体如下：

  

• 最后一列Maxlen，它代表该种字符集表示一个字符最多需要几个字节。

• 这里把常见的字符集和对应的Maxlen显式如下：

  

• 常见操作：

  # 查看GBK字符集的比较规则
  mysql> SHOW COLLATION LIKE 'gbk%';
  +----------------+---------+----+---------+----------+---------+---------------+
  | Collation      | Charset | Id | Default | Compiled | Sortlen | Pad_attribute |
  +----------------+---------+----+---------+----------+---------+---------------+
  | gbk_bin        | gbk     | 87 |         | Yes      |       1 | PAD SPACE     |
  | gbk_chinese_ci | gbk     | 28 | Yes     | Yes      |       1 | PAD SPACE     |
  +----------------+---------+----+---------+----------+---------+---------------+
  2 rows in set (0.00 sec)
  
  
  # 查看UTF-8字符集的比较规则
  mysql> SHOW COLLATION LIKE 'utf8%';
  +----------------------------+---------+-----+---------+----------+---------+---------------+
  | Collation                  | Charset | Id  | Default | Compiled | Sortlen | Pad_attribute |
  +----------------------------+---------+-----+---------+----------+---------+---------------+
  | utf8mb4_0900_ai_ci         | utf8mb4 | 255 | Yes     | Yes      |       0 | NO PAD        |
  | utf8mb4_0900_as_ci         | utf8mb4 | 305 |         | Yes      |       0 | NO PAD        |
  | utf8mb4_0900_as_cs         | utf8mb4 | 278 |         | Yes      |       0 | NO PAD        |
  | utf8mb4_0900_bin           | utf8mb4 | 309 |         | Yes      |       1 | NO PAD        |
  | utf8mb4_bin                | utf8mb4 |  46 |         | Yes      |       1 | PAD SPACE     |
  ………………
  
  # 查看服务器的字符集和比较规则
  mysql> SHOW VARIABLES LIKE '%_server';
  +-------------------------+--------------------+
  | Variable_name           | Value              |
  +-------------------------+--------------------+
  | character_set_server    | utf8mb4            |
  | collation_server        | utf8mb4_unicode_ci |
  | innodb_dedicated_server | OFF                |
  +-------------------------+--------------------+
  3 rows in set (0.00 sec)
  
  # 查看数据库的字符集和比较规则
  mysql> SHOW VARIABLES LIKE '%_database';
  +------------------------+--------------------+
  | Variable_name          | Value              |
  +------------------------+--------------------+
  | character_set_database | utf8mb4            |
  | collation_database     | utf8mb4_unicode_ci |
  | skip_show_database     | OFF                |
  +------------------------+--------------------+
  3 rows in set (0.00 sec)
  
  # 查看具体数据库的字符集
  mysql> SHOW CREATE DATABASE testdb;
  +----------+--------------------------------------------------------------------------------------------------------------------------------+
  | Database | Create Database                                                                                                                |
  +----------+--------------------------------------------------------------------------------------------------------------------------------+
  | test     | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
  +----------+--------------------------------------------------------------------------------------------------------------------------------+
  1 row in set (0.00 sec)
  
  # 修改具体数据库的字符集
  mysql> ALTER DATABASE testdb DEFAULT CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
  Query OK, 1 row affected, 2 warnings (0.01 sec)
  
  # 再次查看具体数据库的字符集
  mysql> SHOW CREATE DATABASE testdb;
  +----------+--------------------------------------------------------------------------------------------------+
  | Database | Create Database                                                                                  |
  +----------+--------------------------------------------------------------------------------------------------+
  | test     | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8 */ /*!80016 DEFAULT ENCRYPTION='N' */ |
  +----------+--------------------------------------------------------------------------------------------------+
  1 row in set (0.00 sec)
  
  # 查看表的字符集
  show create table employees;
  
  # 查看表的比较规则
  mysql> show table status from testdb like 'employees';
  +-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
  | Name      | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation       | Checksum | Create_options | Comment |
  +-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
  | employees | InnoDB |      10 | Dynamic    |  107 |            153 |       16384 |               0 |        81920 |         0 |           NULL | 2022-04-13 07:42:57 | 2022-04-13 07:42:57 | NULL       | utf8_general_ci |     NULL |                |         |
  +-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
  1 row in set (0.01 sec)
  
  # 修改表的字符集和比较规则
  ALTER TABLE employees DEFAULT CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

• utf8_unicode_ci和utf8_general_ci对中、英文来说没有实质的差别。
• utf8_general_ci校对速度快，但准确度稍差。
• utf8_unicode_ci准确度高，但校对速度稍慢。
• 一般情况，用utfa_general_ci就够了，但如果你的应用有德语、法语或者俄语，请一定使用utf8_unicode_ci。
• 修改了数据库的默认字符集和比较规则后，原来已经创建的表格的字符集和比较规则并不会改变，如果需要，那么需单独修改。

3、请求到响应过程中字符集的变化

• 我们知道从客户端发往服务器的请求木质上就是一个字符串，服务器向客户就返回的结果木质上也是一个字符串，而字符串其实是使用某种字符集编码的二进制数据。这个字符串可不是使用一种字符集的编码方式一条道走到黑的，从发送请求到返回结果这个过程中伴随着多次字符串的转换，在这个过程中会用到3个系统变量，我们先把它们写出来看一下：

  

  • 这几个系统变量在我的计算机上的默认值如下（不同操作系统的默认值可能不同）：

    mysql> show variables like 'character%';
    +--------------------------+--------------------------------+
    | Variable_name            | Value                          |
    +--------------------------+--------------------------------+
    | character_set_client     | utf8mb4                        |
    | character_set_connection | utf8mb4                        |
    | character_set_database   | utf8mb3                        |
    | character_set_filesystem | binary                         |
    | character_set_results    | utf8mb4                        |
    | character_set_server     | utf8mb4                        |
    | character_set_system     | utf8mb3                        |
    | character_sets_dir       | /usr/share/mysql-8.0/charsets/ |
    +--------------------------+--------------------------------+
    8 rows in set (0.01 sec)

• 为了体现出字符集在请求处理过程中的变化，我们这里特意修改一个系统变量的值：

  mysql> set character_set_connection = gbk;
  Query OK, 0 rows affected (0.00 sec)

  • 现在假设我们客户端发送的请求是下边这个字符串：

    SELECT * FROM t WHERE s = '我';

    为了方便大家理解这个过程，我们只分析字符 ‘我’ 在这个过程中字符集的转换。

  • 现在看一下在请求从发送到结果返回过程中字符集的变化：

    • ①客户端发送请求所使用的字符集。一般情况下客户端所使用的字符集和当前操作系统一致，不同操作系统使用的字符集可能不一样，如下：

      • 类Unix系统使用的是utf8。
      • Windows使用的是gbk。

      当客户端使用的是utf8字符集，字符 ‘我’ 在发送给服务器的请求中的字节形式就是：0xE68891。

      提示：如果你使用的是可视化工具，比如navicat之类的，这些工具可能会使用自定义的字符集来编码发送到服务器的字符串，而不采用操作系统默认的字符集（所以在学习的时候还是尽量用 命令行窗口）。

    • ②服务器接收到客户端发送来的请求其实是一串二进制的字节，它会认为这串字节采用的字符集是 character_set_client，然后把这串字节转换为character_set_connection字符集编码的字符。

      • 由于我的计算机上character_set_client的值是utf8 ，首先会按照utf8字符集对字节串0xE68891进行解码，得到的字符串就是 ‘我’ ，然后按照character_set_connection代表的字符集，也就是gbk进行编码，得到的结果就是字节串0xCED2。

    • ③因为表t的列col采用的是gbk字符集，与character_set_connection一致，所以直接到列中找字节值为0xCED2的记录，最后找到了一条记录。

      提示：如果某个列使用的字符集和character_set_connection代表的字符集不一致的话，还需要进行一次字符集转换。

    • ④上一步骤找到的记录中的col列其实是一个字节串0xCED2，col列是采用gbk进行编码的，所以首先会将这个字节串使用gbk进行解码，得到字符串 ‘我’ ，然后再把这个字符串使用character_set_results代表的字符集，也就是utf8进行编码，得到了新的字节串：0xE68891，然后发送给客户端。

    • ⑤由于客户端是用的字符集是utf8 ，所以可以顺利的将0xE68891解释成字符我 ，从而显示到我们的显示器上，所以我们人类也读懂了返回的结果。

    

• 从这个分析中我们可以得出这么几点需要注意的地方：

  • 服务器认为客户端发送过来的请求是用character_set_client编码的。

    • 假设你的客户端采用的字符集和character_set_client不一样的话，这就会出现识别不准确的情况。比如我的客户端使用的是utf8字符集，如果把系统变量character_set_client的值设置为ascii的话，服务器可能无法理解我们发送的请求，更别谈处理这个请求了。

  • 服务器将把得到的结果集使用character-set-results编码后发送给客户端。

    • 假设你的客户端采用的字符集和character_set-results不一样的话，这就可能会出现客户端无法解码结果集的情况，结果就是在你的屏上出现乱码。比如我的客户端用的是utf8字符集，如果把系统变量character_set_results的设置为ascii的话，可能会产生乱码。

  • character_set_connection只是服务器将请求的字节串从character_set_client转挨为character_set.conmection时使用，一定要注意，该字符集包含的字符范图一定涵盖请求中的字符，要不然会导致有的符无法使用character_set-connection代表的字符集进行编码。

  • 经验：开发中通常把character_set_client、character_set_connection、character_set_results 这三个系统变量设置成和客户端使用的字符集一致的情况，这样减少了很多无谓的字符集转换。为了方便我们设置，MySQL提供了一条非常简便的语句：

    SET NAMES 字符集名

    这一条语句产生的效果和我们执行这3条的效果是一样的：

    SET character_set_client = 字符集名;
    StI character_set_connection = 字符集名;
    SCT character_set_results = 字符集名;

    另外，如果你想在启动客户端的时候就把character-set_client、character_set_connection、character_set_results这三个系统变量的值设置成一样的，那我们可以在启动客户端的时候指定一个叫default-character-set的后动选项，比如在配置文件里可以这么写：

    [client]
    default-character-set = utf8

    它起到的效果和执行一遍SET NANES utf8是一样一样的，都会将那三个系统变量的值设置成utf8。

4、SQL大小写规范

4.1 Windows和Linux平台区别

• 在SQL中，关键字和函数名是不用区分字母大小写的，比如SELECT、WHERE、ORDER、GROUP BY等关键字，以及ABS、MOD、ROUND、MAX等函数名。

• 不过在SQL中，你还是要确定大小写的规范，因为在Linux和Windows环境下，你可能会遇到不同的大小写问题。windows系统默认大小写不敏感，但是linux系统是大小写敏感的。

• 通过如下命令查看：

  SHOW VARIABLES LIKE '%lower_case_table_names%'

  • Windows系统下：

    mysql> SHOW VARIABLES LIKE '%lower_case_table_names%';
    +------------------------+-------+
    | Variable_name          | Value |
    +------------------------+-------+
    | lower_case_table_names | 1     |
    +------------------------+-------+
    1 row in set, 1 warning (0.02 sec)

  • Linux系统下：

    mysql> SHOW VARIABLES LIKE '%lower_case_table_names%';
    +------------------------+-------+
    | Variable_name          | Value |
    +------------------------+-------+
    | lower_case_table_names | 0     |
    +------------------------+-------+
    1 row in set (0.00 sec)

    • lower_case_table_names参数值的设置：
      • 默认为0，大小写敏感。
      • 设置1，大小写不敏感。创建的表，数据库都是以小写形式存放在磁盘上，对于sql语句都是转换为小写对表和数据库进行查找。
      • 设置2，创建的表和数据库依据语句上格式存放，凡是查找都是转换为小写进行。

    • MySQL在Linux下数据库名、表名、列名、别名大小写规则是这样的：
      • 数据库名、表名、表的别名、变量名是严格区分大小写的；
      • 关键字、函数名称在SQL中不区分大小写；
      • 列名（或字段名）与列的别名（或字段别名）在所有的情况下均是忽略大小写的；
    • MySQL在Windows的环境下全部不区分大小写。

4.2 Linux下大小写规则设置

• 当想设置为大小写不敏感时，要在my.cnf这个配置文件[mysqld]中加入lower_case_table_names=1，然后重启服务器。
  • 但是要在重启数据库实例之前就需要将原来的数据库和表转换为小写，否则将找不到数据库名。
  • 此参数适用于MySQL5.7。在MySQL 8下禁止在重新启动MySQL服务时将lower_case_table_names设置成不同于初始化MySQL服务时设置的lower_case_table_names值。如果非要将MySQL8设置为大小写不敏感，具体步骤为：
    • ①停止MySQL服务。
    • ②删除数据目录，即删除/var/lib/mysql目录。
    • ③在MySQL配置文件（/etc/my.cnf）中添加lower_case_table_names=1。
    • ④启动MySQL服务。

4.3 SQL编写建议

• 如果你的变量名命名规范没有统一，就可能产生错误。这里有一个有关命名规范的建议：
  • 关键字和函数名称全部大写；
  • 数据库名、表名、表别名、字段名、字段别名等全部小写；
  • SQL语句必须以分号结尾。
• 数据库名、表名和字段名在Linux MySQL环境下是区分大小写的，因此建议你统一这些字段的命名规则，比如全部采用小写的方式。
• 虽然关键字和函数名称在SQL中不区分大小写，也就是如果小写的话同样可以执行。但是同时将关键词和函数名称全部大写，以便于区分数据库名、表名、字段名。

5、sql_mode的合理设置

• sql_mode会影响MySQL支持的5QL语法以及它执行的数据验证检查。通过设置sql_mode，可以完成不同严格程度的数据校验，有效地保障数据准确性。
• MySQL服务器可以在不同的SQL模式下运行，并且可以针对不同的客户端以不同的方式应用这些模式，具体现决于sqL_mode系统变量的值。
• MySQL5.6和MySQL5.7默认的sqL_mode模式参数是不一样的：
  • 5.6的mode默认值为空（即：NO_ENGINE_SUBSTITUTION），其实表示的是一个空值，相当于没有什么模式设置，可以理解为宽松模式。在这种设置是可以允许一些非法操作的，比如允许一些非法数据的插入。
  • 5.7的mode是STRICT_TRANS_TABLES，也就是严格模式。用于进行数的严格校验，错误数据不能插入，报error（错误），并且事务回滚。

5.1 宽松模式 vs 严格模式

• 宽松模式：
  • 如果设置的是宽松模式，那么我们在插入数据的时候，即便是给了一个错误的数据，也可能会被接受， 并且不报错。
    • 举例：我在创建一个表时，该表中有一个字段为name，给name设置的字段类型时 char(10) ，如果我 在插入数据的时候，其中name这个字段对应的有一条数据的 长度超过了10 ，例如’1234567890abc’，超过了设定的字段长度10，那么不会报错，并且取前10个字符存上，也就是说你这个数据被存为了’1234567890’，而’abc’就没有了。但是，我们给的这条数据是错误的，因为超过了字段长度，但是并没 有报错，并且mysql自行处理并接受了，这就是宽松模式的效果。
    • 应用场景：通过设置sql mode为宽松模式，来保证大多数sql符合标准的sql语法，这样应用在不同数据 库之间进行迁移时，则不需要对业务sql进行较大的修改。
• 严格模式：
  • 出现上面宽松模式的错误，应该报错才对，所以MySQL5.7版本就将sql_mode默认值改为了严格模式。所 以在生产等环境中，我们必须采用的是严格模式，进而开发、测试环境的数据库也必须要设置，这样在 开发测试阶段就可以发现问题。并且我们即便是用的MySQL5.6，也应该自行将其改为严格模式。
    • 开发经验：MySQL等数据库总想把关于数据的所有操作都自己包揽下来，包括数据的校验，其实开发 中，我们应该在自己开发的项目程序级别将这些校验给做了，虽然写项目的时候麻烦了一些步骤，但是这样做之后，我们在进行数据库迁移或者在项目的迁移时，就会方便很多。
    • 改为严格模式后可能会存在的问题：若设置模式中包含了NO_ZERO_DATE，那么MySQL数据库不允许插入零日期，插入零日期会抛出错误而不是警告。例如，表中含字段TIMESTAMP列（如果未声明为NULL或显示DEFAULT子句）将自动分配DEFAULT ‘0000-00-00 00:00:00’（零时间戳），这显然是不满足sql_mode中的NO_ZERO_DATE而报错。

5.2 模式查看和设置

• 查看当前的sql_mode：

  select @@session.sql_mode
  select @@global.sql_mode
  # 或者
  show variables like 'sql_mode';

  

• 临时设置方式：设置当前窗口中设置sql_mode：

  SET GLOBAL sql_mode = 'modes...'; # 全局
  SET SESSION sql_mode = 'modes...'; # 当前会话

  # 改为严格模式。此方法只在当前会话中生效，关闭当前会话就不生效了。
  set SESSION sql_mode='STRICT_TRANS_TABLES';
  
  # 改为严格模式。此方法在当前服务中生效，重启MySQL服务后失效。
  set GLOBAL sql_mode='STRICT_TRANS_TABLES';

• 永久设置方式：在/etc/my.cnf中配置sql_mode：

  • 在my.cnf文件(windows系统是my.ini文件)，新增：

    [mysqld]
    sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

    然后重启MySQL。

  • 当然生产环境上是禁止重启MySQL服务的，所以采用临时设置方式 + 永久设置方式来解决线上的问题， 那么即便是有一天真的重启了MySQL服务，也会永久生效了。

5.3 sql_mode常用值

值	说明
ONLY_FULL_GROUP_BY	对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因此列不在GROUP BY从句中。
NO_AUTO_VALUE_ON_ZERO	该值影响自增列的插入。在默认设置下，插入0或NULL代表生成下一个自增长值。如果用户希望插入的值为0，而该列又是自增长值，那么该模式就起作用了。
STRICT_TRANS_TABLES	在该模式下，如果一个值不能插入到一个事务表中，则中断当前的操作，对非事务表不做限制。
NO_ZERO_IN_DATE	在严格模式下，不允许日期和月份为”零”。
NO_ZERO_DATE	在该模式下，MySQL不允许插入”零”日期，插入零日期会抛出错误而不是警告。
ERROR_FOR_DIVISION_BY_ZERO	在INSERT或UPDATE过程中，如果数据被清零，则产生错误而非警告。如果未给出该模式，那么数据被清零时，MySQL返回NULL。
NO_AUTO_CREATE_USER	禁止GRANT创建密码为空的用户。
NO_ENGINE_SUBSTITUTION	如果需要的存储引擎被禁用或未编译，那么抛出错误。不设置该模式，用默认的存储引擎代替，并抛出一个异常。
PIPES_AS_CONCAT	将”||”视为字符串的连接操作符而非或运算符，这和ORACLE数据库是一样的，也和字符串的拼接函数Concat相似。
ANSI_QUOTES	启用ANSI_QUOTES后，不能用双引号来引用字符串，因为它被解释为识别符。