mysql有三大类数据类型, 分别为数字、日期/时间、字符串, 本文将对数字类型进行介绍。
如下，这是mysql的数字类型相应字段。
| 名称 | 大小 | 范围 | 描述 |
| tinyint | 1 byte | -128~127 | 小整数 |
| smallint | 2 bytes | -32768~32767 | 大整数 |
| mediumint | 3 bytes | -8388608~8388607 | 大整数 |
| int | 4 bytes | -2147483648~2147483647 | 大整数 |
| bigint | 8 bytes | -9.22e18~9.22e18 | 超大整数 |
| float(m, d) | 4 bytes | 取决于参数（m<255且d<30） | 单精度浮点 |
| double(m, d) | 8 bytes | 取决于参数（m<255且d<30） | 双精度浮点 |
| decimal(m, d) | 取决于参数 | 取决于参数（m<65且d<30） | 双精度定点 |
对于精度类型，都接受了两个参数，一个显示宽度指示器和一个小数点指示器，小数位超过则被截取（四舍五入），数值长度超过宽度则变为最大值。
mysql> CREATE TABLE float_test (id float(5,3)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.12 sec)
mysql> insert into float_test values (19.12578);
Query OK, 1 row affected (0.01 sec)
mysql> select * from float_test;
+--------+
| id |
+--------+
| 19.126 |
+--------+
1 row in set (0.00 sec)
mysql> insert into float_test values (13761.23);
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> select * from float_test;
+--------+
| id |
+--------+
| 19.126 |
| 99.999 |
+--------+
2 rows in set (0.00 sec)
Query OK, 0 rows affected (0.12 sec)
mysql> insert into float_test values (19.12578);
Query OK, 1 row affected (0.01 sec)
mysql> select * from float_test;
+--------+
| id |
+--------+
| 19.126 |
+--------+
1 row in set (0.00 sec)
mysql> insert into float_test values (13761.23);
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> select * from float_test;
+--------+
| id |
+--------+
| 19.126 |
| 99.999 |
+--------+
2 rows in set (0.00 sec)
浮点型在数据库中存放的是近似值，而定点类型在数据库中存放的是精确值。
mysql> CREATE TABLE float_test1 (id float(55,3)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.04 sec)
mysql> insert into float_test1 values (1374564654132484132132465789456132141.23);
Query OK, 1 row affected (0.00 sec)
mysql> select * from float_test1;
+-------------------------------------------+
| id |
+-------------------------------------------+
| 1374564727220453400000000000000000000.000 |
+-------------------------------------------+
1 rows in set (0.00 sec)
mysql> CREATE TABLE float_test3 (id decimal(55,3)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)
mysql> insert into float_test3 values (1374564654132484132132465789456132141.23);
Query OK, 1 row affected (0.00 sec)
mysql> select * from float_test3;
+-------------------------------------------+
| id |
+-------------------------------------------+
| 1374564654132484132132465789456132141.230 |
+-------------------------------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.04 sec)
mysql> insert into float_test1 values (1374564654132484132132465789456132141.23);
Query OK, 1 row affected (0.00 sec)
mysql> select * from float_test1;
+-------------------------------------------+
| id |
+-------------------------------------------+
| 1374564727220453400000000000000000000.000 |
+-------------------------------------------+
1 rows in set (0.00 sec)
mysql> CREATE TABLE float_test3 (id decimal(55,3)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)
mysql> insert into float_test3 values (1374564654132484132132465789456132141.23);
Query OK, 1 row affected (0.00 sec)
mysql> select * from float_test3;
+-------------------------------------------+
| id |
+-------------------------------------------+
| 1374564654132484132132465789456132141.230 |
+-------------------------------------------+
1 row in set (0.00 sec)
其他的一些总结点：
1. 取值范围如果加了unsigned，则最大值翻倍，如tinyint unsigned的取值范围为(0~256)。
2. int(m)里的m是表示SELECT查询结果集中的显示宽度，并不影响实际的取值范围，而且如要显示出来宽度必须在创建表时设置zerofill,例如create table test_int (t int(3) zerofill);一般情况下我们不设置int后面的()。
3. bigint的实际区间是-9223372036854775808~9223372036854775807。
Leave a Reply