MySQL Strange Insert Error

Question

I am getting strange error while inserting data into mysql table column.

Details:

Create Table Statement:

     CREATE TABLE `product_offer_line` (
  `object_id` int(19) NOT NULL default '0',
  `snapshot_id` int(19) NOT NULL default '0',
  `domain` varchar(255) NOT NULL,
  `description` varchar(255) default NULL,
  `name` varchar(255) NOT NULL,
  `priority` int(10) default NULL,
  `parent_offer_line` varchar(255) default NULL,
  PRIMARY KEY  (`object_id`,`snapshot_id`,`domain`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Note here object_id as int(19), and my insert statement is ("INSERT INTO product_offer_line VALUES('1000000000001','1','NL','Radio and TV','TV','0','NULL')");

Now when I run this insert and than

select * from product_offer_line;

+------------+-------------+--------+-----------------------+------+----------+-------------------+ | object_id | snapshot_id | domain | description | name | priority | parent_offer_line | +------------+-------------+--------+-----------------------+------+----------+-------------------+ | 2147483647 | 1 | NL | Standard radio and TV | CATV | 0 | NULL | +------------+-------------+--------+-----------------------+------+----------+-------------------+

My question here is that even if am entering 1000000000001 why does select statement show 2147483647 ?

I have tried creating object_id as int(64) also but even than I am having same issue.

I am not sure what is going wrong here and so would really appreciate if someone can share some insights on the issue.

Thanks.

Answer 1

1000000000001 is beyond the range of an INT. You need to use BIGINT instead.

The number is brackets relates to how it is displayed, not the storage size.

Answer 2

A mysql int data type can only store a number up to 4294967295 (unsigned). If you try to store a number greater than that, such as 1000000000001, then mysql will store it as the max number possible (2147483647 if the column is signed). Change the column to a bigint data type and you should be good to go.

Answer 3

It's been awhile since I used mysql, but is it because you quoting your number?

Answer 4

use BIGINT rather than INT(64) (ref). If your numbers are guaranteed to be positive, you can also increase your range by declaring the field UNSIGNED.

Answer 5

MySQL is very lenient on values without the valid range for any column. In your case you are exceeding the value range for an INT column. For an INT(64) column this does not mean you can store actual numbers with up to 64 digits, but only that MySQL should display them with that length.

You will have to define the column as a BIGINT or a VARCHAR, depending on whether the quoting in your INSERT was intentional. If for example you have to store numbers with leading zeroes, a VARCHAR might be a better choice.

You can control whether MySQL silently converts invalid values to the closest match it can hold, or fail you with an error. You can control this in some detail with the SQL Server Mode. See this MySQL 5.0 manual page for the possible values and how they affect MySQL's behavior.

For your particular case you should have a look at the STRICT modes which specify what will happen on invalid values in particular.