The answer, I guess, is don't use DECIMAL. I understand now the need for a fraction (someone pointed out that Euro calculations must be done in upto 4 decimal places with rounding).
All I can say is that: (a) you multiply the amounts by 10000 (so as to deal with integers), and (b) Split each amount into 3 32-bit numbers, or 2 64-bit numbers, and store each of these in 3 or 2 columns. (c) Use some high-level data type that abstracts large exact numbers (if you're using Java, "java.math.BigInteger" or "java.math.BigDecimal" qualifies, but it's a bit of a pain to construct a BigInteger from an int[] array). Alternatively, if you're using Java throughout, you can start with BigDecimals, and do the following: * To write into the database: - extract the scale, and the unscaled BigInteger, from the BigDecimal. - Convert the BigInteger to a byte array. - store the bigdecimal as a byte array (TINYBLOB, or a hex string in a CHAR(N) column) by calling BigInteger.toByteArray(); * To read it back, read in the byte array and scale, and do new BigDecimal(new BigInteger(bytearray), scale) -----Original Message----- From: Gyulay Gabor [mailto:[EMAIL PROTECTED]] Sent: Thursday, November 15, 2001 11:16 PM To: Shankar Unni Cc: [EMAIL PROTECTED] Subject: Re: About huge numbers On Thu, 15 Nov 2001, Shankar Unni wrote: Thank you for your answers. > Generally, you don't want to store currencies in floating point, anyway, > and it's unfortunate that MySQL implements DECIMAL as floating point rather > than a variable-length BCD (which is exact). Yes. This is strange, because we would like to convert our applications from foxpro. It seems, we have to store these numbers as floating point, because we have to handle all currencies the same way. > However, I tried a sanity check on that number: taking a value of the order > of 100 trillion (say, US dollars - this would be of the order of 50x the > amount of the current US annual budget), converting it to, say, Turkish > Lire (150000x), I get a number that barely exceeds the signed 64-bit limit, > and definitely fits in an unsigned 64-bit number. > > For Italian lire, the number is a hundred times smaller, and should be well > within reach of 64-bit computation. > > Are you absolutely *sure* you can't stick to a 64-bit number? Are you > really dealing with hundreds of quadrillions of US dollars? We would like to prepare the system to handle extreme values without errors. To store the annual money circulation of an huge Hungarian company, we must use 12+2 digits at least 123.456.789.012,12 HUF and the system should convert it to ITL, USD etc. This way it exceeds the system's limit, and we cannot use integers, because we need to store fractions too. If we would multiply by 10000, we would get this: (Right now there is no real demand to count with these numbers, but what if it occurs in the future...) mysql> create table try (money decimal(20,2)); mysql> insert into try values (1234567890123456.12); mysql> select * from try; +---------------------+ | money | +---------------------+ | 1234567890123456.00 | +---------------------+ 1 row in set (0.00 sec) Of course this is the biggest number we plan to handle, and this is the worst case we would like to convert, and this could be only a subconscious fear. Thank you again, Gyulay Gabor ps. I tried this under foxpro, and got the same result. So the problem existed in our previous system, and we haven't even know about it. --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php