decimal type 제대로 알고 사용하기

mysql에서 실수를 표현할때는 보통 decimal 혹은 double을 사용하게 된다. 이 둘의 차이점이 무엇이고, decimal 을 사용하는 경우에 주의할 점을 기술한다.

Decimal vs. double

  • 고정소수점, 부동소수점의 차이이다. (컴퓨터개론에서 배울수 있었던 것 같은데..)
  • fixed-point type (고정소수점)
    • INT
    • DECIMAL
    • Exact-value numeric literals
      • 정수부과 소수부으로 표시한 실수표현
      • Examples: 1, .2, 3.4, -5, -6.78, +9.10
  • floating-point types (부동소수점)
    • FLOAT
    • DOUBLE
    • Approximate-value numeric literals
      • 가수부와 지수부로 표시한 실수표현
      • Examples: 1.2E3, 1.2E-3, -1.2E3, -1.2E-3
      • 범위를 넘어가면 근사값을 저장함.

정확한 값을 저장하려면 Decimal

  • 정확한 값을 다루고 계산하려면 Decimal타입을 사용한다.
  • 단, precision, scale(M,D)자릿수를 정확하게 정의하도록 한다.

MySQL Float, Double의 accuracy

  • 근사값이다!
  • Float는 4bytes, Double은 8bytes
  • Double에서 대략 15자리까지만 정확하다.(*)
  • A double-precision floating-point number is accurate to approximately 15 decimal places.
    • https://dev.mysql.com/doc/refman/5.7/en/numeric-type-overview.html

Exact-value vs. Approximate-value numeric literals

  • Approximate-value numeric literals을 사용하면 근사값이다.
root@localhost:(none) 22:19:45>select 0.12345678901234567e0, 1.2345678901234567e-1,  9.2345678901234567e-1;
+-----------------------+-----------------------+-----------------------+
| 0.12345678901234567e0 | 1.2345678901234567e-1 | 9.2345678901234567e-1 |
+-----------------------+-----------------------+-----------------------+
|   0.12345678901234566 |   0.12345678901234566 |    0.9234567890123456 |
+-----------------------+-----------------------+-----------------------+
1 row in set (0.00 sec)

-> 16이하 소수점 자리가 의도한 값이 아님을 확인할 수 있다.

Further Test : 17번째 자릿수의 값을 0-9로 변화시켜본다.

  • table column의 데이터type을 decimal로 생성했다고 해서 decimal로 정확한 값을 저장하는 것이 아니다. 지수표현을 사용했다면 double의 accuracy(15자리까지만 정확함)를 가지는 근사치를 저장한다.
create table t1 (
id int AUTO_INCREMENT PRIMARY KEY,
exact_val decimal(24,18),
exponent_val decimal(24,18) not null
);

insert into t1(exact_val, exponent_val) values(0.12345678901234560, 0.12345678901234560e0);
insert into t1(exact_val, exponent_val) values(0.12345678901234561, 0.12345678901234561e0);
insert into t1(exact_val, exponent_val) values(0.12345678901234562, 0.12345678901234562e0);
insert into t1(exact_val, exponent_val) values(0.12345678901234563, 0.12345678901234563e0);
insert into t1(exact_val, exponent_val) values(0.12345678901234564, 0.12345678901234564e0);
insert into t1(exact_val, exponent_val) values(0.12345678901234565, 0.12345678901234565e0);
insert into t1(exact_val, exponent_val) values(0.12345678901234566, 0.12345678901234566e0);
insert into t1(exact_val, exponent_val) values(0.12345678901234567, 0.12345678901234567e0);
insert into t1(exact_val, exponent_val) values(0.12345678901234568, 0.12345678901234568e0);
insert into t1(exact_val, exponent_val) values(0.12345678901234569, 0.12345678901234569e0);

insert into t1(exact_val, exponent_val) values(0.92345678901234560, 0.92345678901234560e0);
insert into t1(exact_val, exponent_val) values(0.92345678901234561, 0.92345678901234561e0);
insert into t1(exact_val, exponent_val) values(0.92345678901234562, 0.92345678901234562e0);
insert into t1(exact_val, exponent_val) values(0.92345678901234563, 0.92345678901234563e0);
insert into t1(exact_val, exponent_val) values(0.92345678901234564, 0.92345678901234564e0);
insert into t1(exact_val, exponent_val) values(0.92345678901234565, 0.92345678901234565e0);
insert into t1(exact_val, exponent_val) values(0.92345678901234566, 0.92345678901234566e0);
insert into t1(exact_val, exponent_val) values(0.92345678901234567, 0.92345678901234567e0);
insert into t1(exact_val, exponent_val) values(0.92345678901234568, 0.92345678901234568e0);
insert into t1(exact_val, exponent_val) values(0.92345678901234569, 0.92345678901234569e0);

root@localhost:test 22:00:31>select id, exact_val, exponent_val, exact_val=exponent_val from t1;
+----+----------------------+----------------------+------------------------+
| id | exact_val            | exponent_val         | exact_val=exponent_val |
+----+----------------------+----------------------+------------------------+
|  1 | 0.123456789012345600 | 0.123456789012345600 |                      1 |
|  2 | 0.123456789012345610 | 0.123456789012345610 |                      1 |
|  3 | 0.123456789012345620 | 0.123456789012345620 |                      1 |
|  4 | 0.123456789012345630 | 0.123456789012345640 |                      0 |
|  5 | 0.123456789012345640 | 0.123456789012345640 |                      1 |
|  6 | 0.123456789012345650 | 0.123456789012345650 |                      1 |
|  7 | 0.123456789012345660 | 0.123456789012345660 |                      1 |
|  8 | 0.123456789012345670 | 0.123456789012345660 |                      0 |
|  9 | 0.123456789012345680 | 0.123456789012345680 |                      1 |
| 10 | 0.123456789012345690 | 0.123456789012345690 |                      1 |
| 11 | 0.923456789012345600 | 0.923456789012345600 |                      1 |
| 12 | 0.923456789012345610 | 0.923456789012345600 |                      0 |
| 13 | 0.923456789012345620 | 0.923456789012345600 |                      0 |
| 14 | 0.923456789012345630 | 0.923456789012345600 |                      0 |
| 15 | 0.923456789012345640 | 0.923456789012345600 |                      0 |
| 16 | 0.923456789012345650 | 0.923456789012345600 |                      0 |
| 17 | 0.923456789012345660 | 0.923456789012345600 |                      0 |
| 18 | 0.923456789012345670 | 0.923456789012345600 |                      0 |
| 19 | 0.923456789012345680 | 0.923456789012345600 |                      0 |
| 20 | 0.923456789012345690 | 0.923456789012345700 |                      0 |
+----+----------------------+----------------------+------------------------+
20 rows in set (0.00 sec)

-> Exact-value numeric로 표현한 값과, 지수표현을 사용한 Approximate-value numeric literals을 decimal(24,18)에 저장해보았다.

  • 지수표현을 사용한 경우에는 의도한 값이 제대로 반영되지 않았다는 것을 확인할 수 있다.
  • id 20의 값을 비교해보자, 0.92345678901234569e0의 값을 입력한 것인데, 15자리까지는 정확하지만, 16,17자리는 입력한 값과 다름.

Decimal 사용시 주의할 점

  • Approximate-value numeric literals 지수표현을 사용한 value를 DECIMAL에 저장한다면? 정확하게 저장되지 않을 수있다.
  • Exact-value numeric literals만을 사용해야한다.