数据库严格模式，

数据库严格模式，

近期有同事遇到前端传值到后端写入库数据类型的疑问  查询了解到数据库的严格模式和非严格模式

 

非严格模式下  写入'a'到int类型的列 会写入为0

严格模式下写入 则报错

 

测试代码   临时设置当前窗体为严格模式  未修改配置文件

mysql> create database day10;
ERROR 1007 (HY000): Can't create database 'day10'; database exists
mysql> use day10;
Database changed
mysql> show tables;
+-----------------+
| Tables_in_day10 |
+-----------------+
| t1              |
+-----------------+
1 row in set (0.00 sec)

mysql>
mysql>
mysql>
mysql>
mysql> create table t10;
ERROR 1113 (42000): A table must have at least 1 column
mysql> create table t10  (id int, age int);
Query OK, 0 rows affected (0.05 sec)

mysql> select * from t10;l
Empty set (0.00 sec)

    -> select * from t10;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'l
select * from t10' at line 1
mysql> select * from t10;
Empty set (0.00 sec)

mysql>
mysql>
mysql> insert int t10 values(1,11);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'int t10 values(1,11)' at line 1
mysql> desc t10;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
| age   | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.02 sec)

mysql> select * from t10;
Empty set (0.00 sec)

mysql> insert into t10 values(1,11);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t10;
+------+------+
| id   | age  |
+------+------+
|    1 |   11 |
+------+------+
1 row in set (0.00 sec)

mysql> insert into t10 values(1,'22');
Query OK, 1 row affected (0.03 sec)

mysql> select * from t10;
+------+------+
| id   | age  |
+------+------+
|    1 |   11 |
|    1 |   22 |
+------+------+
2 rows in set (0.00 sec)

mysql> select @@session.sql_mode;
+------------------------+
| @@session.sql_mode     |
+------------------------+
| NO_ENGINE_SUBSTITUTION |
+------------------------+
1 row in set (0.00 sec)

mysql> insert into t10 values(1,'223');
Query OK, 1 row affected (0.01 sec)

mysql> set @@session.sql_mode='strict_trans_tables';
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql>
mysql> insert into t10 values(1,'223333');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t10 values(1,'a');
ERROR 1366 (HY000): Incorrect integer value: 'a' for column 'age' at row 1
mysql> set @@session.sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t10 values(1,'a');
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> select * from t10;
+------+--------+
| id   | age    |
+------+--------+
|    1 |     11 |
|    1 |     22 |
|    1 |    223 |
|    1 | 223333 |
|    1 |      0 |
+------+--------+
5 rows in set (0.00 sec)

参考博客:

https://www.cnblogs.com/lujs/p/6288806.html