I am using mysql db to save id's. It was working fine.
But now an id like this 10000000754987. Is stored as 1.0000000754987E+14. How I can I fix it.
The field is set to varchar 255 characters limit.
I appreciate any help.
Thanks.
9
-
Please post the insertion code, are you sure its a varchar field? What you see above is the scientific notation, it does not need fixing, it is correct ;)Jacob– Jacob2011-07-12 13:12:07 +00:00Commented Jul 12, 2011 at 13:12
-
Have you debugged your PHP and ensured that you are infact passing '10000000754987' instead of '1.0000000754987E+14' to the sql query?jlb– jlb2011-07-12 13:12:24 +00:00Commented Jul 12, 2011 at 13:12
-
7Why are you using varchar to store these numbers? Why not use bigint? The range is -9223372036854775808 to 9223372036854775807. The unsigned range is 0 to 18446744073709551615 - should be plenty for you. Then it's just a matter of formatting a number.Aleks G– Aleks G2011-07-12 13:12:32 +00:00Commented Jul 12, 2011 at 13:12
-
1I second the BIGINT notion. Use those all the time playaaaaMoarCodePlz– MoarCodePlz2011-07-12 13:13:13 +00:00Commented Jul 12, 2011 at 13:13
-
2If you're on a 32-bit instance of PHP, your 'int's will turn to scientific notation once you cross the threshold.. read more here: php.net/manual/en/language.types.integer.phpjlb– jlb2011-07-12 13:16:57 +00:00Commented Jul 12, 2011 at 13:16
|
Show 4 more comments
2 Answers
You're apparently using PHP to generate that ID.
Since you didn't mention what's exactly happening, I can only assume certain reasons for your database design.
First off, you could store that number as bigint and not varchar. It's an integer you're saving, I see no reason why you'd use varchar and inherently waste more space than needed.
bigint uses 8 bytes to store a number. That means that every number with more than 8 digits stored in varchar field would use more space than a bigint field that can store numbers up to 2^64.
Second, make sure you don't use any number formatting before sending the result of your calculation operation to the db. I copy/pasted the integer you posted (10000000754987) and php doesn't automatically convert it to scientific notation so my guess is that you have something else going on there in the background with that number generation - turn it off if possible and store the number in appropriate field type (bigint). It'd also be useful if you posted additional info about your app and what it does, because error isn't always where people thing it is.
1 Comment
Karolis
I think it's a typo in question, actually the number is longer by one digit. We can see this because of
E+14. If the first number is correct then scientific representation would be E+13. So these two numbers in question are not the same.Your problem is that the numerical IDs have gotten too big for PHP/MySQL to handle.
Change the data type to VARCHAR(36) and CakePHP will now start using UUIDs instead of numerical IDs, you will have to do this on any foreign keys for this table too. This will prevent this problem from happening in the future.
Additionally, as it's varchar, it should work with your current IDs too..
