SQL mode of the MySQL server

SQL _modeSystem variables can regulate the SQL mode of MySQL

Any client can change the MySQL server's response to itself without affecting other clients.

If you want to set the SQL mode when MySQL starts, you can add the SQL _mode option to the MySQL configuration file.

SQL-mode = "traditional"

If you modify the SQL mode during running, you can use the following command:

Setsql_mode = "traditional ";

 

Client1

Mysql>Show variables like "SQL _mode ";

+ --------------- + ------- +

| Variable_name | value |

+ --------------- + ------- +

| SQL _mode |

+ --------------- + ------- +

1 row in SET (0.00 Sec)

 

Mysql>Set SQL _mode = "traditional ";

Query OK, 0 rows affected (0.23 Sec)

 

Mysql>Show variables like "SQL _mode ";

+ --------------- + ------------------------- +

| Variable_name | value |

+ --------------- + ------------------------- +

| SQL _mode | strict_trans_tables, strict_all_tables, no_zero_in_date, no_zero_date, week, traditional, no_auto_create_user, no_engine_substitution |

+ --------------- + ------------------------- +

1 rowin set (0.00 Sec)

 

Mysql>

 

Client2

Client1 has modified the SQL mode, but the SQL mode of Client2 has not changed. This shows that the mode changes between clients are independent of each other.

Mysql>Show variables like "SQL _mode ";

+ --------------- + ------- +

| Variable_name | value |

+ --------------- + ------- +

| SQL _mode |

+ --------------- + ------- +

1 row in SET (0.00 Sec)

 

Mysql>

 

Set global variables (to set global variables, add the Global keyword and require the super permission)

Mysql>Set globalsql_mode = "traditional ";

Query OK, 0 rows affected (0.02 Sec)

Select @ session. SQL _mode-- View the SQL mode of the current session

Select @ Global. SQL _mode-- View System session SQL Mode

After global variables are set, the new client is connected to the global SQL mode by default.