Of course, all engineering is balancing the trade-offs. The maximum positive value of a BIGINT is 9223372036854775807, which suggests that it can store any 18 digit value accurately, which perhaps solves the problem. BIGINT, like DOUBLE, requires 8 bytes storage, so you are neutral in that regard, but using BIGINT you are adding the CPU overhead of multiplying and dividing by 10^n on every store/fetch (not to mention remembering to apply it everywhere). Also, if the code manipulating this data is C, C++, perl, PHP, Java, lisp or any other commonly used language, it is very highly probable that the code is using the IEEE double-precision format (in place since 1985), so any precision you are worried about losing has already been lost long before it gets to the SQL engine. If the code is manipulating the data in this format, that is likely why all your numbers are coming up with exactly that many digits.
It is possible that the software is using a high-precision library; check with the developers.. if it's off-the shelf software and it doesn't mention super-precision as a feature, it's likely using the good old 8 byte double precision (like 99.99% of all software written in the last 20 years) and a MySQL DOUBLE will store exactly the value it has been given. FYI: the float (IEEE single precision) only guarantees 8 (or 9?) digits of accuracy if I remember my researches from earlier in the day. MySQL DECIMAL type serves as a wrapper for both float and double and will choose the underlaying type depending on the the requested precision. On Wed, 2005-02-02 at 22:54, Galen wrote: > First, I'm storing all my data and I want it stored accurately. I don't > know that I will need quite this much precision in the long run, but > because I am working with highly detailed statistics generated from > hundreds of millions of measurements, I do not want to risk throwing > away information that could prove useful, as this table is only one > step of many. > > Regarding the idea of storing as a bigint and simply multiplying the > value the appropriate amount, that's a possibility. It feels a bit > cumbersome, but would work well enough. > > I'm still not completely clear on float vs double. My goal is decent > efficiency in storage and very good efficiency in sorting and such. It > seems like all numeric values want to be value(16,15) - meaning an > apparent excess of, oh, 12 places to the left of the decimal. It seems > like a waste. > > -Galen > > > On Feb 2, 2005, at 7:02 PM, Michael Dykman wrote: > > > I stand corrected.. I thought I recalled that the IEEE for double > > precision offered 18 digits of accuracy (been years since I looked at > > it) but a little research shows me 15. > > > > - michael dykman > > > > On Wed, 2005-02-02 at 14:02, Roger Baklund wrote: > >> Michael Dykman wrote: > >> [...] > >>> The MySQL implementation also supports this optional > >>> precision specification, but the precision value is used only to > >>> determine storage size. > >> > >> Right. This means you can not have 15 decimals precision using DOUBLE: > >> > >> mysql> use test > >> Database changed > >> mysql> create table dtest(d double(18,15)); > >> Query OK, 0 rows affected (0.01 sec) > >> > >> mysql> insert into dtest values > >> (6.984789027653891),(39.484789039053891); > >> Query OK, 2 rows affected (0.02 sec) > >> Records: 2 Duplicates: 0 Warnings: 0 > >> > >> mysql> select * from dtest; > >> +--------------------+ > >> | d | > >> +--------------------+ > >> | 6.984789027653892 | > >> | 39.484789039053894 | > >> +--------------------+ > >> 2 rows in set (0.00 sec) > >> > >> Last digit is "wrong" in both test rows. Increasing precision does > >> not help: > >> > >> mysql> create table d2test(d double(18,16)); > >> Query OK, 0 rows affected (0.02 sec) > >> > >> mysql> insert into d2test values > >> (6.984789027653891),(39.484789039053891); > >> Query OK, 2 rows affected (0.00 sec) > >> Records: 2 Duplicates: 0 Warnings: 0 > >> > >> mysql> select * from d2test; > >> +---------------------+ > >> | d | > >> +---------------------+ > >> | 6.9847890276538909 | > >> | 39.4847890390538940 | > >> +---------------------+ > >> 2 rows in set (0.00 sec) > >> > >> This is no error, it is the approximate data type at work... it simply > >> can not store the exact value. > > -- > > - michael dykman > > - [EMAIL PROTECTED] > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > > http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > -- - michael dykman - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
