【MySQL】环境配置

目录

• 1. 环境变量配置
  • 1.1. session & global
    • 1.1.1. 修改全局变量
    • 1.1.2. 会话级别(session)
  • 1.2. 静态参数和动态参数
• 2. 配置项
  • 2.1. SQL Mode
    • 2.1.1. 查看与设置
    • 2.1.2. 版本变更
    • 2.1.3. 最常用 sql_mode
  • 2.2. charset
    • 2.2.1. charset
    • 2.2.2. SET names
  • 2.3. 查询相关配置
  • 2.4. 时间设置
    • 2.4.1. 设置时区
• 3. 连接
  • 3.1. 连接机制

1. 环境变量配置

1.1. session & global

默认不填写就是session级别；

1.1.1. 修改全局变量

在MySQL中要修改全局（global）变量，有两种方法：

1. 修改my.ini配置文件，如果要设置全局变量最简单的方式是在my.ini文件中直接写入变量配置。重启数据库服务就可以使全局变量生效。

2. 在不修改配置文件的基础上，使用关键字global设置全局变量：
   set global autocommit=1; # 将autocommit变量的值设置为ON

   需要注意的是，使用此方法对global全局变量的设置仅对于新开启的会话才是有效的，对已经开启的会话不生效。

1.1.2. 会话级别(session)

1. 在MySQL中要修改会话（session）变量，可以使用session关键字，如：

   set session autocommit=1;
   修改session变量配置，仅仅是对本session的变量配置有效，对于其他session无效。
   

2. 虽然设置了global变量、session变量，但是在mysql服务重启之后，数据库的配置又会重新初始化，一切按照my.ini的配置进行初始化。global和session的配置都会失效了。

参考：

1. MySQL设置global变量和session变量的两种方法详解

备注:

1. SET 命令格式:

    SET variable = expr [, variable = expr] ...
   
    variable: {
        user_var_name
    | param_name
    | local_var_name
    | {GLOBAL | @@GLOBAL.} system_var_name
    | {PERSIST | @@PERSIST.} system_var_name
    | {PERSIST_ONLY | @@PERSIST_ONLY.} system_var_name
    | [SESSION | @@SESSION. | @@] system_var_name
    }
   
    //示例:
    SET SESSION sql_mode = 'TRADITIONAL';
    SET LOCAL sql_mode = 'TRADITIONAL';
    SET @@SESSION.sql_mode = 'TRADITIONAL';
    SET @@LOCAL.sql_mode = 'TRADITIONAL';
    SET @@sql_mode = 'TRADITIONAL';
    SET sql_mode = 'TRADITIONAL';
   

   参考: mysql文档: SET Syntax for Variable Assignment

1.2. 静态参数和动态参数

1. 概念
   静态参数：只有数据库进行重启才会生效（数据库安装时就要确定好）
   动态参数：在数据库运行过程中可以随时修改；

---

2. 配置项

2.1. SQL Mode

2.1.1. 查看与设置

1. 设置

--配置
SET GLOBAL sql_mode = 'modes';
SET SESSION sql_mode = 'modes';
--查看
SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;

2.1.2. 版本变更

建议查看mysql官方文档；

1. mysql 5.7

   1. The default SQL mode in MySQL 5.7 includes these modes: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, and NO_ENGINE_SUBSTITUTION.
   2. These modes were added to the default SQL mode in MySQL 5.7: The ONLY_FULL_GROUP_BY and STRICT_TRANS_TABLES modes were added in MySQL 5.7.5. The NO_AUTO_CREATE_USER mode was added in MySQL 5.7.7. The ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, and NO_ZERO_IN_DATE modes were added in MySQL 5.7.8. For additional discussion regarding these changes to the default SQL mode value, see SQL Mode Changes in MySQL 5.7.

2. 可用值

   1. Default Value

      ONLY_FULL_GROUP_BY STRICT_TRANS_TABLES NO_ZERO_IN_DATE NO_ZERO_DATE
      ERROR_FOR_DIVISION_BY_ZERO NO_AUTO_CREATE_USER NO_ENGINE_SUBSTITUTION
      

   2. Valid Values

      ALLOW_INVALID_DATE SANSI_QUOTES ERROR_FOR_DIVISION_BY_ZERO HIGH_NOT_PRECEDENCE IGNORE_SPACE
      NO_AUTO_CREATE_USER NO_AUTO_VALUE_ON_ZERO NO_BACKSLASH_ESCAPES NO_DIR_IN_CREATE 
      NO_ENGINE_SUBSTITUTION NO_FIELD_OPTIONS NO_KEY_OPTIONS NO_TABLE_OPTIONS 
      NO_UNSIGNED_SUBTRACTION NO_ZERO_DATE NO_ZERO_IN_DATE ONLY_FULL_GROUP_BY 
      PAD_CHAR_TO_FULL_LENGTH PIPES_AS_CONCAT REAL_AS_FLOAT STRICT_ALL_TABLES 
      STRICT_TRANS_TABLES 
      

