Hi all I have a database table where I am storing VERY large numbers.... numbers up to 200 digits long. I had this working on my MySQL 4 database but we migrated to MySQL 5.0.27 this week and I am having issues. I have a field set to DOUBLE(200,0). When I try and insert a large number like 99999999999999999999999999999999999999999999999999999999999999999999999999999 I check the data and it is "truncated" to: 9999999999999999635896294965248 I dont know why at the moment..... its not PHP as I have another database running off the same web server that works, its got to do with this new version of MySQL 5. Can anyone help? Its pretty URGENT!!!!
Some more info.... Our SQL MODE is set to '' When I run an update and try update the column with the large number we get the warning: Warning | 1264 | Out of range value adjusted for column 'money' at row 1
The manual states "...A large integer. The signed range is -9223372036854775808 to 9223372036854775807. The unsigned range is 0 to 18446744073709551615." The value you've supplied is clearly out of this range. I've tried using just double, and it inserts. Only problem is that it's not going to help you as it's going to scientific notation so it's rounding. The only way I see you coming right here is to store those gigantic numbers as strings. Try char(200) or varchar(200), and then devise a way to crunch from there if you have to.
I have this working with MySQL 4.0.21 so it does work just for some reason with this new version of MySQL its not. DOUBLE is a a double precision number which can store HUGE numbers in exponential format. When you supply precision, like DOUBLE(200,0) then you can insert or update with big numbers... it does work. The values are rounded because at the db level there stoerd as exponentials but thats ok for what I am using it for,
Then you'll have to revert back to MySQL 4.0.21. What I quoted there was straight out of the MySQL 5 manual. See half-way down this page: http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html (look for BIGINT[(M)] [UNSIGNED] [ZEROFILL]) I'm sure the developers should know what they've decided they'll set storage limits at.
What you might also try is to break your numbers up into a few columns and then crunch from there. Quite a bit more work to do to pull it off, but then again, hard work never killed anybody I know of. Other than my suggestions, I'm sorry but I can't solve your problem otherwise.
Decimals only go to max 55 in MySQL 5. Scolls DOUBLEs are a double precision data type, it has a massive lower and upper limit. I have alro read the manual ... Splitting is not possible... no way. Surely someone does this??? Doesn't anyone store very large numbers?
It's still 64 bit, and therefore still has a threshold of how big a number it can hold. The largest number possible that can be held in any 64 bit number is 4,294,967,295. Or, in other words, this number in binary.... 1111 1111 1111 1111 1111 1111 1111 1111 1111 1111 1111 1111 1111 1111 1111 1111 A 64 bit number is all a 64 bit server can hold. And 64 bit servers have only become available in the past few years. Before that, the largest possible number was half of that. The *ONLY* way to handle a bigger number is to store it as a character, and write a function that substings out smaller pieces of it, converts to number. If you want a bigger number than this right now, you'll have to write a version of MySQL that works on a 128 bit.
Nope, no one does this. it currently isn't possible on any DBMS, not just MySQL. For what possible purpose?
Like I said I have this running now on a MySQL database. I insert very large numbers into a DOUBLE(200,0) field. Im talking numbers with 100 digits... I am doing it now mate and it works. Dont tell me its not possible Ive been doing it for years! Its an online game, that has massive numbers.
I run large scale big number gaming sites. running in decimal (254) will more then handle any area you need.
As an axample I am looking at the data on the MySQL database now.... in a DOUBLE(200,0) field and one of the values is: 99999999994364365105258936570907179930059620363034598443638458156156510823940113338185395101413475876864
In MySQL 5 decimals can only go up to 65 precision. Here Ill show you: Error SQL query: ALTER TABLE `test` ADD `figure6` DECIMAL( 255 ) NOT NULL MySQL said: Documentation #1426 - Too big precision 255 specified for column 'figure6'. Maximum is 65. Code (markup):
Ok, I think I found the answer. it's not actually storing that number as that number. Instead, it's using scientific notation to store the number. When you view the value, it's converting the scientific expression to number in php. Try inserting your number using scientific notation. Allowable values are -1.7976931348623157E+308 to -2.2250738585072014E-308, 0 and 2.2250738585072014E-308 to 1.7976931348623157E+308
You don't have to.... like I said I CURRENTLY do this with MySQL 4.... I update/insert DOUBLE(200,0) fields with MASSIVE numbers and it works FINE. All I want to know is how come its not for MySQL 5, its chopping and rounding all the big numebrs to 9999999999999999635896294965248 I have this working PERFECTLY in MySQL 4 and have for years.
If you are running MySQL 5.0.27 then try it. Create a table with a DOUBLE(200,0) field and insert a new record and use a massive number, then have a look at the data. If you have MySQL 4 do it as well, I bet it works. Keep in mind if you use PHPMYADMIN then you need to make sure the PHP precision var is set to 200.
mysql 4 - mysql 5 run ma bit different in from the above stated. thats why its not working for him. In which case im not to sure. this is reasons i have yet to upgrade past 4.4 mysql. as I run large databases and it is quite fine how it is for me. instead of 255 max string have you tried 254 as i know the mysql setup can be finicky sometimes as well in mysql5