Shulou(Shulou.com)06/01 Report--

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)

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.