The MySQL server works in different SQL modes and can be applied in different ways for different clients. In this way, the application can tailor the server operation to suit its needs. Such patterns define the SQL syntax that MySQL should support, and what validation checks should be performed on the data. You can use the "--sql-mode=" modes "" option to set the default SQL mode by starting mysqld. You can also use Set [session| after booting] GLOBAL] sql_mode= ' modes ' statement, changing the mode by setting the Sql_mode variable.
Sql_mode common values are as follows:
Only_full_group_by: For a group by aggregation operation, if the column in select is not present in group by, then this SQL is not legal because the column is not in the GROUP BY clause
No_auto_value_on_zero: This value affects the insertion of the self-growing column. By default, inserting 0 or null represents the generation of the next self-growth value. This option is useful if the user wants to insert a value of 0 and the column is self-growing.
Strict_trans_tables: In this mode, if a value cannot be inserted into a transaction table, the current operation is interrupted and no restriction is made on the non-transactional table
No_zero_in_date: In strict mode, the date and month are not allowed to zero
No_zero_date: Setting this value, the MySQL database does not allow the insertion of the 0 period, and inserting the 0 period throws an error instead of a warning.
Error_for_division_by_zero: During an INSERT or update process, if the data is removed by 0, an error is generated instead of a warning. If the pattern is not given, then MySQL returns NULL when the data is removed by 0
No_auto_create_user: Disallow grant to create a user with a blank password
No_engine_substitution: Throws an error if the required storage engine is disabled or not compiled. When this value is not set, the default storage engine is substituted and an exception is thrown
Pipes_as_concat: Will "| |" The concatenation operator, rather than the operator, that is treated as a string, which is the same as the Oracle database, and similar to the concatenation function concat of the string.
Ansi_quotes: You cannot use double quotation marks to reference a string after Ansi_quotes is enabled because it is interpreted as a qualifier
Description: SQL mode can be set to multiple patterns separated by commas in the middle of the line.
MySQL's SQL Mode
