1

When I run this:

INSERT INTO `orders` (`order_id`) VALUES ('50261111111s11');

The following gets inserted:

order_id => 2147483647

The field order_id is an INT type, so I am assuming that is the root cause. What I do not get is why it converted it to that number. I would normally expect it to insert a 0 since I used an alpha character in there. What gives?

user2864740
  • 60,010
  • 15
  • 145
  • 220
AXM
  • 806
  • 1
  • 9
  • 24
  • 4
    just answered here http://stackoverflow.com/questions/23550231/long-integer-is-transformed-when-inserted-in-shorter-column-not-truncated-why/23550275#23550275 so not adding a duplicate one. – Abhik Chakraborty May 08 '14 at 21:06
  • That number looks like 2G to me... – Floris May 08 '14 at 21:08
  • your column `order_id` is not set as a `varchar` column, but rather an `int` column, or some other numerical kind. *Or* you need to use `bigint`. – khaverim May 08 '14 at 21:22
  • 1
    @AbhikChakraborty - thank you for not adding to the unnecessary duplication of information. I wish other people were equally disciplined. – Floris May 08 '14 at 21:31
  • Apologizes! Apparently someone beat me to this question by 2 hours! – AXM May 09 '14 at 17:29

2 Answers2

3

First, the string '50261111111s11' is implicitly coerced to 502611111111.

Then it is capped at 21474836472, which is the maximum value for an INT field in MySQL.

(Unless an Order ID is a constrained integer, such an auto-increment key, maybe it should be a CHAR/VARCHAR column?)

1 See the INSERT statement:

This might involve [an implicit] type conversion if the type of the expression does not match the type of the column, and conversion of a given value can result in different inserted values depending on the data type.

(MySQL might implement a direct conversion/truncation to INT, but consider the case where the value supplied was CONVERT("50261111111s11", SIGNED INTEGER) - the result is 50261111111, not 0.)

2 From 11.2.6 Out-of-Range and Overflow Handling:

  • If strict SQL mode is enabled, MySQL rejects the out-of-range value with an error, and the insert fails, in accordance with the SQL standard.

  • If no restrictive modes are enabled, MySQL clips the value to the appropriate endpoint of the range and stores the resulting value instead.

Community
  • 1
  • 1
user2864740
  • 60,010
  • 15
  • 145
  • 220
-1

you are inserting a string ... VALUES ('50261111111**s**11') in an int field, you should change order_id to varchar

alou
  • 1,432
  • 13
  • 16