How to get a warning when a FLOAT value is inserted into an INT column in MySQL?

Question

I have acreated a Table containing a column of type INT:

CREATE TEMPORARY TABLE `myTab` (`int` INT, `text` TEXT, `float` FLOAT);

Now I try to add a float value into the INT column

INSERT INTO `myTab` (`int`) VALUES (13.34);

I don't get a warning:

SHOW WARNINGS;

Although the column contains only 13 afterwards:

SELECT * FROM `myTab`;

Is it somehow possible to get a warning when a float value is added to a integer column? I'm currently using mysql Ver 14.14 Distrib 5.5.31, for debian-linux-gnu (i686) using readline 6.2

Add: I'd like to avoid adding a "check for dots" for each INT-column (How do I check to see if a value is an integer in MySQL?). I'd prefer a warning when data is lost during casting to INT.

Answer 1

You could use a DECIMAL(n, 0) type instead of INT. Attempting to insert a non-integer number into such a column does raise a warning.

CREATE TEMPORARY TABLE t (id DECIMAL); -- equivalent to the default DECIMAL(10, 0), which covers the range of INT
INSERT INTO t VALUES (0.9); -- raises warning "1265 - Data truncated for column 'id' at row 1"
SELECT * FROM t; -- 0.9 was rounded to 1

I am aware this is only a workaround, and certainly has a negative impact on performance (albeit probably minor). But this is the only approaching solution I have been able to come up with.