Detailed explanation of sql_mode mode example in MySQL

Detailed explanation of sql_mode mode example in MySQL

This article describes the sql_mode mode in MySQL with examples. Share with you for your reference, the details are as follows:

There is an environment variable sql_mode in the MySQL database, which defines the SQL syntax, data validation, etc. that MySQL should support! We can view the sql_mode used by the current database in the following ways:

mysql> select @@sql_mode;
+----------------------------------------------------------------+
| @@sql_mode |
+----------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------+

MySQL 5.0 and above versions support three sql_mode modes

ANSI Mode

In loose mode, the inserted data is checked. If it does not conform to the defined type or length, the data type is adjusted or truncated before saving, and a warning is issued.

TRADITIONAL mode

Strict mode: When inserting data into the MySQL database, strict data verification is performed to ensure that incorrect data cannot be inserted and an error is reported. When used for transactions, the transaction will be rolled back.

STRICT_TRANS_TABLES mode

Strict mode performs strict data verification. Incorrect data cannot be inserted and an error is reported.

1 ANSI mode

In ANSI mode, when we insert data and the column length requirement is not met, the data will be inserted successfully, but the fields that exceed the column length will be truncated and a warning will be reported.

mysql> set @@sql_mode=ANSI;
Query OK, 0 rows affected (0.00 sec)
mysql> create table test(name varchar(4), pass varchar(4));
Query OK, 0 rows affected (0.03 sec)
mysql> insert into test values('aaaaa','aaaaa'),('bbbb','bbbb');
Query OK, 2 rows affected, 2 warnings (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 2
mysql> show warnings;
+---------+------+-------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------+
| Warning | 1265 | Data truncated for column 'name' at row 1 |
| Warning | 1265 | Data truncated for column 'pass' at row 1 |
+---------+------+-------------------------------------------+
2 rows in set (0.00 sec)
mysql> select * from test;
+------+------+
| name | pass |
+------+------+
| aaaa | aaaa |
| bbbb | bbbb |
+------+------+
2 rows in set (0.00 sec)

2 STRICT_TRANS_TABLES mode

In STRICT_TRANS_TABLES mode, when we insert data, MySQL will strictly verify the data. If it is found that the inserted column value does not meet the requirements, it will directly report an error, ensuring that the wrong data cannot be inserted into the database.

mysql> set @@sql_mode=STRICT_TRANS_TABLES;
Query OK, 0 rows affected (0.00 sec)
mysql> create table test(name varchar(4), pass varchar(4));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into test values('aaaaa','aaaaa'),('bbbb','bbbb');
ERROR 1406 (22001): Data too long for column 'name' at row 1
mysql> show errors;
+-------+------+------------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------------+
| Error | 1406 | Data too long for column 'name' at row 1 |
+-------+------+------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from test;
Empty set (0.00 sec)

3 TRADITIONAL mode, first look at whether the results are the same

mysql> set @@sql_mode=TRADITIONAL;
Query OK, 0 rows affected (0.00 sec)
mysql> create table test(name varchar(4), pass varchar(4));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into test values('aaaaa','aaaaa'),('bbbb','bbbb');
ERROR 1406 (22001): Data too long for column 'name' at row 1
mysql> show errors;
+-------+------+------------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------------+
| Error | 1406 | Data too long for column 'name' at row 1 |
+-------+------+------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from test;
Empty set (0.00 sec)

However, you can see what happens after setting

mysql> set @@sql_mode=TRADITIONAL;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@sql_mode\G
*************************** 1. row ***************************
@@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)

In TRADITIONAL mode, all transactional storage engines and non-transactional storage engines are checked. The month and day parts of the date type cannot contain 0, there cannot be a date like 0 (0000-00-00), the data cannot be divided by 0, and grant is prohibited from automatically creating new users.

at last:

set @@ is only set at the sessions level. If you want all to take effect, you still need to set the configuration file

vi /etc/my.cnf

Add the following line under [mysqld]:

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

#NO_ENGINE_SUBSTITUTION will report an error for a non-existent engine. If it is not added, the default innodb will be specified when specifying an unsupported engine

In addition: sql_mode also has a configuration ONLY_FULL_GROUP_BY, which means that when group by is used to select data, only the information in the new group can be viewed.

Operations before changing mode

mysql> select * from employee group by post;
+----+--------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+--------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
| 14 | Zhang Ye | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 |
| 9 | 歪歪| female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 |
| 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |
| 1 | egon | male | 18 | 2017-03-01 | Ambassador of the Old Boy's Office in Shahe | NULL | 7300.33 | 401 | 1 |
+----+--------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
4 rows in set (0.00 sec)

