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

This article shows you how to set up sql_mode in mysql. The content is concise and easy to understand. It will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

1. Perform SQL view

Select @ @ session.sql_mode

Global level: viewin

Select @ @ global.sql_mode

2, modify

Set @ @ session.sql_mode='xx_mode'set session sql_mode='xx_mode'

Global level: modifyin

Set global sql_mode='xx_mode';set @ @ global.sql_mode='xx_mode'

Session can be omitted. Default session, valid only for the current session.

For global modification, advanced permissions are required, which will only take effect on the next connection and will not affect the current session, and will expire after MySQL restart, because the corresponding values in the configuration file will be re-read when MySQL is restarted. If you want to take effect permanently, you need to modify the values in the configuration file.

Vi / etc/ my.cnf [mysqld] sql-mode = "xx_mode"

Save the exit, restart the server, and take effect permanently.

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

In strict mode, zero is not allowed for a date or month, and an error is reported as long as the month or day of the date contains a value of 0, with the exception of '0000-00'

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. Any one of the years, months and days that is not zero meets the requirement, and only '0000-00' will report an error.

ERROR_FOR_pISION_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

Update table set num = 5 / 0; an error will be reported after setting this mode. If it is not set, it will be modified successfully. The value of num is null.

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 above is how to set up sql_mode 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.

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.