SET [GLOBAL|SESSION] <variable>

    注意

    与 MySQL 类似,对 GLOBAL 变量的更改不适用于已有连接或本地连接,只有新会话才会反映值的变化。

    SetStmt

    VariableAssignment:

    1. +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
    2. | Variable_name | Value |
    3. +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
    4. | sql_mode | 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 |
    5. +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
    6. 1 row in set (0.00 sec)
    1. SHOW SESSION VARIABLES LIKE 'sql_mode';
    1. +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
    2. | Variable_name | Value |
    3. | sql_mode | 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 |
    4. +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
    5. 1 row in set (0.00 sec)

    更新全局的 sql_mode

    1. Query OK, 0 rows affected (0.03 sec)

    检查更新之后的 sql_mode 的取值,可以看到 SESSION 级别的值没有更新:

    1. SHOW GLOBAL VARIABLES LIKE 'sql_mode';
    1. +---------------+-----------------------------------------+
    2. | Variable_name | Value |
    3. +---------------+-----------------------------------------+
    4. | sql_mode | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER |
    5. +---------------+-----------------------------------------+
    6. 1 row in set (0.00 sec)
    1. +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
    2. | Variable_name | Value |
    3. | sql_mode | 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 |
    4. +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
    5. 1 row in set (0.00 sec)

    SET SESSION 则可以立即生效:

    1. SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER';
    1. Query OK, 0 rows affected (0.01 sec)
    1. +---------------+-----------------------------------------+
    2. | Variable_name | Value |
    3. +---------------+-----------------------------------------+
    4. | sql_mode | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER |
    5. +---------------+-----------------------------------------+
    6. 1 row in set (0.00 sec)
    • 与 MySQL 不同,TiDB 中使用 SET GLOBAL 所作的修改会应用于集群中的全部 TiDB 实例。而在 MySQL 中,修改不会应用于副本。
    • TiDB 中的若干变量可读又可设置,这是与 MySQL 相兼容的要求,因为应用程序和连接器常读取 MySQL 变量。例如:JDBC 连接器同时读取和设置缓存查询的参数,尽管并不依赖这一行为。