Shulou(Shulou.com)06/01 Report--

The following together to understand the setting and query MYSQL SQL mode method, I believe that we will benefit a lot after reading, the text in the essence is not much, hope to set and query MYSQL SQL mode method this short content is what you want.

Set and query SQL mode

Change the SQL mode by changing the value of the sql_mode variable.

The SQL mode can be set at the global level or at the session level. The value of sql_mode can be modified when the database is started and when the database is running.

Set the SQL mode when the database starts

Use the-- sql_mode='modes' option on the command line, or use sql_mode= "modes" in the configuration file.

Modes is a comma-separated list of patterns.

To clear the SQL mode, set it to an empty string, such as sql_mode= ""

Set the SQL mode while the database is running

Use the set statement to change the value of sql_mode, for example:

SET GLOBAL sql_mode = 'modes'

SET SESSION sql_mode = 'modes'

Setting the value of a global variable requires SUPER permission, which is applied to all subsequent client connections.

Setting the session variable applies only to the current client, and each client can change its sessionSQL mode at any time.

Query SQL schema

To determine which SQL schema is currently in use, query using the following statement

SELECT @ @ GLOBAL.sql_mode

SELECT @ @ SESSION.sql_mode

The main SQL mode

The main sql_mode values are as follows:

ANSI

This is a combinatorial pattern, which looks like a more standard SQLSTRICT_TRANS_TABLES in syntax and behavior.

A strict schema for the transaction table. In this mode, if a value cannot be inserted into the transaction table, the statement is terminated. For non-transactional tables, the statement is also terminated if the value that cannot be inserted occurs in the first row of a single-row statement or multiple-row statement. TRADITIONAL

The traditional mode, which is also a combination mode. In this mode, when an incorrect value is inserted, an error is given instead of a warning. In a non-transactional storage engine, this may not be what we want, because statements break when an error occurs, but data modifications made before the error cannot be rolled back, resulting in partial updates. )

Complete list of SQL schemas

Sql patterns can be broadly divided into the following categories

Strict mode (including STRICT_ALL_TABLES and STRICT_TRANS_TABLES) STRICT_ALL_TABLES

If strict mode is enabled for all storage engines, invalid values are rejected. STRICT_TRANS_TABLES

Enable strict mode for the transaction storage engine and, where possible, enable strict mode for the flying transaction storage engine.

In MySQL5.7.4 to MySQL5.7.7, strict mode includes the effects of ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE and NO_ZERO_IN_DATE.

NO_ZERO_DATE used to limit the value of 0 and used with strict mode

Affects whether the database allows' 0000-00' as a valid date. The effect also depends on whether strict mode is enabled.

If this mode is enabled, the value of '0000-0000' is allowed and the insertion does not generate a warning

If this mode is disabled, the value of '0000-0000' is allowed but insertion generates a warning

If both this mode and strict mode are enabled, '0000-00' insertion and error NO_ZERO_IN_DATE will not be allowed unless the IGNORE is given at the same time.

Affects whether the database allows the month or date to be 0 when the year is not zero. The effect also depends on whether strict mode is enabled.

If this mode is enabled, a date value of 0 is allowed and the insertion does not generate a warning.

If this mode is disabled, a date value of 0 is allowed, but insertion generates a warning.

If this mode and strict mode are used at the same time, the date value containing 0 is not allowed to be inserted unless the IGNORE is given at the same time, and insertion will result in an error. For INSERT IGNORE and UPDATE IGNORE, a date value containing 0 is inserted as' 0000-00-00 'and generates a warning ERROR_FOR_DIVISION_BY_ZERO

Affects whether the database allows 0 as a divisor, including MOD (NPhon0). The effect also depends on whether strict mode is enabled.

If this mode is enabled, divisor of 0 is allowed and insertion does not generate a warning

If this mode is disabled, a divisor of 0 is allowed, but insertion produces a warning

If this mode and strict mode are used at the same time, a divisor of 0 is not allowed unless IGNORE is given at the same time, and insertion will result in an error. For INSERT IGNORE and UPDATE IGNORE, a divisor of 0 inserts NULL and generates a warning.

In previous versions of MySQL5.7.4, the above three modes were deprecated

In MySQL5.7.4 to MySQL5.7.7, the above three modes have no effect, and their effects are included in strict mode.

In MySQL5.7.8 and later versions, the above three patterns have their own separate role, rather than part of the strict mode. However, they should be used with strict mode, and they are on by default. A warning is generated if you use strict mode instead of the above mode, and if you use any of the above modes but do not enable strict mode.

Since the above three schemas are deprecated, they will be deleted as a separate schema name in subsequent versions of MySQL, and their effects will be included in strict schemas.

ANSI_QUOTES used to illustrate the role of symbols

