10

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.

Community
  • 1
  • 1
R_User
  • 10,682
  • 25
  • 79
  • 120
  • "check for dots" would be an easier way to approach this problem, never less, I'm very interested in reading the solution – GregD May 30 '13 at 01:18

2 Answers2

8

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.

RandomSeed
  • 29,301
  • 6
  • 52
  • 87
  • Why not put this on update or insert trigger so that you can raise the error automatically? – Mark May 30 '13 at 06:02
4

EDIT: I have tested different scenarios to try an get a warning or a least an error for this but with no luck so i suspect that its either a intended behavior or its a mysql bug (although i didn't found a already known bug that describes this) so i think the best/only solution is the one posted by YaK

Use this:

TRADITIONAL

Make MySQL behave like a “traditional” SQL database system. A simple description of this mode is “give an error instead of a warning” when inserting an incorrect value into a column.

set global sql_mode="TRADITIONAL";

For more info read this.

UPDATE: what i purposed above is to get a error instead of a warning because with the warning the data is still inserted thus you will have corrupt data but if you still want to use warnings instead of errors then you have to use the default sql_mode:

set global sql_mode="";

EDIT: be sure that the warnings are enabled :

set global sql_warnings=1;

better yet you can add it in the config file my.cnf so that it will not be overwritten when mysql restarts .

enter image description here

Stephan
  • 8,000
  • 3
  • 36
  • 42
  • I initially thought about this solution, but it issues no warning (let alone an error) with either SQL mode. What version are you using (I have 5.5.31)? – RandomSeed May 21 '13 at 15:16
  • 1
    hmm, are you sure no warning is raised? did you run `SHOW WARNINGS`? – Stephan May 21 '13 at 15:23
  • Yes, please see [this fiddle](http://sqlfiddle.com/#!2/da92e). The first two insersions succeed as described (but only the second one raises a warning on my local host). Notice the third one succeeds with no warning. Only the fourth one raises an error. *sorry for multiple edits, I got mixed up with all those tabs ;)* – RandomSeed May 21 '13 at 15:39
  • you cannot change the variable in fiddle ... i checked , just run `show variables like '%sql_mode%'` – Stephan May 21 '13 at 15:40
  • test on your local mysql and also enable warnings: `set global sql_warnings=1;` – Stephan May 21 '13 at 15:40
  • Yup, tested on my local. Please see my latest link to SQL Fiddle (sorry again for so many edits). You can see that you can change the SQL mode (but you cannot see the cahnge in the right pane, because I suppose the query is issued in a different session). See how Statement #2 succeeds, but Statement #4 fails. My point is: it does raise an error on *plain overflow*, but not on *conversion from FLOAT to INT*. In particular, see that both Statements #1 and Statement #3 succeed. – RandomSeed May 21 '13 at 15:44
  • And by "Statement", I actually mean "INSERT statement". – RandomSeed May 21 '13 at 15:47
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/30346/discussion-between-stephan-and-yak) – Stephan May 21 '13 at 15:57
  • You may want to update your answer as we found out that you did not really cover the initial question. Yet, you provided interesting clues, so it shouldn't be removed nor down-voted IMHO. – RandomSeed May 26 '13 at 08:46
  • @YaK i know now that my answer didn't cover the question since i falsely assume that if a warning was thrown when data truncation occurred it means that a precision loss will be also thrown when a float value is inserted into a int column. Thank you for understanding. – Stephan May 27 '13 at 07:37