mysql数据库-----数字类型

在数据库中，经常需要存储一些数字，如商品的库存、销量、价格等，适合用数字类型来保存。数字类型包括整数类型、浮点数类型、定点数类型，BIT(位)类型等，下面分别进行讲解。

1．整数类型

MySQL中的整数类型用于保存整数。根据取值范围的不同，整数类型可分为5种，分别是TINYINT、SMALLINT. MEDIUMINT，INT和BIGINT。不同整数类型所对应的字节大小和取值范围如表3-1所示。
表3-1 MySQL 整数类型

数据类型	字节数	无符号数的取值范围	有符号数的取值范围
TINYINT	1	0～255	-128～127
SMALLINT	2	0～65 535	-32 768～32 767
MEDIUMINT	3	0～16 777 215	-8 388 608～8 388 607
INT	4	0～4 294 967 295	-2 147 483 648～2 147 483 647
BIGINT	8	0～18 446 744 073 709 551 615	-9 223 372 036 854 775 808～ 9 223 372 036 854 775 807

从表3-1中可以看出，不同整数类型所占用的字节数和取值范围都是不同的。其中，占用字节数最小的是TINYINT，占用字节数最大的是BIGINT。不同整数类型的取值范围可以根据字节数计算出来，例如，TINYINT类型的整数占用1字节，1字节是8位，那么，TINYINT类型无符号数的最大值就是28-1(即255)，有符号数的最大值就是27-1(即127)。同理，可以算出其他不同整数类型的取值范围。
需要注意的是，若使用无符号数据类型，需要在数据类型右边加上UNSIGNED关键字来修饰，例如，INT UNSIGNED表示无符号INT类型。
为了让读者更好理解，下面通过案例的方式演示整数类型的使用及注意事项。
(1)创建my_int表，选取INT和TINYINT两种类型测试。具体SQL语句如下。

mysql> USE mydb;
mysql> CREATE TABLE my_int (
    ->   int_1 INT,
    ->   int_2 INT UNSIGNED,
    ->   int_3 TINYINT,
    ->   int_4 TINYINT UNSIGNED
    -> );

上述SQL语句中，int_1和int_3是有符号类型，int_2和int_4是无符号类型。
(2)插入记录进行测试。当数值在合法的取值范围内时，可以正确插入，反之则无法插入，提示错误信息。具体SQL语句及执行结果如下。

① 插入成功测试

mysql> INSERT INTO my_int VALUES(1000, 1000, 100, 100);
Query OK, 1 row affected (0.00 sec)

② 插入失败测试

mysql> INSERT INTO my_int VALUES(1000, -1000, 100, 100);
ERROR 1264 (22003): Out of range value for column 'int_2' at row 1

从上述结果可以看出，由于“一1000”超出了无符号INT类型的取值范围，数据插入失败，MySQL显示了错误信息，提示int__2字段超出取值范围。
(3)查看my_ int表的结构，具体SQL语句及执行结果如下。

mysql> DESC my_int;
+-------+------------------------+------+-----+---------+-------+
| Field | Type                   | Null | Key | Default | Extra |
+-------+------------------------+------+-----+---------+-------+
| int_1 | int(11)                | YES  |     | NULL    |       |
| int_2 | int(10) unsigned       | YES  |     | NULL    |       |
| int_3 | tinyint(4)             | YES  |     | NULL    |       |
| int_4 | tinyint(3) unsigned    | YES  |     | NULL    |       |
+-------+------------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

在执行结果中，数据类型右边使用小括号数字标注了显示宽度。默认情况下，显示宽度是取值范围所能表示的最大宽度。对于有符号类型，符号也占用一个宽度。例如，255的显示宽度为3，-128的显示宽度为4。需要注意的是，显示宽度与取值范围无关，若数值的位数小于显示宽度，会填充空格，若大于显示宽度，则不影响显示结果。
(4)为字段设置零填充(ZEROFILL)时，若数值宽度小于显示宽度，会在左侧填充0。
创建my__int2表，为字段设置零填充和宽度，具体SQL语句执行结果如下。

