Description:
If you try to add a non-number value in an integer column, you get a warning. But if you try to insert a non-integer number, it will round it to the nearest integer and insert the value, but WITHOUT any warning.
How to repeat:
mysql> CREATE TABLE `table1` (`number` int(20) DEFAULT NULL) ENGINE=MyISAM;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into table1 values ('19.5454559');
Query OK, 1 row affected (0.00 sec)
mysql> select * from table1;
+-------+
| number |
+-------+
| 20 |
+-------+
1 row in set (0.00 sec)
mysql> insert into table1 values ('a9.5454559');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+-------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------------------------------+
| Warning | 1366 | Incorrect integer value: 'a9.5454559' for column 'number' at row 1 |
+---------+------+-------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from table1;
+-------+
| phone |
+-------+
| 10 |
+-------+
Suggested fix:
Please add a warning stating previous value and rounded value
Mockup:
mysql> insert into table1 values ('9.5454559');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+-------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------------------------------+
| Warning | XXX | Incorrect integer value: '9.5454559' for column 'number' at row 1, rounded to '10' |
+---------+------+-------------------------------------------------------------------+
1 row in set (0.00 sec)