自分のためのメモ

System

mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| innodb_version          | 5.6.22   |
| protocol_version        | 10       |
| slave_type_conversions  |          |
| version                 | 5.6.22   |
| version_comment         | Homebrew |
| version_compile_machine | x86_64   |
| version_compile_os      | osx10.9  |
+-------------------------+----------+
7 rows in set (0.01 sec)

What happens?

ユーザのスコアテーブルを考えましょう

CREATE TABLE IF NOT EXISTS `scores` (
`id` int(10) unsigned NOT NULL,
  `score` double NOT NULL,
  `user_id` int(11) NOT NULL
);

INSERT INTO `scores` (`id`, `score`, `user_id`) VALUES
(1, 2, 1),
(2, 0.2, 2),
(3, 3, 2),
(4, 0.2, 2);

mysql> SELECT * FROM `scores`;
+----+-------+---------+
| id | score | user_id |
+----+-------+---------+
|  1 |     2 |       1 |
|  2 |   0.2 |       2 |
|  3 |     3 |       2 |
|  4 |   0.2 |       2 |
+----+-------+---------+
4 rows in set (0.00 sec)

合計しましょう

mysql> SELECT SUM(`score`) AS `total_score` FROM `scores`;
+-------------+
| total_score |
+-------------+
|         5.4 |
+-------------+
1 row in set (0.00 sec)

想定どおり

ユーザ毎に合計しましょう

mysql> SELECT SUM(`score`) AS `total_score_per_user`, `user_id` FROM `scores` GROUP BY `user_id`;
+----------------------+---------+
| total_score_per_user | user_id |
+----------------------+---------+
|                    2 |       1 |
|   3.4000000000000004 |       2 |
+----------------------+---------+
2 rows in set (0.00 sec)

！！！

Why?

FLOAT、DOUBLE はおおよその値である（5.0.3 より前では DECIMAL は文字列として保存されるが、計算は FLOAT として計算される）ため

Reference sites

https://dev.mysql.com/doc/refman/5.0/en/problems-with-float.html