MySQL's Sql_mode reasonable setup

MySQL's Sql_mode reasonable setup

Sql_mode is a very easy to ignore variable, the default value is a null value, in this setting can allow some illegal operation, such as allowing some illegal data insertion. This value must be set to strict mode in the production environment, so the database for the development and test environment must also be set up so that problems can be identified during the development testing phase

Sql_mode common values are as follows:
Only_full_group_by:

For a group by aggregation operation, if the column in select does not appear in group BY, then this SQL is not legal because the column is not in the GROUP BY clause

PS. Error raised: In aggregated query without GROUP by, expression #1 of SELECT list contains Nonagg

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 0 period to be inserted, and inserting the 0 period throws an error instead of a warning.

Error_for_division_by_zero:

During the 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:

When Ansi_quotes is enabled, you cannot use double quotation marks to refer to a string because it is interpreted as a qualifier

Oracle's Sql_mode settings are equivalent: Pipes_as_concat, Ansi_quotes, Ignore_space, No_key_options, No_table_options, NO_FIELD_OPTIONS, No_auto_create_user.

If you use MySQL, you can set the Sql_mode for MySQL as follows in order to keep the habit of using Oracle:

Add the following configuration in MY.CNF

[mysqld] Sql_mode = ' Only_full_group_by,no_auto_value_on_zero,strict_trans_tables,no_zero_in_date,no_zero_date,error_for_ Division_by_zero,no_auto_create_user,no_engine_substitution,pipes_as_concat,ansi_quotes'

MySQL's Sql_mode reasonable setup