×

热门关键词

FalconChen
2021-03-19 19:30
动弹
#daily tips# SQL严格模式和非严格模式下对decimal溢出的处理。
sql 代码片断 
mysql> desc test;
+--------+--------------+------+-----+---------+----------------+
| Field  | Type         | Null | Key | Default | Extra          |
+--------+--------------+------+-----+---------+----------------+
| id     | int(11)      | NO   | PRI | NULL    | auto_increment |
| number | decimal(4,2) | NO   |     | NULL    |                |
+--------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

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

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

mysql> insert into test(id,number) value(NULL,100);
ERROR 1264 (22003): Out of range value for column 'number' at row 1

mysql> SET SESSION sql_mode='NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test(id,number) value(NULL,100);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from test;
+----+--------+
| id | number |
+----+--------+
|  1 |  99.99 |
+----+--------+
1 row in set (0.00 sec)
在OSC查看
0

评论 ( 1 )

  • FalconChen 弹主
    2021-03-19 19:46
    只是MySQL，不知道其他的关系型数据库有没有这种设定，开发时不能依赖系统的sqlmode，否则可能导致不同机器表现不一样。
    0