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

This article mainly explains "what are the common values of SQL_MODE of Mysql". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "what are the common values of SQL_MODE of Mysql".

SQL_SAFE_UPDATES

When I was doing the database experiment to UPDATE the mysql table, mysql gave me an error: Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode

It turns out that mysql has a variable called SQL_SAFE_UPDATES. After searching for a long time, all the Chinese results are from copy to copy, and it is only a simple solution. Later found the foreigner net, it says like this: MySQL will refuse to run the UPDATE or DELETE query if executed without the WHERE clause or LIMIT clause. MySQL will also refuse the query which have WHERE clause but there is no condition with the KEY column (translate it yourself). But still do not know what this update safe model is used, can only make a simple guess: for the security of the data.

SQL_SAFE_UPDATES has two values: 0 and 1. Update and delete operation statements without where and limit conditions cannot be executed without SQL_SAFE_UPDATES = 1, even update and delete without key column conditions without where and limit conditions. When SQL_SAFE_UPDATES = 0, the update and delete operations will be executed smoothly. So obviously, the default value for this variable is 1. As shown in the figure, the update operation is successfully completed after the correction:

SQL_MODE (sql Grammar Mode)

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'

Thank you for your reading, the above is the content of "what are the common values of SQL_MODE of Mysql". After the study of this article, I believe you have a deeper understanding of what the common values of SQL_MODE of Mysql have, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.