Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2011
    Posts
    8

    Unanswered: Comparison Between Floating Point Types

    I inserted different values of pi

    3.14
    3.14159265359
    3.14159
    3.1415

    And I'm not seeing the difference in how the different floating point types handle the same values.
    Code:
    mysql> select * from types;
    +---------+---------+---------+----------+
    | flo     | dub     | deci    | noomeric |
    +---------+---------+---------+----------+
    | 3.14000 | 3.14000 | 3.14000 |  3.14000 |
    | 3.14159 | 3.14159 | 3.14159 |  3.14159 |
    | 3.14159 | 3.14159 | 3.14159 |  3.14159 |
    | 3.14150 | 3.14150 | 3.14150 |  3.14150 |
    | 3.14150 | 3.14150 | 3.14150 |  3.14150 |
    +---------+---------+---------+----------+
    5 rows in set (0.00 sec)
    
    mysql> describe types;
    +----------+---------------+------+-----+---------+-------+
    | Field    | Type          | Null | Key | Default | Extra |
    +----------+---------------+------+-----+---------+-------+
    | flo      | float(10,5)   | YES  |     | NULL    |       |
    | dub      | double(10,5)  | YES  |     | NULL    |       |
    | deci     | decimal(10,5) | YES  |     | NULL    |       |
    | noomeric | decimal(10,5) | YES  |     | NULL    |       |
    +----------+---------------+------+-----+---------+-------+
    4 rows in set (0.00 sec)
    mysql>

    The only thing I can see here is when creating the table, the field with numeric type used decimal shown by the describe table command.

    Would somebody give me an example that shows the differences between FLOAT, DECIMAL and DOUBLE?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by sakamal View Post
    Would somebody give me an example that shows the differences between FLOAT, DECIMAL and DOUBLE?
    FLOAT and DOUBLE are essentially the same thing

    they are meant for really really small values, like the distance in light years between san francisco and oakland, or really really large values, like the number of molecules in a glass of water

    the main point about them is that they are approximate --
    Quote Originally Posted by da manual
    Because floating-point values are approximate and not stored as exact values, attempts to treat them as exact in comparisons may lead to problems. They are also subject to platform or implementation dependencies.
    DECIMAL allows for an exact representation

    the reason your DECIMAL column for pi didn't work too well is because you allowed for only 5 decimal places, but tried to feed it 11 decimal places

    if you want to store the value of pi accurate to 11 decimal places, you would use DECIMAL(12,11)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Nov 2012
    Posts
    2
    I've already read it. I'm asking for an example that shows the differences. I've tried to create some myself but couldn't get results that showed the difference.

    https://dev.mysql.com/doc/refman/5.6...int-types.html

    MySQL performs rounding when storing values, so if you insert 999.00009 into a FLOAT(7,4) column, the approximate result is 999.0001.
    Both types are doing that. Then can you give me an example that shows the difference?

    Code:
    mysql> CREATE TABLE types(flo FLOAT(7,4),deci DECIMAL(7,4));
    Query OK, 0 rows affected (0.14 sec)
    
    mysql> insert into types values (999.00009,999.00009);
    Query OK, 1 row affected, 1 warning (0.04 sec)
    
    mysql> select * from types;
    +----------+----------+
    | flo      | deci     |
    +----------+----------+
    | 999.0001 | 999.0001 |
    +----------+----------+
    1 row in set (0.00 sec)
    
    mysql>
    Last edited by BullShark; 11-25-12 at 17:23.

  5. #5
    Join Date
    Nov 2012
    Posts
    2
    Quote Originally Posted by r937 View Post
    FLOAT and DOUBLE are essentially the same thing
    I understand the difference between those 2. They are the same except differ in storage size 4 bytes / 8 bytes.

    I'm asking for some example(s) that show the difference between FLOAT and DECIMAL.

    DECIMAL allows for an exact representation
    Please give me an example that shows a value being treated differently when stored as DECIMAL opposed to the same value being stored and used as FLOAT.

    the reason your DECIMAL column for pi didn't work too well is because you allowed for only 5 decimal places, but tried to feed it 11 decimal places

    if you want to store the value of pi accurate to 11 decimal places, you would use DECIMAL(12,11)
    Again, I'm looking for an example to show the differences, so I feed it several values. I'm not worried about showing all the decimal places, only the difference between the two types.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by BullShark View Post
    Please give me an example that shows a value being treated differently when stored as DECIMAL opposed to the same value being stored and used as FLOAT.
    sure, no prob...
    Code:
    CREATE TABLE test_decimal_float
    ( d DECIMAL(12,11)
    , f FLOAT
    );
    
    INSERT INTO test_decimal_float VALUES
      ( 3.937937937 , 3.937937937 )
     ,( 3.888888888 , 3.888888888 )
     ,( 3.777777777 , 3.777777777 )
     ,( 3.666666666 , 3.666666666 )
     ,( 3.555555555 , 3.555555555 )
     ,( 3.444444444 , 3.444444444 )
     ,( 3.333333333 , 3.333333333 )
     ,( 3.222222222 , 3.222222222 ) 
     ,( 3.111111111 , 3.111111111 ) 
     ;
    
    SELECT * FROM test_decimal_float
      WHERE f = d
    before you run the SELECT, take a guess as to which rows will evaluate the WHERE clause as true
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •