看了mysql8.0官网，发现set sql_mode原来有可以不用修改my.cnf或mysqld-auto.cnf就可以持久化系统变量的方式

概述

@@GLOBAL.sql_mode: 全局级别的设置，影响所有新的客户端连接。通常需要具有高级权限才能修改，且修改后对尚未建立连接的新会话生效，对当前已存在的会话无效。MySQL服务器重启后，如果没有在配置文件中永久设定，全局设置将恢复到服务器启动时的默认值或配置文件中的设定。

@@SESSION.sql_mode: 会话级别的设置，只影响当前已建立的会话。可以在会话内随时修改，仅对该会话内的查询有效。即使服务器全局设置改变，已存在的会话仍保持其自身的sql_mode设置，直到会话结束或手动更改。

在MySQL 8.0.36中，如果同时设置了@@GLOBAL.sql_mode、@@SESSION.sql_mode：
对于新建立的会话，其sql_mode初始值将继承自服务器的全局设置@@GLOBAL.sql_mode。
对于已存在的会话，其sql_mode由会话自身的@@SESSION.sql_mode决定，不受全局设置更改的影响，直至会话结束或在会话内主动更改。

mysql set语法官网

https://dev.mysql.com/doc/refman/8.0/en/set-variable.html

在set 中使用persist关键字持久化系统变量【直接上代码】

-- 先查询一下当前的sql_mode
select @@GLOBAL.sql_mode;
-- 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
-- 要将全局变量值写入my.cnf或mysqld-auto.cnf 使用persist关键字【官网原文描述】
-- 执行下面两行代码
set persist sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
set @@SESSION.sql_mode=@@GLOBAL.sql_mode;
-- set @@LOCAL.sql_mode=@@GLOBAL.sql_mode;

mysql 使用set 设置变量的其它用法【以下内容来源mysql8.0官网】

变量赋值的 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 变量赋值语法使您能够为影响服务器或客户端操作的不同类型的变量赋值：

• 用户定义的变量。请参见 第 11.4 节“用户定义的变量”。
• 存储过程和函数参数，以及存储的程序局部变量。请参见 第 15.6.4 节“存储程序中的变量”。
• 系统变量。请参见 第 7.1.8 节“服务器系统变量”。系统变量也可以在服务器启动时设置，如 第 7.1.9 节 “使用系统变量”中所述。

分配变量值的语句SET 不会写入二进制日志，因此在复制场景中，它仅影响执行该语句的主机。要影响所有复制主机，请在每个主机上执行该语句。

以下部分描述了 SET 设置变量的语法。它们使用 = 赋值运算符，但 := 赋值运算符也可以用于此目的。

• 用户定义的变量赋值
• 参数和局部变量赋值
• 系统变量赋值
• 设置错误处理
• 多变量赋值
• 表达式中的系统变量引用

用户定义的变量赋值【鼓励大家勇敢大胆经常使用mysql自定义变量】

用户定义的变量是在会话中本地创建的，并且仅存在于该会话的上下文中；请参见 第 11.4 节“用户定义的变量”。

用户定义的变量被写为 并被分配一个表达式值，如下所示： @var_name

SET @var_name = expr;

例子：

SET @name = 43;
SET @total_tax = (SELECT SUM(tax) FROM taxable_transactions);

正如这些语句所演示的， expr范围可以从简单（文字值）到更复杂（标量子查询返回的值）。

性能模式 user_variables_by_thread表包含有关用户定义变量的信息。请参见 第 29.12.10 节“性能模式用户定义变量表”。

参数和局部变量赋值

SET 适用于定义参数和局部变量的存储对象上下文中的参数和局部变量。以下过程使用increment过程参数和counter局部变量：

CREATE PROCEDURE p(increment INT)
BEGIN
  DECLARE counter INT DEFAULT 0;
  WHILE counter < 10 DO
    -- ... do work ...
    SET counter = counter + increment;
  END WHILE;
END;

系统变量赋值

MySQL 服务器维护配置其操作的系统变量。系统变量可以具有影响整个服务器操作的全局值、影响当前会话的会话值或两者。许多系统变量是动态的，可以在运行时使用语句进行更改， SET 以影响当前服务器实例的操作。 SET 还可以用于将某些系统变量保存到 mysqld-auto.cnf数据目录中的文件中，以影响后续启动的服务器操作。

如果针对敏感系统变量发出语句， 则在将其记录到常规日志和审核日志之前，SET会重写查询以用“ <redacted>”替换该值。即使服务器实例上无法通过密钥环组件进行安全存储，也会发生这种情况。

如果您更改会话系统变量，则该值在您的会话中保持有效，直到您将该变量更改为其他值或会话结束。该更改不会影响其他会话。

