Shulou(Shulou.com)05/31 Report--

This article introduces you how to set sql_mode in mysql, the content is very detailed, interested friends can refer to, hope to be helpful to you.

Reasonable setting of sql_mode for MySQL

Sql_mode is an easily overlooked variable, and the default value is null, which allows some illegal operations, such as the insertion of illegal data. This value must be set to strict mode in the production environment, so the database of the development and test environment must also be set so that problems can be found during the development and testing phase.

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'

On how to set up sql_mode in mysql to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.