mysql> CREATE TABLE my_int2 (
    ->   int_1 INT(3) ZEROFILL,
    ->   int_2 TINYINT(6) ZEROFILL
    -> );
Query OK, 0 rows affected (0.01 sec)
mysql> DESC my_int2;
+-------+------------------------------+------+-----+---------+-------+
| Field | Type                         | Null | Key | Default | Extra |
+-------+------------------------------+------+-----+---------+-------+
| int_1 | int(3) unsigned zerofill     | YES  |     | NULL    |       |
| int_2 | tinyint(6) unsigned zerofill | YES  |     | NULL    |       |
+-------+------------------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

在上述结果中，设置零填充后，字段自动设为无符号类型，这是因为负数不能使用零填充。
(5)插入数据测试，具体SQL语句及执行结果如下。

mysql> INSERT INTO my_int2 VALUES(1234, 2);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM my_int2;
+-------+--------+
| int_1 | int_2  |
+-------+--------+
|  1234 | 000002 |
+-------+--------+
1 row in set (0.00 sec)

从上述结果可知，当数值超过显示宽度时，不填充零;当数值未达到显示宽度时，在左侧填充零。
提示:
(1)在选择数据类型时，若一个数据将来可能参与数学计算，推荐使用整数、浮点数或定点数类型;若只用来显示，则推荐使用宇符串类型。例如，商品库存可能需要增加、减少、求和等，所以保存为整数类型;用户的身份证、电话号码一般不需要计算，可以保存为字符串型。
(2)表的主键推荐使用整数类型，与字符串相比，整数类型的处理效率更高，查询速度更快。
(3)当插入的值的数据类型与字段的数据类型不一致，或使用ALTER TABLE修改字段的数据类型时，MySQL会尝试尽可能将现有的值转换为新类型。例如，字符串"123’、’-123’、'1.23’与数字123、-123、1.23可以互相转换；1.5转换为整数时，会被四舍五入，结果为2。

2．浮点数类型

在MySQL中，存储的小数都是使用浮点数或定点数来表示的。浮点数的类型有两种，分别是单精度浮点数类型(FLOAT)和双精度浮点数类型(DOUBLE)，对应的字节大小及其取值范围如表3-2所示。
表3-2 MySQL浮点数类型
数据类型 字节数 负数的取值范围 非负数的取值范围
FLOAT 4 -3.402 823 466E+38 ~
-1.175 494 351E-38 0 和
1.175 494 351E-38 ~3.402 823 466E+38
DOUBLE 8 -1.797 693 134 862 315 7E+308 ~
-2.225 073 858 507 201 4E-308 0 和
2.225 073 858 507 201 4E-308 ~1.797 693 134 862 315 7E+308
表3-2中列举的取值范围是理论上的极限值，但根据不同的硬件或操作系统，实际范围可能会小。另外，当浮点数类型使用UNSIGNED修饰为无符号时，取值范围将不包含负数。
需要注意的是，浮点数类型虽然取值范围很大，但是精度并不高。FLOAT的精度为6位或7位，DOUBLE的精度大约为15位。如果超出精度，可能会导致给定的数值与实际保存的数值不一致，发生精度损失。
为了让读者更好地理解，下面通过案例的方式演示浮点数类型的使用及注意事项。具体SQL语句及执行结果如下。
① 创建表，选取FLOAT类型进行测试

mysql> CREATE TABLE my_float (f1 FLOAT, f2 FLOAT);
Query OK, 0 rows affected (0.01 sec)

② 插入未超出精度的数字

mysql> INSERT INTO my_float VALUES(111111, 1.11111);
Query OK, 1 row affected (0.00 sec)

③ 插入超出精度的数字

mysql> INSERT INTO my_float VALUES(1111111, 1.111111);
Query OK, 1 row affected (0.00 sec)

④ 插入7位数，第7位四舍五入

mysql> INSERT INTO my_float VALUES(1111114, 1111115);
Query OK, 1 row affected (0.00 sec)

⑤ 插入8位数，第7位四舍五入，第8位忽略

