• SET [GLOBAL|SESSION]
    • 语法图
    • 示例
    • MySQL 兼容性
    • 另请参阅

    SET [GLOBAL|SESSION]

    SET [GLOBAL|SESSION] 语句用于在 SESSION 或 GLOBAL 的范围内，对某个 TiDB 的内置变量进行更改。需注意，对 GLOBAL 变量的更改不适用于已有连接或本地连接，这与 MySQL 类似。只有新会话才会反映值的变化。

    语法图

    SetStmt:

    SetStmt

    示例

    1. mysql> SHOW GLOBAL VARIABLES LIKE 'sql_mode';
    2. +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
    3. | Variable_name | Value                                                                                                                                     |
    4. +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
    5. | 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 |
    6. +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
    7. 1 row in set (0.00 sec)
    9. mysql> SHOW SESSION VARIABLES LIKE 'sql_mode';
    10. +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
    11. | Variable_name | Value                                                                                                                                     |
    12. +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
    13. | 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 |
    14. +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
    15. 1 row in set (0.00 sec)
    17. mysql> SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER';
    18. Query OK, 0 rows affected (0.03 sec)
    20. mysql> SHOW GLOBAL VARIABLES LIKE 'sql_mode';
    21. +---------------+-----------------------------------------+
    22. | Variable_name | Value                                   |
    23. +---------------+-----------------------------------------+
    24. | sql_mode      | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER |
    25. +---------------+-----------------------------------------+
    26. 1 row in set (0.00 sec)
    28. mysql> SHOW SESSION VARIABLES LIKE 'sql_mode';
    29. +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
    30. | Variable_name | Value                                                                                                                                     |
    31. +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
    32. | 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 |
    33. +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
    34. 1 row in set (0.00 sec)
    36. mysql> SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER';
    37. Query OK, 0 rows affected (0.01 sec)
    39. mysql> SHOW SESSION VARIABLES LIKE 'sql_mode';
    40. +---------------+-----------------------------------------+
    41. | Variable_name | Value                                   |
    42. +---------------+-----------------------------------------+
    43. | sql_mode      | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER |
    44. +---------------+-----------------------------------------+
    45. 1 row in set (0.00 sec)

    MySQL 兼容性

    SET [GLOBAL|SESSION] 语句与 MySQL 完全兼容。如有任何兼容性差异，请在 GitHub 上提交 issue。

    另请参阅

    • SHOW [GLOBAL|SESSION] VARIABLES