如果更改全局系统变量，系统会记住该值并用于初始化新会话的会话值，直到将该变量更改为其他值或服务器退出。任何访问全局值的客户端都可以看到更改。但是，更改仅影响更改后连接的客户端的相应会话值。全局变量更改不会影响任何当前客户端会话的会话值（甚至不会影响发生全局值更改的会话）。

要使全局系统变量设置永久化，以便它在服务器重新启动时应用，您可以将其保留到 mysqld-auto.cnf数据目录中的文件中。也可以通过手动修改 my.cnf选项文件来进行持久性配置更改，但这比较麻烦，并且手动输入的设置中的错误可能要等到很久以后才能发现。 SET 保留系统变量的语句更方便，并且避免了格式错误的设置的可能性，因为具有语法错误的设置不会成功并且不会更改服务器配置。有关持久化系统变量和mysqld-auto.cnf 文件的更多信息，请参见第 7.1.9.3 节 “持久化系统变量”。

笔记

设置或保留全局系统变量值始终需要特殊权限。设置会话系统变量值通常不需要特殊权限，并且可以由任何用户完成，但也有例外。有关更多信息，请参见 第 7.1.9.1 节 “系统变量权限”。

以下讨论描述了用于设置和保留系统变量的语法选项：

• 要将值分配给全局系统变量，请在变量名称前添加GLOBAL关键字或@@GLOBAL.限定符：

  SET GLOBAL max_connections = 1000;
  SET @@GLOBAL.max_connections = 1000;
  

• 要将值分配给会话系统变量，请在变量名称前添加SESSIONor LOCAL关键字、 @@SESSION.、 @@LOCAL.或@@ 限定符，或者不添加关键字或根本不添加修饰符：

  -- 以下方式全部是等效的
  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';
  

  客户端可以更改自己的会话变量，但不能更改任何其他客户端的会话变量。

• 要将全局系统变量保存到 mysqld-auto.cnf数据目录中的选项文件中，请在变量名称前面添加 PERSIST关键字或 @@PERSIST.限定符：

  SET PERSIST max_connections = 1000;
  SET @@PERSIST.max_connections = 1000;
  

  此 SET 语法使您能够在运行时进行配置更改，这些更改在服务器重新启动后也将持续存在。与 类似 SET GLOBAL， SET PERSIST设置全局变量运行时值，但还将变量设置写入文件 mysqld-auto.cnf（如果存在，则替换任何现有变量设置）。

• 要将全局系统变量保留到 mysqld-auto.cnf文件而不设置全局变量运行时值，请在变量名称前面添加PERSIST_ONLY关键字或 @@PERSIST_ONLY.限定符：

  SET PERSIST_ONLY back_log = 100;
  SET @@PERSIST_ONLY.back_log = 100;
  

  就像 一样PERSIST， PERSIST_ONLY将变量设置写入mysqld-auto.cnf。然而，与 不同的是PERSIST， PERSIST_ONLY它不会修改全局变量运行时值。这 PERSIST_ONLY适合配置只能在服务器启动时设置的只读系统变量。

要将全局系统变量值设置为编译后的 MySQL 默认值或将会话系统变量设置为当前对应的全局值，请将变量设置为 value DEFAULT。例如，以下两条语句在将 的会话值设置为 max_join_size当前全局值方面是相同的：

SET @@SESSION.max_join_size = DEFAULT;
SET @@SESSION.max_join_size = @@GLOBAL.max_join_size;

使用 SET将全局系统变量保留为值 DEFAULT或其文字默认值会为该变量分配其默认值，并将该变量的设置添加到mysqld-auto.cnf。要从文件中删除变量，请使用 RESET PERSIST.

某些系统变量无法持久保存或受到持久限制。请参见 第 7.1.9.4 节“不可持久和持久限制的系统变量”。

SET 如果在执行语句 时安装了插件，则可以持久保存插件实现的系统变量 。如果插件仍然安装，则持久插件变量的分配将在后续服务器重新启动时生效。如果不再安装插件，则当服务器读取文件时，插件变量不再存在mysqld-auto.cnf。在这种情况下，服务器将警告写入错误日志并继续：

currently unknown variable 'var_name'
was read from the persisted config file

显示系统变量名称和值：

• 使用该SHOW VARIABLES 语句；请参见 “SHOW VARIABLES 语句”。
• 几个Performance_Schema库中的表提供系统变量信息。
  请参见 Performance-Schema-System-Variable-Tables。
