How to Permanently Set Global SQL_MODE in MySQL?

Setting Global SQL_MODE in MySQL: A Guide

When working with MySQL, one often encounters the need to modify the SQL_MODE setting. This mode governs a range of SQL behavior and parser configurations, affecting factors such as data validation, transaction handling, and more.

Is the Command Structure Correct?

The syntax for setting multiple modes in SQL_MODE using the SET command is correct:

SET GLOBAL SQL_MODE='NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLE','NO_AUTO_CREATE_USER','NO_ENGINE_SUBSTITUTION'

Advantages of Session and Global Modes

• Session mode: Affects only the current session and is reset upon session termination. Ideal for temporary or user-specific SQL_MODE settings.
• Global mode: Affects all subsequent sessions until modified. Suitable for system-wide, permanent configurations that apply to all users.

Global Mode vs. Session Mode

When faced with a scenario like updating the database with UNC values using different users, setting a global mode for NO_BACKSLASH_ESCAPES makes sense because it ensures that all users are affected, regardless of their session settings.

Setting Global SQL_MODE Permanently

Unfortunately, direct modification of global settings using SET is not persistent and resets after MySQL restart. To make changes permanent, set the mode in the configuration file(/etc/mysql/my.cnf):

[mysqld]
sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

For newer MySQL versions (e.g., 5.7.8 ), use the following syntax:

[mysqld]
sql-mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"

Conclusion

For system-wide, persistent SQL_MODE settings that affect all users, setting a global mode is the preferred approach. Permanent changes should be made in the configuration file, ensuring that the desired SQL behavior is consistent across sessions.

The above is the detailed content of How to Permanently Set Global SQL_MODE in MySQL?. For more information, please follow other related articles on the PHP Chinese website!