Get the App
SLTechnology News&Howtos  ›  Database  › 

The way of MySQL Management-Notes-the change of MySQL5.7 sql_mode

Shulou Source: shulou.com Published: 2022-06-01 15:24:59 11月16日 Update

Changes in MySQL 5.7sql_mode

1. STRICT_TRANS_TABLES strict mode is enabled by default, which is strict mode, the data will be strictly checked, the error data cannot be inserted into the error report, and things will be rolled back.

2. The default SQL_MODE mode for MySQL5.6 is empty.

The table age field is int, and an error occurs when inserting a character class, but sql_mode is empty, so the data can be inserted.

Figure 1

Root@localhost:mysql3306.sock [sbtest] > desc T1 +-+ | Field | Type | Null | Key | Default | Extra | +-+-+ | id | int (11) | NO | PRI | NULL | name | varchar (2) | YES | | NULL | | age | smallint (6) | YES | | NULL | | +-+-+ |

Figure 2 (sql_mode is set to empty)

Root@localhost:mysql3306.sock [sbtest] > set sql_mode='';Query OK, 0 rows affected, 1 warning (0.02 sec) root@localhost:mysql3306.sock [sbtest] > insert into T1 values (1 row affected, 1 warning (0.04 sec) root@localhost:mysql3306.sock [sbtest] > show warnings +-+ | Level | Code | Message | +- -- + | Warning | 1366 | Incorrect integer value: 'aaa' for column' age' at row 1 | +-- -+ row in set (0.00 sec)

Figure 3 (inserted successfully)

Root@localhost:mysql3306.sock [sbtest] > select * from T1 + | id | name | age | +-+ | 1 | aa | 0 | +-+ row in set (0.00 sec)

Figure 4 (changed to STRICT_TRANS_TABLES, insert failed, transaction rolled back)

Root@localhost:mysql3306.sock [sbtest] > set sql_mode='STRICT_TRANS_TABLES';Query OK, 0 rows affected, 1 warning (0.00 sec) root@localhost:mysql3306.sock [sbtest] > insert into T1 values (2) ERROR 1366 (HY000): Incorrect integer value: 'bbb' for column' age' at row 1root@localhost:mysql3306.sock [sbtest] > select * from T1 where id=2;Empty set (0.04 sec)

Tags: Patterns data success transactions things fields characters errors reports notes management Apple Docker Huawei Linux macOS MariaDB Microsoft MySQL NVidia OPPO Reno MySQL Redmi Huawei Docker NVidia