mysql> INSERT INTO my_float VALUES(11111149, 11111159);
Query OK, 1 row affected (0.00 sec)

⑥ 查询结果

mysql> SELECT * FROM my_float;
+----------+----------+
| f1       | f2       |
+----------+----------+
|   111111 |  1.11111 |
|  1111110 |  1.11111 |
|  1111110 |  1111120 |
| 11111100 | 11111200 |
+----------+----------+
4 rows in set (0.00 sec)

从上述结果可以看出，当一个数字的整数部分和小数部分加起来达到7位时，第7位就会四舍五人。

3．定点数类型

定点数类型(DECIMAL)通过DECIMAL(M,D)设置位数和精度，其中，M表示数字总位数(不包括“.”和“-”)，最大值为65，默认值为10；D表示小数点后的位数，最大值为30，默认值为0。例如，DECIMAL(5,2)表示的取值范围是-999.99~999.99。系统会自动根据存储的数据来分配存储空间。若不允许保存负数，可通过UNSIGNED修饰。
为了让读者更好地理解，下面通过案例的方式演示定点数类型的使用及注意事项。具体SQL语句及执行结果如下。。
① 创建表，选取DECIMAL类型进行测试

mysql> CREATE TABLE my_decimal (d1 DECIMAL(4,2), d2 DECIMAL(4,2));
Query OK, 0 rows affected (0.01 sec)

② 插入的小数部分超出范围时，会四舍五入并出现警告

mysql> INSERT INTO my_decimal VALUES(1.234, 1.235);
Query OK, 1 row affected, 2 warnings (0.00 sec)
mysql> SHOW WARNINGS;
+-------+------+-----------------------------------------+
| Level | Code | Message                                 |
+-------+------+-----------------------------------------+
| Note  | 1265 | Data truncated for column 'd1' at row 1 |
| Note  | 1265 | Data truncated for column 'd2' at row 1 |
+-------+------+-----------------------------------------+
2 rows in set (0.00 sec)

③ 插入的小数部分四舍五入导致整数部分进位时，插入失败

mysql> INSERT INTO my_decimal VALUES(99.99, 99.999);
ERROR 1264 (22003): Out of range value for column 'd2' at row 1

④ 查询结果

mysql> SELECT * FROM my_decimal;
+------+------+
| d1   | d2   |
+------+------+
| 1.23 | 1.24 |
+------+------+
1 row in set (0.00 sec)

从上述结果可以看出，若小数部分超出范围，会进行四舍五入，并出现Data truncated(数据截断)警告;若整数部分超出范围，数据会插入失败，提示Out of range value(超出取值范围)错误。
提示:浮点数类型也可以设置位数和精度，如float(8,2)，但仍有可能损失精度。在实际使用时应避免使用浮点数类型，以免出现不能人为控制的问题。因此，对于小数类型的设置，推荐使用定点数类型并设置合理的范围可以使计算更为准确。

4．BIT类型

BIT(位)类型用于存储二进制数据，语法为BIT(M)，M表示位数，范围为1~64。
下面以保存字符A为例，A的ASCII码为十进制65，对应的二进制为1000001，总共有7位，需要至少7位来保存。示例SQL语句如下。
① 获取字符“A”的ASCII码，结果为65

mysql> SELECT ASCII('A');

② 将十进制数转换为二进制，并计算长度，结果分别为1000001、7

mysql> SELECT BIN(65), LENGTH(BIN(65));

③ 创建表，然后插入数据

mysql> CREATE TABLE my_bit (b BIT(7));
mysql> INSERT INTO my_bit VALUES(65);

④ 查询数据，查询结果为“A”

mysql> SELECT * FROM my_bit WHERE b = 65;

⑤ 查询数据并转为二进制数字显示，查询结果为“1000001”

mysql> SELECT BIN(b) FROM my_bit;

从上述示例可以看出，利用MySQL中的ASCII().BIN(). LENGTH()函数可以方便地查询ASCII码、二进制值和数字长度。BIT类型字段在数字插入时转换为二进制保存，但在利用SELECT查询时，会自动转换为对应的字符显示。