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

This article introduces what are the characteristics of SQL mode in MySQL. The content is very detailed. Interested friends can use it for reference. I hope it will be helpful to you.

Set SQL mode

To change the SQL mode at run time, sql_mode uses the following SET statement to set the global or session system variable

SET GLOBAL sql_mode = 'modes';SET SESSION sql_mode =' modes'

Pattern list

Pattern comment ALLOW_INVALID_DATES invalid date generates an error ERROR_FOR_DIVISION_BY_ZERO except 0 error NO_BACKSLASH_ESCAPES forbids the use of the backslash character (\) as an escape character in the string. When this mode is enabled, the backslash is like any other ordinary character. NO_UNSIGNED_SUBTRACTION subtracts UNSIGNED (one of which is a type) between integer values, which produces unsigned results by default. If the result is otherwise negative, it will result in error NO_ZERO_IN_DATE'0000-00-00' then allow and insert warning that the specified field in ONLY_FULL_GROUP_BYselect must appear in groupby, otherwise error STRICT_TRANS_TABLES enables strict SQL mode for transactional storage engine and, if possible, for non-transactional storage engine. STRICT_ALL_TABLES enables strict SQL mode for all storage engines. Invalid data value is rejected.

For details, please refer to https://dev.mysql.com/doc/ref...

Strict SQL mode

The MySQL server can run in different SQL modes and can apply these modes in different ways for different clients, depending on the value of the sql_mode system variable. In strict SQL mode, the server escalates some warnings to errors.

The strict SQL mode applies to the following statements

ALTER TABLE

CREATE TABLE

CREATE TABLE... SELECT

DELETE

INSERT

LOAD DATA

LOAD XML

SELECT SLEEP ()

UPDATE

In a stored program, if a program is defined when strict mode takes effect, a single statement of the type listed will be executed in strict SQL mode.

The strict SQL pattern applies to the following errors, which indicate that the input value is invalid or missing. If the value has the wrong column data type or may be out of range, the value is invalid. If the new row to be inserted does not contain the value of a column in its definition where NOT NULL does not have an explicit DEFAULT clause, the value is missing.

ER_BAD_NULL_ERROR

ER_CUT_VALUE_GROUP_CONCAT

ER_DATA_TOO_LONG

ER_DATETIME_FUNCTION_OVERFLOW

ER_DIVISION_BY_ZERO

ER_INVALID_ARGUMENT_FOR_LOGARITHM

ER_NO_DEFAULT_FOR_FIELD

ER_NO_DEFAULT_FOR_VIEW_FIELD

ER_TOO_LONG_KEY

ER_TRUNCATED_WRONG_VALUE

ER_TRUNCATED_WRONG_VALUE_FOR_FIELD

ER_WARN_DATA_OUT_OF_RANGE

ER_WARN_NULL_TO_NOTNULL

ER_WARN_TOO_FEW_RECORDS

ER_WRONG_ARGUMENTS

ER_WRONG_VALUE_FOR_TYPE

WARN_DATA_TRUNCATED

On the characteristics of the SQL pattern in MySQL what is shared here, I hope that 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.