参考：

1. 5.1.10 Server SQL Modes–mysql 5.7

2.1.3. 最常用 sql_mode

1. ONLY_FULL_GROUP_BY
   对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中。

2. NO_AUTO_VALUE_ON_ZERO
   该值影响自增长列的插入。默认设置下，插入0或NULL代表生成下一个自增长值。如果用户希望插入的值为0，该列又是自增长的，那么这个选项就有用了。

3. STRICT_TRANS_TABLES
   在该模式下，如果一个值不能插入到一个事物表中，则中断当前的操作，对非事物表不做限制

4. NO_ZERO_IN_DATE
   在严格模式下，不允许日期和月份为零;

   1. The NO_ZERO_IN_DATE mode affects whether the server permits dates in which the year part is nonzero but the month or day part is 0. (This mode affects dates such as ‘2010-00-01’ or ‘2010-01-00’, but not ‘0000-00-00’.
   2. NO_ZERO_IN_DATE is deprecated. NO_ZERO_IN_DATE is not part of strict mode, but should be used in conjunction with strict mode and is enabled by default. A warning occurs if NO_ZERO_IN_DATE is enabled without also enabling strict mode or vice versa.

5. NO_ZERO_DATE
   设置该值，mysql数据库不允许插入零日期，插入零日期会抛出错误而不是警告。

6. ERROR_FOR_DIVISION_BY_ZERO
   在INSERT或UPDATE过程中，如果数据被零除，则产生错误而非警告。如 果未给出该模式，那么数据被零除时MySQL返回NULL

7. NO_AUTO_CREATE_USER
   禁止GRANT创建密码为空的用户

8. NO_ENGINE_SUBSTITUTION
   如果需要的存储引擎被禁用或未编译，那么抛出错误。不设置此值时，用默认的存储引擎替代，并抛出一个异常

9. PIPES_AS_CONCAT
   将"||"视为字符串的连接操作符而非或运算符，这和Oracle数据库是一样的，也和字符串的拼接函数Concat相类似

10. ANSI_QUOTES
    启用ANSI_QUOTES后，不能用双引号来引用字符串，因为它被解释为识别符

注意：

1. When working with InnoDB tables, consider also the innodb_strict_mode system variable. It enables additional error checks for InnoDB tables.

参考:

1. sql-mode MySQL8.0

2.2. charset

2.2.1. charset

2.2.2. SET names

set names 时会同时设置了3个session变量

SET character_set_client = charset_name;
SET character_set_results = charset_name;
SET character_set_connection = charset_name;

---

2.3. 查询相关配置

1. 慢查询

   1. MySQL的慢查询日志是MySQL提供的一种日志记录,
   2. 它用来记录在MySQL中响应时间超过阈值的语句,具体指运行时间;
   3. 超过long_query_time值的SQL,则会被记录到慢查询日志中;
   4. long_query_time 的默认值为10,即运行10秒以上的语句;

   MySQL数据库默认没有开启慢查询日志,
   --查看是否开启慢查询日志
       SHOW VARIABLES LIKE '%slow_query_log%';
   -- 开启慢查询日志
       set global slow_query_log=1; 
       只对当前数据库生效,如果MySQL重启后,则会失效;
   -- 查看系统默认慢的阈值时间;
       SHOW VARIABLES LIKE 'long_query_time%'; 
   -- 设置慢的阈值时间;
       set global long_query_time=3; 
       日志分析工具mysqldumpslow
   

---

2.4. 时间设置

2.4.1. 设置时区

1. 设置时区时间

--文件中更改
在[mysqld]部分的文件“my.cnf”中
default-time-zone='+00:00'
@@ global.time_zone变量
要查看它们设置的值：

-- global
SELECT @@global.time_zone;
要为其设置值，请使用以下任一项：

SET GLOBAL time_zone = '+8:00';
SET GLOBAL time_zone = 'Europe/Helsinki';
SET @@global.time_zone = '+00:00';
（使用像'Europe / Helsinki'这样的命名时区意味着您必须正确填充时区表。）

请记住，+02:00是一个偏移量。 TIME是时区（具有两个偏移量），CEST是与特定偏移量对应的时钟时间。

--查看
@@ session.time_zone变量
SELECT @@session.time_zone;

参考:

1. timezone - 如何设置MySQL的时区？ --12种解决方案

---

3. 连接

3.1. 连接机制

1. 连接管理线程在必要时创建一个新线程，但是会先尝试避免这样做，MySQL连接管理器线程首先会查看线程缓存是否包含空闲可用于连接的线程，如果有，则从线程缓存中选取一个空闲的线程分配给客户端，如果没有，则重新创建一个线程。当连接结束时，如果缓存未满，则其线程返回到线程缓存中，用于下次使用。

参考:

1. [玩转MySQL之二]MySQL连接机制浅析及运维 --介绍很详细

---