Use "as an identifier (the same as `) rather than as a reference symbol for a string. When this mode is enabled,` can still be used as a reference identifier, but you cannot use double quotes to reference a text string. PIPES_AS_CONCAT

Use | | as a string concatenation operator (the same as CONCAT ()), rather than as a synonym for OR REAL_AS_FLOAT

Use REAL as a synonym for FLOAT. By default, MySQL regards REAL as a synonym for DOUBLE. NO_BACKSLASH_ESCAPES

Disables the backslash character () as an escape character in the string. When this mode is enabled, the backslash becomes a normal character. Affecting the manner or result of a statement.

NO_UNSIGNED_SUBTRACTION

For subtraction between integers, if a value is of type UNSIGNED, an unsigned integer result is generated by default, but if the result is negative, an error occurs

If NO_UNSIGNED_SUBTRACTION is enabled, no error will be reported if the result is negative.

IGNORE_SPACE

Spaces are allowed in the sum of the function name. This causes the built-in function name to be treated as a reserved word. Therefore, the same identifier as the function name must be referenced.

For example, because there is a COUNT () function, using count as the table name directly results in an error

Mysql > CREATE TABLE count (I INT)

ERROR 1064 (42000): You have an error in your SQL syntax

The table name should be referenced:

Mysql > CREATE TABLE count (I INT)

Query OK, 0 rows affected (0.00 sec)

HIGH_NOT_PRECEDENCE

In MySQL5.7, the order in which NOT a BETWEEN b AND C is calculated is NOT (a BETWEEN b AND c)

When HIGH_NOT_PRECEDENCE is enabled, the order is changed to (NOT a) the NO_FIELD_OPTIONS that BETWEEN b AND c uses to restrict the output of the SHOW CREATE TABLE statement

The MySQL-specific column option NO_KEY_OPTIONS is not displayed in the output of SHOW CREATE TABLE

The MySQL-specific index option NO_TABLE_OPTIONS is not displayed in the output of SHOW CREATE TABLE

MySQL-specific table options that affect the behavior of the database are not displayed in the output of SHOW CRETAE TABLE.

If you do not specify authentication information, the GRANT statement does not automatically create users. The GRANT statement must use the IDENTIFIED BY statement to specify a non-empty password or the IDENTIFIED WITH statement to specify the authentication plug-in.

It is recommended that you use the CREATE USER statement to create a user NO_AUTO_VALUE_ON_ZERO

NO_AUTO_VALUE_ON_ZERO affects the processing of auto-growing columns. Typically, the next serial number is generated by inserting NULL or 0. NO_AUTO_VALUE_ON_ZERO allows you to insert a value of 0 in an auto-growing column so that only by inserting NULL can the next sequence number be generated. NO_ENGINE_SUBSTITUTION

When a statement such as CREATE TABLE or ALTER TABLE specifies a disabled or uncompiled storage engine, it is automatically replaced with the default storage engine. When NO_ENGINE_SUBSTITUTION is not enabled. If the specified storage engine is not available, for CREATE TABLE, use the default storage engine and generate warnings, and for ALTER TABLE, generate warnings and do not modify the table.

When NO_ENGINE_SUBSTITUTION is enabled, if the specified storage engine is not available, either creating or modifying the table will result in an error. PAD_CHAR_TO_FULL_LENGTH

By default, the space at the end of the CHAR column is automatically deleted when querying. If PAD_CHAR_TO_FULL_LENGTH is enabled, spaces are not deleted and the queried Char value is completed to the full column length. NO_DIR_IN_CREATE

All INDEX DIRECTORY and DATA DIRECTORY instructions are ignored when creating the table. This option is useful in copied slave libraries. ONLY_FULL_GROUP_BY

Columns that are not named in the GROUP BY clause or cannot be uniquely identified by the GROUP BY clause cannot be included in the SELECT HAVING or ORDER BY list

Please refer to http://www.ywnds.com/?p=8184ALLOW_INVALID_DATES

Invalid dates are allowed, only the month is between 1-12 and the date is between 1-31, and the date is not fully checked. This mode applies only to DATE and DATETIME columns. When strict mode is disabled, invalid dates such as "2018-02-31" are converted to '0000-00-00' and generate a warning, and such invalid dates can generate errors if strict mode is enabled.

Combination of SQL modes

The following patterns are abbreviations for some of the combinations in the complete list of SQL schemas above

Full list of names ANSIREAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, and (on MySQL 5.7.5) ONLY_FULL_GROUP_BYDB2PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONSMSSQLPIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONSPOSTGRESQLPIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS NO_FIELD_OPTIONSORACLEPIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USERMAXDBPIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USERTRADITIONALSTRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION

After reading this article on setting and querying MYSQL SQL mode methods, many readers will want to know more about it. If you need more industry information, you can follow our industry information section.

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.