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

This article mainly explains "the introduction of SQL mode of MySQL". The content of the explanation in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "introduction to SQL mode of MySQL".

The MySQL server can run in different SQL modes, and can set different SQL modes in different clients. The SQL mode can be set by sql_mode system parameters.

The SQL schema affects the SQL syntax and data validation supported by MySQL.

MariaDB [test] > select @ @ sql_mode

+-- +

| | @ @ sql_mode |

+-- +

| | NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |

+-- +

1 row in set (0.08 sec)

NO_AUTO_CREATE_USER

Prevent the creation of database accounts without passwords through GRANT statements. When creating a user through GRANT, you must specify a non-empty password after the IDENTIFIED BY. It is recommended that you use the CREATE USER statement to create a database account and try to avoid using the Grant statement to create a database account.

NO_ENGINE_SUBSTITUTION

Controls how the default storage engine is automatically replaced when a CREATE TABLE or ALTER TABLE statement is executed that specifies a storage engine that is not supported or not compiled.

When the NO_ENGINE_SUBSTITUTION parameter is not enabled, for CREATE TABLE statements, if the specified storage engine is not available, the default storage is used and a warning is generated. For ALTER TABLE statements, a warning is generated and the storage engine conversion of the table fails.

When the NO_ENGINE_SUBSTITUTION parameter is enabled, an error occurs if the specified storage engine is not available, and the table is not created or changed successfully.

The most important SQL mode

ANSI

This pattern changes the syntax and behavior of SQL to be closer to the standard SQL.

STRICT_TRANS_TABLES

Exit the statement if the value cannot be inserted into the transaction table; for non-transactional tables, exit the statement if the inserted or updated value occurs in the first row of the SQL or multirow statement of a row.

TRADITIONAL

When you insert an incorrect value into a column, MySQL reports an error instead of a warning.

-- combination of SQL mode

Some special SQL patterns are abbreviations for the combination of some SQL

For example, ANSI is a combination of REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE,ONLY_FULL_GROUP_BY (MySQL 5.7.5) modes

DB2 is a combination of PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, and NO_FIELD_OPTIONS modes.

-- strict SQL mode

Strict mode controls how MySQL handles data that changes invalid or missing values in SQL statements such as INSERT or UPDATE. There are many reasons why the inserted or updated value is invalid, for example, it may be because the data type of the field, or because the value exceeds the maximum range of the field. For a non-empty field, when a value to be inserted is empty and there is no default value, this is a value loss. Strict mode can also affect DDL statements, such as CREATE TABLE.

If strict mode does not take effect, MySQL inserts adjusted values for invalid or missing values. In strict mode, errors can be skipped through INSERT IGNORE or UPDATE IGNORE statements.

In strict mode, for statements like SELECT that do not change the data, invalid values generate a warning, not an error. In strict mode, an error is generated for inserting or updating values that exceed the maximum length of the field, while in non-strict mode, a warning is generated and the truncated value is inserted or updated into the table.

Strict mode does not affect foreign key validation. You can validate foreign keys through the foreign_key_checks parameter.

MariaDB [test] > show variables like'% foreign%'

+-+ +

| | Variable_name | Value |

+-+ +

| | foreign_key_checks | ON |

+-+ +

1 row in set (0.00 sec)

-- sample ①

Strict SQL mode

MariaDB [test] > select @ @ sql_mode

+-- +

| | @ @ sql_mode |

+-- +

| | NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |

+-- +

1 row in set (0.08 sec)

MariaDB [test] > create table t (name varchar (20), email varchar (40))

Query OK, 0 rows affected (0.21 sec)

MariaDB [test] > insert into t values ('12345678900000000000000000000000000000000, the title title fire beijing @ hotmail.com')

Query OK, 1 row affected, 1 warning (0.00 sec)

MariaDB [test] > show warnings

+-- +

| | Level | Code | Message | |

+-- +

| | Warning | 1265 | Data truncated for column 'name' at row 1 | |

+-- +

1 row in set (0.00 sec)

MariaDB [test] > select * from t

+-+

| | name | email |

+-+

| | 123456789000000000 | fire beijing @ hotmail.com |

+-+

1 row in set (0.00 sec)

MariaDB [test] > set session sql_mode='STRICT_TRANS_TABLES'

Query OK, 0 rows affected (0.00 sec)

MariaDB [test] > select @ @ sql_mode

+-+

| | @ @ sql_mode |

+-+

| | STRICT_TRANS_TABLES |

+-+

1 row in set (0.00 sec)

MariaDB [test] > insert into t values ('12345678900000000000000000000000000000000, the title title fire beijing @ hotmail.com')

ERROR 1406 (22001): Data too long for column 'name' at row 1

-- sample ②

The difference between ANSI mode and traditional mode

MariaDB [test] > set session sql_mode='ANSI'

Query OK, 0 rows affected (0.00 sec)

MariaDB [test] > select @ @ sql_mode

+-- +

| | @ @ sql_mode |

+-- +

| | REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI |

+-- +

1 row in set (0.00 sec)

MariaDB [test] > drop table t

Query OK, 0 rows affected (0.13 sec)

MariaDB [test] > create table t (d datetime)

Query OK, 0 rows affected (0.21 sec)

MariaDB [test] > insert into t values ('2007-04-31')

Query OK, 1 row affected, 1 warning (0.01 sec)

