MySQL 数据类型之固定浮点类型

• Decimal和numeric数据类型用来存储高精度数据，一般只在对小数进行精确计算时才使用，比如涉及财务数据的时候
• DECIMAL[(M[,D])] [UNSIGNED]
• 在MySQL中，numeric和decimal的含义相同
• Decimal的使用方法举例为decimal(5,2)
• 其中的5代表为精度，表示了可以使用多少位数字
• 其中的2代表小数点后面的小数位数
• 此例子的取值范围为-999.99到999.99
• 当不需要指定小数时，可以使用decimal(M),decimal(M,0)表示
• 当直接使用decimal时，则默认的M为10
• M的最大取值为65，D的最大取值为30，当D为0时可以用来存储比BIGINT更大范围的整数值
• 当指定unsigned，表示不允许负数
• MySQL对decimal字段采用每4个字节存储9个数字的方式，例如decimal(18,9)小数点两边各存储9个数字，一共使用9个字节：小数点前的数字用4个字节，小数点后的数字用4个字节，小数点本身占1个字节

mysql> drop table temp;
Query OK, 0 rows affected (0.15 sec)

mysql> create table temp(id decimal(10,5),id2 numeric(10,5));
Query OK, 0 rows affected (0.04 sec)

mysql> desc temp;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id    | decimal(10,5) | YES  |     | NULL    |       |
| id2   | decimal(10,5) | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into temp values(12345.67891,12345.67891);
Query OK, 1 row affected (0.03 sec)

mysql> select * from temp;
+-------------+-------------+
| id          | id2         |
+-------------+-------------+
| 12345.67891 | 12345.67891 |
+-------------+-------------+
1 row in set (0.00 sec)

mysql> insert into temp values(12345.67890,12345.67890);
Query OK, 1 row affected (0.06 sec)

mysql> insert into temp values(12345.67890,123450.67890);
ERROR 1264 (22003): Out of range value for column 'id2' at row 1
mysql> create table temp2(id decimal,id2 decimal(12,0));
Query OK, 0 rows affected (0.06 sec)

mysql> desc temp2;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id    | decimal(10,0) | YES  |     | NULL    |       |
| id2   | decimal(12,0) | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> insert into temp2 values(100,100);
Query OK, 1 row affected (0.05 sec)

mysql> select * from  temp2;
+------+------+
| id   | id2  |
+------+------+
|  100 |  100 |
+------+------+
1 row in set (0.00 sec)

mysql> create table temp3(id decimal(65,0));
Query OK, 0 rows affected (0.10 sec)

mysql> desc temp3;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id    | decimal(65,0) | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql> select * from temp2;
+------+------+
| id   | id2  |
+------+------+
|  100 |  100 |
+------+------+
1 row in set (0.01 sec)

mysql> insert into temp2 values(100.9,100.9);
Query OK, 1 row affected, 2 warnings (0.12 sec)

mysql> select * from temp2;
+------+------+
| id   | id2  |
+------+------+
|  100 |  100 |
|  101 |  101 |
+------+------+
2 rows in set (0.00 sec)