At this time, sql_mode:

mysql> select @@sql_mode;
+----------------------------------------------------------------+
| @@sql_mode |
+----------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------+
1 row in set (0.00 sec)

Modify it and exit and then enter again to take effect

mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0.00 sec)
mysql> select @@sql_mode;
+----------------------------------------------------------------+
| @@sql_mode |
+----------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> exit
Bye

Re-enter

mysql> select @@sql_mode;
+-----------------------------------------------------------------------------------+
| @@sql_mode |
+-----------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------+
1 row in set (0.00 sec)

See the modified results below

mysql> select * from employee group by post; //Only post can be viewed
ERROR 1055 (42000): 't1.employee.id' isn't in GROUP BY
mysql> select post from employee group by post;
+-----------------------------------------+
| post |
+-----------------------------------------+
| operation |
| sale |
| teacher |
| Diplomatic Ambassador of Old Boys Office in Shahe |
+-----------------------------------------+
4 rows in set (0.00 sec)
mysql> select id,post from employee group by post;
ERROR 1055 (42000): 't1.employee.id' isn't in GROUP BY
mysql> select name,post from employee group by post,name; //View according to the selection after group by +------------+-----------------------------------------+
| name | post |
+------------+-----------------------------------------+
| Zhang Ye| operation |
| Cheng Yaojin | operation |
| Cheng Yaotie | operation |
| Cheng Yaotong | operation |
| Cheng Yaoyin | operation |
| Ding Ding | sale |
| Yaya | sale |
| Stars | sale |
| Gege | sale |
| Wai Wai | sale |
| alex | teacher |
| jingliyang | teacher |
| jinxin | teacher |
| liwenzhou | teacher |
| wupeiqi | teacher |
| xiaomage | teacher |
| yuanhao | teacher |
| egon | Diplomatic Ambassador of Old Boys Office in Shahe |
+------------+-----------------------------------------+
18 rows in set (0.00 sec)

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL common functions summary", "MySQL log operation skills", "MySQL transaction operation skills summary", "MySQL stored procedure skills" and "MySQL database lock related skills summary"

I hope this article will be helpful to everyone's MySQL database design.

You may also be interested in:
  • Reasons and solutions for MySQL sql_mode modification not taking effect
  • Detailed explanation of MySQL sql_mode query and setting
  • Detailed explanation of viewing and setting SQL Mode in MySQL
  • Django2 connects to MySQL and model test example analysis
  • Detailed explanation on reasonable settings of MySQL sql_mode
  • MySQL sql_mode analysis and setting explanation
  • The pitfalls and solutions caused by the default value of sql_mode in MySQL 5.7
  • The perfect solution for MySql version problem sql_mode=only_full_group_by
  • Solve the MySQL 5.7.9 version sql_mode=only_full_group_by problem
  • Detailed explanation of SQL Mode usage in MySQL
  • mysql sql_mode="" function description
  • Detailed explanation of the use of MySQL sql_mode

<<:  Linux remote login implementation tutorial analysis

>>:  Native JS to achieve cool paging effect

Recommend

Docker installation of MySQL (8 and 5.7)

This article will introduce how to use Docker to ...

Solve the problem that shutting down Tomcat using shutdown.bat will shut down other Tomcats

The shutdown.bat file has a sentence if not "...

Based on the special characters in the URL escape encoding

Table of contents Special characters in URLs URL ...

Detailed explanation of the role of key in React

Table of contents Question: When the button is cl...

Examples of simple add, delete, modify, and query operations using mysql statements

This article uses examples to describe how to use...

A simple ID generation strategy: Implementation of generating globally unique ID from MySQL table

There are many ways to generate a global ID. Here...

CSS and HTML and front-end technology layer diagram

Front-end technology layer (The picture is a bit e...

Linux server SSH cracking prevention method (recommended)

1. The Linux server configures /etc/hosts.deny to...

JavaScript Document Object Model DOM

Table of contents 1. JavaScript can change all HT...

Detailed explanation of setting static ip network card for CentOS 8 VMware virtual machine to access the Internet

first step: In VMware, click "Edit" - &...

Tutorial on installing Tomcat server under Windows

1 Download and prepare First, we need to download...

Nginx load balancing configuration, automatic switching mode when downtime occurs

Strictly speaking, nginx does not have a health c...

Detailed explanation of the observer mode starting from the component value transfer of Vue

Table of contents Observer Pattern Vue pass value...

JavaScript implements double-ended queue

This article example shares the specific code of ...

Detailed tutorial for installing MySQL 8.0.22 on Redhat 7.3 (binary installation)

Table of contents 1. Download the MySQL installat...