MariaDB [test] > select * from t

+-+

| | d |

+-+

| | 0000-00-0000: 00:00 | |

+-+

1 row in set (0.00 sec)

MariaDB [test] > set session sql_mode='TRADITIONAL'

Query OK, 0 rows affected (0.00 sec)

MariaDB [test] > select @ @ sql_mode

+- -- +

| | @ @ sql_mode |

+- -- +

| | STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |

+- -- +

1 row in set (0.00 sec)

MariaDB [test] > insert into t values ('2007-04-31')

ERROR 1292 (22007): Incorrect datetime value: '2007-04-31' for column 'd'at row 1

-- sample ③

Comparison between ANSI mode and traditional mode

MariaDB [test] > set sql_mode='ANSI'

Query OK, 0 rows affected (0.00 sec)

MariaDB [test] > drop table t

Query OK, 0 rows affected (0.11 sec)

MariaDB [test] > create table t (I int)

Query OK, 0 rows affected (0.45 sec)

MariaDB [test] > insert into t values (9% 0)

Query OK, 1 row affected (0.08 sec)

MariaDB [test] > select * from t

+-+

| | I |

+-+

| | NULL |

+-+

1 row in set (0.00 sec)

MariaDB [test] > set session sql_mode='TRADITIONAL'

Query OK, 0 rows affected (0.00 sec)

MariaDB [test] > insert into t values (9% 0)

ERROR 1365 (22012): Division by 0

-- sample ④

Make a backslash a normal character

MariaDB [test] > set sql_mode='ansi'

Query OK, 0 rows affected (0.00 sec)

MariaDB [test] > select @ @ global.sql_mode

+-- +

| | @ @ global.sql_mode |

+-- +

| | NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |

+-- +

1 row in set (0.00 sec)

MariaDB [test] > select @ @ session.sql_mode

+-- +

| | @ @ session.sql_mode |

+-- +

| | REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI |

+-- +

1 row in set (0.00 sec)

MariaDB [test] > select @ @ sql_mode

+-- +

| | @ @ sql_mode |

+-- +

| | REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI |

+-- +

1 row in set (0.00 sec)

MariaDB [test] > drop table t

Query OK, 0 rows affected (0.14 sec)

MariaDB [test] > create table t (context varchar (20))

Query OK, 0 rows affected (0.17 sec)

MariaDB [test] > insert into t values ('\ beijing')

Query OK, 1 row affected (0.07 sec)

MariaDB [test] > select * from t

+-+

| | context |

+-+

| | eijing |

+-+

1 row in set (0.00 sec)

MariaDB [test] > insert into t values ('\\ beijing')

Query OK, 1 row affected (0.11 sec)

MariaDB [test] > select * from t

+-+

| | context |

+-+

| | eijing |

| |\ beijing |

+-+

2 rows in set (0.00 sec)

MariaDB [test] > set sql_mode='REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI,NO_BACKSLASH_ESCAPES'

Query OK, 0 rows affected (0.00 sec)

MariaDB [test] > insert into t values ('\\ beijing')

Query OK, 1 row affected (0.00 sec)

MariaDB [test] > select * from t

+-+

| | context |

+-+

| | eijing |

| |\ beijing |

| |\\ beijing |

+-+

3 rows in set (0.00 sec)

-- sample ⑤

ANSI mode contains PIPES_AS_CONCAT mode, which treats | as a connection operator.

MariaDB [test] > set sql_mode='ansi'

Query OK, 0 rows affected (0.00 sec)

MariaDB [test] > select @ @ session.sql_mode

+-- +

| | @ @ session.sql_mode |

+-- +

| | REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI |

+-- +

1 row in set (0.00 sec)

MariaDB [test] > select 'beijing' | |' 2008'

+-+

| | 'beijing' | |' 2008' |

+-+

| | beijing2008 |

+-+

1 row in set (0.00 sec)

MariaDB [test] > set sql_mode='NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

Query OK, 0 rows affected (0.00 sec)

MariaDB [test] > select 'beijing' | |' 2008'

+-+

| | 'beijing' | |' 2008' |

+-+

| | 1 |

+-+

1 row in set, 2 warnings (0.02 sec)

MariaDB [test] > show warnings

+-+

| | Level | Code | Message | |

+-+

| | Warning | 1292 | Truncated incorrect INTEGER value: 'beijing' |

| | Warning | 1292 | Truncated incorrect DOUBLE value: 'beijing' |

+-+

2 rows in set (0.00 sec)

-- sample ⑥

When migrating a MySQL database to other data, you can set sql_mode to NO_TABLE_OPTIONS mode, which removes the engine keyword

MariaDB [test] > show create table t

+-+ +

| | Table | Create Table |

+-+ +

| | t | CREATE TABLE `t` (

`context` varchar (20) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

+-+ +

1 row in set (0.00 sec)

MariaDB [test] > set session sql_mode='NO_TABLE_OPTIONS'

Query OK, 0 rows affected (0.00 sec)

MariaDB [test] > show create table t

+-+

| | Table | Create Table |

+-+

| | t | CREATE TABLE `t` (

`context` varchar (20) DEFAULT NULL

) |

+-+

1 row in set (0.00 sec)

Thank you for your reading, the above is the content of "introduction of SQL mode of MySQL". After the study of this article, I believe you have a deeper understanding of the introduction of SQL mode of MySQL, 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.