近期有同事遇到前端传值到后端写入库数据类型的疑问 查询了解到数据库的严格模式和非严格模式
非严格模式下 写入'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)参考博客: