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

This article introduces you how to achieve a sql_mode pattern in mysql, the content is very detailed, interested friends can refer to, hope to be helpful to you.

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 | +-- +

Three sql_mode modes are supported in mysql5.0 and above

ANSI mode

Loose mode, check the inserted data, adjust or truncate the data type if it does not meet the defined type or length, and report a warning warning.

TRADITIONAL mode

Strict mode, when inserting data into the mysql database, strictly check the data to ensure that the wrong data can not be inserted and report an error error. When used in things, things will be rolled back.

STRICT_TRANS_TABLES mode

Strict mode, strict verification of data, error data can not be inserted, report error error.

1 ANSI mode

In ANSI mode, when we insert data that does not meet the column length requirements, the data is also inserted successfully, but fields that exceed the column length are truncated and a warning warning is 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: 2mysql > 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 check the data. When we find that the value of the inserted column does not meet the requirements, we directly report the error error, which ensures that the wrong data can not 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 1mysql > 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, at first glance, is the result 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 1mysql > 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 take a look at the situation after the setting

Mysql > set @ @ sql_mode=TRADITIONAL Query OK, 0 rows affected (0.00 sec) mysql > select @ @ sql_mode\ gateway * 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_SUBSTITUTION1 row in set (0.00 sec)

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

Finally:

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

Vi / etc/my.cnf

Add the following under [mysqld]:

Sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

# NO_ENGINE_SUBSTITUTION reports an error for an engine that does not exist. If not, specify the default innodb when specifying an unsupported engine.

In addition: sql_mode also has a configuration ONLY_FULL_GROUP_BY, which means that when using group by data, you can only view the information in the new group.

Operation before changing mode

Mysql > select * from employee group by post +- -+-+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | + -+ | 14 | Zhangye | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 | 9 | askew | 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 | diplomatic Ambassador of the Old Boy's Office in Shahe | NULL | 7300.33 | 401 | 1 | +-+-- -+-+ 4 rows in set (0.00 sec)

Sql_mode at this time:

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

After modification, the exit and re-entry will 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 > exitBye

Enter again

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)

Let's take a look at the modified results.

Mysql > select * from employee group by post; / / you can only view postERROR 1055 (42000): 't1.employee.id' isn't in GROUP BYmysql > select post from employee group by post +-- + | post | +-- + | operation | | sale | | teacher | diplomatic Ambassador of the Old Boy's 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 BYmysql > 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 Yaoyin | operation | | Tintin | sale | | Ya | sale | Star | sale | Lattice | sale | | sale | alex | teacher | | jingliyang | teacher | jinxin | teacher | teacher | wupeiqi | teacher | xiaomage | teacher | yuanhao | teacher | egon | Ambassador of the Old Boy's Office in Shahe | +-+ -- + 18 rows in set (0.00 sec) so much for sharing on how to implement a sql_mode pattern in mysql. I hope the above content can be of some help to you and 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.