How to Set and Persist Global SQL Modes in MySQL?

Setting Global SQL Mode in MySQL

Multiple Mode Configuration

The command you provided is incorrect for setting multiple SQL modes globally. The correct syntax is:

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

Advantages of Session and Global Modes

• Session modes: Affect only the current connection and are reset upon connection termination. Advantages include:

  • Easily configurable for specific user sessions
  • No impact on other connections

• Global modes: Affect all connections to the database. Advantages include:

  • Consistent behavior for all users
  • Simplified configuration

Preferred Mode

The preferred mode depends on the specific requirements and usage patterns. If the same SQL mode is desired for all users, setting a global mode is recommended. Otherwise, session modes can provide flexibility.

Persistent Changes Using Config File

Note that setting SQL modes with SET GLOBAL will not persist after a MySQL restart. To make changes permanent, you should update the MySQL configuration file (e.g., my.cnf) as follows:

[mysqld]
sql_mode = 'NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES'

For MySQL versions 5.7.8 and above, use the following syntax:

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

Make sure to consult the MySQL documentation for your specific version for the available SQL mode options.

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