How to set the sql_ mode value in mysql

This article shows you how to set the sql_ mode value in mysql, which is concise and easy to understand, which will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

Common values for sql_mode are as follows:

ONLY_FULL_GROUP_BY:

For GROUP BY aggregation operations, if the column in SELECT does not appear in GROUP BY, then the SQL is illegal because the column is not in the GROUP BY clause

NO_AUTO_VALUE_ON_ZERO:

This value affects the insertion of self-growing columns. By default, inserting 0 or NULL means the next self-growth value is generated. 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 there are no restrictions on non-transaction tables.

NO_ZERO_IN_DATE:

Date and month are not allowed to be zero in strict mode

NO_ZERO_DATE:

Set this value, the mysql database does not allow the insertion of a zero date, and inserting a zero date throws an error instead of a warning.

ERROR_FOR_DIVISION_BY_ZERO:

During the INSERT or UPDATE process, if the data is divided by zero, an error is generated instead of a warning. If the pattern is not given, MySQL returns NULL when the data is divided by zero

NO_AUTO_CREATE_USER:

Prohibit GRANT from creating users with empty passwords

NO_ENGINE_SUBSTITUTION:

If the required storage engine is disabled or uncompiled, an error is thrown. When this value is not set, it is replaced with the default storage engine and an exception is thrown

PIPES_AS_CONCAT:

Treat "| |" as the concatenation operator of the string rather than the OR operator, 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 refer to a string in double quotes because it is interpreted as an identifier

The sql_mode settings of ORACLE 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, in order to keep everyone's habit of using oracle, you can set the sql_mode of mysql as follows:

Add the following configuration to 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'

The above is how to set the sql_ mode value in mysql. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.