List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:December 21 2002 4:09am
Subject:Re: MySQL Decimal Column Types Question
View as plain text  
At 21:18 -0500 12/20/02, Michael She wrote:
>At 06:17 PM 12/20/2002 -0600, Paul DuBois wrote:
>>At 17:21 -0500 12/20/02, Michael She wrote:
>>>Hi all,
>>>
>>>
>>>What does a 10,2 decimal definition mean.
>>>
>>>Is it 10 Digits in total with 2 decimal places OR 10 integer 
>>>digits with 2 decimal places?
>>
>>The former.
>>
>>>
>>>For a 10,2 decimal column, should this be a valid number: 1234567890.12?
>>
>>It's easy to test:
>>
>>mysql> CREATE TABLE t (d DECIMAL(10,2));
>>Query OK, 0 rows affected (0.01 sec)
>>
>>mysql> INSERT INTO t SET d = 1234567890.12;
>>Query OK, 1 row affected (0.01 sec)
>>
>>mysql> SELECT * FROM t;
>>+--------------+
>>| d            |
>>+--------------+
>>| 999999999.99 |
>>+--------------+
>>1 row in set (0.00 sec)
>>
>>The value's been clipped to the upper end of the range.
>>
>>Your next question should be why that value has 9 digits to the left
>>of the decimal point. :-)
>
>
>How did you guess!  This behaviour is why I asked the question in 
>the first place.  Why does it get clipped for a 10 digit number. 
>Without the decimal value I believe it inserts correctly, but with 
>the 2 decimal places it get clipped...

No, it won't insert correctly, it will still get clipped.

The reason for the 9 digits is that the column requires an extra byte to
store the sign for negative numbers.  For positive numbers, the sign is
not present, so MySQL uses it to extend the upper range.

>--
>Michael She  : michael.she@stripped
>Mobile       : (519) 589-7309
>WWW Homepage : http://www.binaryio.com/

Thread
MySQL Decimal Column Types QuestionMichael She20 Dec
  • Re: MySQL Decimal Column Types QuestionPaul DuBois21 Dec
    • Re: MySQL Decimal Column Types QuestionMichael She21 Dec
      • Re: MySQL Decimal Column Types QuestionJim Dickenson21 Dec
        • Re: MySQL Decimal Column Types QuestionPaul DuBois21 Dec
          • Re: MySQL Decimal Column Types QuestionMichael She21 Dec
      • Re: MySQL Decimal Column Types QuestionPaul DuBois21 Dec
        • Re: MySQL Decimal Column Types QuestionMichael She21 Dec