Fixed-Point Types (Exact Value) - DECIMAL, NUMERIC(定点数类型)
Floating-Point Types (Approximate Value) - FLOAT, DOUBLE(浮点数类型)
一般数据库更多使用定点数类型,存储货币,因为在MySQL内部以字符串形式存放,比浮点数更精确。
适合存储货币精度高的数据。为什么浮点数据不能存储精确数据,因为浮点值在SQL表现的数据和mysql
内部展现的是不一致的。
例如:
DECIMAL(5,2) =>
column range from -999.99
to 999.99
.
FLOAT(7,4) =>column range from -999.9999 to 999.9999.
插入数据都截断,且四舍五入
mysql> create table t3_test(id1 float(5,2),id2 double(5,2),id3 decimal(5,2));
Query OK, 0 rows affected (0.04 sec)
mysql> insert into t3_test values(1.23,1.23,1.23);
Query OK, 1 row affected (0.02 sec)
mysql> select * from t3_test;
+------+------+------+
| id1 | id2 | id3 |
+------+------+------+
| 1.23 | 1.23 | 1.23 |
+------+------+------+
1 row in set (0.00 sec)
mysql> insert into t3_test values(1.236,1.236,1.236);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> show warnings;
+-------+------+------------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------------+
| Note | 1265 | Data truncated for column 'id3' at row 1 |
+-------+------+------------------------------------------+
1 row in set (0.00 sec)
mysql> select * From t3_test;(得到的数据是四舍五入的)
+------+------+------+
| id1 | id2 | id3 |
+------+------+------+
| 1.23 | 1.23 | 1.23 |
| 1.24 | 1.24 | 1.24 |
+------+------+------+
2 rows in set (0.00 sec)
对于精确长度的插入最好不要使用float和double,看看下面的例子就能明白了
mysql> CREATE TABLE t5 (i INT, d1 DOUBLE, d2 DOUBLE);
Query OK, 0 rows affected (0.03 sec)
mysql> INSERT INTO t5 VALUES (1, 101.40, 21.40), (1, -80.00, 0.00), (2, 0.00, 0.00), (2, -13.20, 0.00), (2, 59.60, 46.40), (2, 30.40, 30.40), (3, 37.00, 7.40), (3, -29.60, 0.00), (4, 60.00, 15.40), (4, -10.60, 0.00), (4, -34.00, 0.00), (5, 33.00, 0.00), (5, -25.80, 0.00), (5, 0.00, 7.20), (6, 0.00, 0.00), (6, -51.40, 0.00);
mysql> select * from t5;
+------+-------+------+
| i | d1 | d2 |
+------+-------+------+
| 1 | 101.4 | 21.4 |
| 1 | -80 | 0 |
| 2 | 0 | 0 |
| 2 | -13.2 | 0 |
| 2 | 59.6 | 46.4 |
| 2 | 30.4 | 30.4 |
| 3 | 37 | 7.4 |
| 3 | -29.6 | 0 |
| 4 | 60 | 15.4 |
| 4 | -10.6 | 0 |
| 4 | -34 | 0 |
| 5 | 33 | 0 |
| 5 | -25.8 | 0 |
| 5 | 0 | 7.2 |
| 6 | 0 | 0 |
| 6 | -51.4 | 0 |
+------+-------+------+
16 rows in set (0.00 sec)
mysql> select i ,sum(d1) as a,sum(d2) as b from t5 group by i;
+------+--------------------+------+
| i | a | b |
+------+--------------------+------+
| 1 | 21.400000000000006 | 21.4 |
| 2 | 76.80000000000001 | 76.8 |
| 3 | 7.399999999999999 | 7.4 |
| 4 | 15.399999999999999 | 15.4 |
| 5 | 7.199999999999999 | 7.2 |
| 6 | -51.4 | 0 |
+------+--------------------+------+
6 rows in set (0.03 sec)
可以发现,分组i,汇总每组的值都非常的奇怪
使用decimal试试看。
mysql> CREATE TABLE t6(i INT, d1 decimal(10,2), d2 decimal(10,2) );
Query OK, 0 rows affected (0.03 sec)
mysql> INSERT INTO t5 VALUES (1, 101.40, 21.40), (1, -80.00, 0.00), (2, 0.00, 0.00), (2, -13.20, 0.00), (2, 59.60, 46.40), (2, 30.40, 30.40), (3, 37.00, 7.40), (3, -29.60, 0.00), (4, 60.00, 15.40), (4, -10.60, 0.00), (4, -34.00, 0.00), (5, 33.00, 0.00), (5, -25.80, 0.00), (5, 0.00, 7.20), (6, 0.00, 0.00), (6, -51.40, 0.00);
mysql> select i ,sum(d1) as a,sum(d2) as b from t6 group by i;
+------+--------+-------+
| i | a | b |
+------+--------+-------+
| 1 | 21.40 | 21.40 |
| 2 | 76.80 | 76.80 |
| 3 | 7.40 | 7.40 |
| 4 | 15.40 | 15.40 |
| 5 | 7.20 | 7.20 |
| 6 | -51.40 | 0.00 |
+------+--------+-------+
6 rows in set (0.03 sec)
上下一对比,就知道为啥要用decimal而不用浮点数类型的了。double类型做统计计算,会变得异常,decimal做计算就正常,也怪不得一个称为Exact Value,另外一个称为
Approximate Value.
Bit-Value Type - BIT(位类型)
对于BIT位类型,用户存放字段值,BIT(M)可以用来存放多位二进制数,M范围是1~64.
mysql> create table t3(id bit(1));
Query OK, 0 rows affected (0.07 sec)
mysql> insert into t3 values(1);
Query OK, 1 row affected (0.02 sec)
mysql> select * from t3;(查看不到任何的数据,由于是位类型)
+------+
| id |
+------+
|