• Performance_Schema 的 variables_info 表中包含信息，显示每个系统变量最近被哪个用户在何时设置。请参见“Performance Schema variables_info 表”
• Performance Schema 持久变量表（persisted_variables table）为 mysqld-auto.cnf 文件提供了一个 SQL 接口，使得可以使用 SELECT 语句在运行时检查其内容。参见“Performance Schema 持久变量表（Performance Schema persisted_variables Table）”。

set错误处理

如果 SET 语句中的任何变量赋值失败，则整个语句都会失败，并且不会更改任何变量，也不会mysqld-auto.cnf 更改文件。

SET 在此处描述的情况下会产生错误。大多数示例显示 SET 使用关键字语法（例如， GLOBALor SESSION）的语句，但原则也适用于使用相应修饰符（例如， @@GLOBAL.or @@SESSION.）的语句。

• 使用 SET （或任何变体）设置只读变量会报错：

  mysql> SET GLOBAL version = 'abc';
  ERROR 1238 (HY000): Variable 'version' is a read only variable
  

• 使用GLOBAL、 PERSIST、 或 PERSIST_ONLY设置仅具有会话值的变量会报错：

  mysql> SET GLOBAL sql_log_bin = ON;
  ERROR 1228 (HY000): Variable 'sql_log_bin' is a SESSION
  variable and can't be used with SET GLOBAL
  

• 使用 SESSION设置仅具有全局值的变量会报错：

  mysql> SET SESSION max_connections = 1000;
  ERROR 1229 (HY000): Variable 'max_connections' is a
  GLOBAL variable and should be set with SET GLOBAL
  

• 省略GLOBAL、 PERSIST、 或 PERSIST_ONLY设置仅具有全局值的变量会报错：

  mysql> SET max_connections = 1000;
  ERROR 1229 (HY000): Variable 'max_connections' is a
  GLOBAL variable and should be set with SET GLOBAL
  

• 使用PERSIST或 PERSIST_ONLY设置无法持久化的变量会报错：

  mysql> SET PERSIST port = 3307;
  ERROR 1238 (HY000): Variable 'port' is a read only variable
  mysql> SET PERSIST_ONLY port = 3307;
  ERROR 1238 (HY000): Variable 'port' is a non persistent read only variable
  

• @@GLOBAL.，@@PERSIST.，@@PERSIST_ONLY.，@@SESSION.，和@@修饰符只适用于系统变量。 如果尝试将它们应用于用户定义的变量、存储过程或函数参数，或存储的程序局部变量，将会发生错误。

• 并非所有系统变量都可以设置为 DEFAULT。在这种情况下，分配 DEFAULT会导致错误。

• 尝试将 DEFAULT 分配给用户定义的变量、存储过程或函数参数，或存储程序的局部变量时，会发生错误。。

多变量赋值

一条SET 语句可以包含多个变量赋值，用逗号分隔。此语句将值分配给用户定义变量和会话系统变量

SET @x = 1, SESSION sql_mode = '';

如果在单个语句中设置多个系统变量，则语句中最新的GLOBAL、 PERSIST、PERSIST_ONLY或SESSION关键字将用于后续未指定关键字的赋值。

多变量赋值的示例：

SET GLOBAL sort_buffer_size = 1000000, SESSION sort_buffer_size = 1000000;
SET @@GLOBAL.sort_buffer_size = 1000000, @@LOCAL.sort_buffer_size = 1000000;
SET GLOBAL max_connections = 1000, sort_buffer_size = 1000000;

@@GLOBAL.、 @@PERSIST.、 @@PERSIST_ONLY.、 @@SESSION.和 修饰符@@ 仅适用于紧随其后的系统变量，不适用于任何剩余的系统变量。即：系统变量的修饰符不共用，不写修饰符默认是SESSION级别。此语句将sort_buffer_size 全局值设置为 50000，将会话值设置为 1000000：

SET @@GLOBAL.sort_buffer_size = 50000, sort_buffer_size = 1000000;

表达式中的系统变量引用【即：引用系统变量】

要在表达式中引用系统变量的值，请使用@@ [GLOBAL或SESSION或不写]修饰符之一；
select表达式中不允许使用@@PERSIST.和 @@PERSIST_ONLY.，但是set 表达式可以）例如，您可以在SELECT如下语句中检索系统变量值：

SELECT @@GLOBAL.sql_mode, @@SESSION.sql_mode, @@sql_mode;

笔记

在表达式中引用系统变量 @@var_name（使用 @@ 而不是 @@GLOBAL. 或 @@SESSION.），如果会话值存在，则返回会话值，否则返回全局值。这与 SET @@var_name = expr 不同，后者总是引用会话值。

原文地址：https://blog.csdn.net/ljh_learn_from_base/article/details/137735961

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！