How to Correctly Set Multiple Global SQL_MODE Values in MySQL?

Setting Global SQL_MODE in MySQL: Multiple Modes, Advantages, and Preferred Approach

When attempting to set SQL_MODE globally in MySQL, users may encounter an error. This error prompts questions about the correct method for setting multiple modes, the advantages of setting both session and global modes, and the preferred approach.

Is this not the proper way to set multiple modes?

The provided command, set global sql_mode='NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLE','NO_AUTO_CREATE_USER','NO_ENGINE_SUBSTITUTION', is not the correct syntax for setting multiple SQL_MODE values globally. The correct approach is to combine the desired modes using commas within double quotes.

Example:

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

What are the advantages of setting session and global modes?

• Session mode: Applies to the current database connection and overrides any global settings. Allows for temporary changes to SQL_MODE settings without affecting other users.
• Global mode: Applies to all database connections on the server. Ensures a consistent SQL_MODE across all users.

Preferred Approach

The preferred approach to setting SQL_MODE depends on the specific requirements:

• Temporary changes: Use session mode to set SQL_MODE values for a specific user or session.
• Permanent changes: Use global mode to set SQL_MODE values for all users on the server. Configure these settings in the MySQL configuration file (my.cnf) to ensure they persist after server restarts.

Example of Setting Global SQL_MODE Permanently

In the MySQL configuration file (/etc/mysql/my.cnf):

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

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