What is the function of sql_mode in MySQL?

In MySQL, sql_mode is a session-level variable that controls how MySQL processes SQL statements. It can be set as a string of multiple parameters separated by commas.

The main functions of sql_mode are as follows:

1. Control how MySQL handles invalid data. For example, if the STRICT_TRANS_TABLES parameter is set, when inserting data, if the data does not meet the constraints defined for the table, MySQL will display an error and refuse to insert the data.
2. Control how MySQL handles dates and times. For example, by setting the NO_ZERO_DATE parameter, MySQL will not allow the use of ‘0000-00-00’ format when inserting dates and times.
3. Control how MySQL handles empty strings. For example, if the NO_ZERO_IN_DATE parameter is set, MySQL will not allow the use of the ‘0000-00-00’ format when inserting an empty string.
4. Control how MySQL compares strings. For example, when the NO_BACKSLASH_ESCAPES parameter is set, MySQL will not consider backslashes as escape characters when comparing strings.
5. Control how MySQL handles auto-increment primary keys. For example, by setting the parameter NO_AUTO_VALUE_ON_ZERO, MySQL will no longer treat 0 as a valid auto-increment value when inserting records.

You can set the value of sql_mode through the following methods:

1. Set the sql-mode parameter in the [mysqld] or [client] section of the MySQL configuration file to establish the default sql_mode value when starting the MySQL service.
2. When connecting to the MySQL server on the client side, you can use the SET statement to set the value of sql_mode. For example, SET sql_mode=’STRICT_TRANS_TABLES,NO_ZERO_IN_DATE’; This way, the sql_mode value set will only be effective for the current session.
3. Setting the value of sql_mode can be done by changing the sql_mode system variable at the global or session level. For example, using the command: SET GLOBAL sql_mode=’STRICT_TRANS_TABLES,NO_ZERO_IN_DATE’; will apply the sql_mode value to all sessions.

It is important to note that the default value of sql_mode is an empty string, indicating that no parameters are enabled. When setting sql_mode, one can use a string composed of one or more parameters, with multiple parameters separated by commas. Additionally, parameters can be added with “+” and removed with “-“. Parameters can also be replaced using “=”.

You can use the following statement to check the current value of sql_mode:

Get the current SQL mode configuration.