数据类型-数值类型
目录
数值类型类型主要分为整型、浮点型。
一、整数类型
整数类型:tinyint, smallint, mediumint, int, bigint
最常用的是:tinyint,int,bigint
作用:存储年龄,等级,id,各种号码等
tinyint
tinyint[(m)] [unsigned] [zerofill] 小整数,数据类型用于保存一些范围的整数。
数值范围:
有符号:-128~127
无符号:0~255
PS:Mysql中无布尔值,使用tinyint(1) 构造。
验证:
验证有符号的 tinyint:
MariaDB [db1]> create table t1(id tinyint); # 创建一个t1表,设置id字段,类型为tinyint,默认为有符号的,即数字前有正负号
Query OK, 0 rows affected (0.01 sec)
MariaDB [db1]> desc t1; # 查看t1表结构
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id | tinyint(4) | YES | | NULL | | # 默认宽度为4,因为字段是有符号的
+-------+------------+------+-----+---------+-------+
1 row in set (0.01 sec)
MariaDB [db1]> insert into t1 values(-129),(-128),(127),(128); # 插入4条记录 -129, -128, 127, 128
Query OK, 4 rows affected, 2 warnings (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 2
MariaDB [db1]> select * from t1; # 查看存入的记录值
+------+
| id |
+------+
| -128 | # -129存为了-128,因为字段是有符号的,所以最小值为-128
| -128 | # -128依然是-128
| 127 | # 127依然是127
| 127 | # 128存为了127,因为是字段有符号的,所以最大值为127
+------+
4 rows in set (0.00 sec)
验证无符号的 tinyint:
MariaDB [db1]> create table t2(id tinyint unsigned); # 创建一个t2表,设置id字段,unsigned表示设置为无符号的
Query OK, 0 rows affected (0.01 sec)
MariaDB [db1]> desc t2; # 查看t2表结构
+-------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| id | tinyint(3) unsigned | YES | | NULL | | # 宽度为3,因为字段是无符号的
+-------+---------------------+------+-----+---------+-------+
1 row in set (0.00 sec)
MariaDB [db1]> insert into t2 values(-1),(0),(255),(266); # 插入4条记录 -1, 0, 255, 256
Query OK, 4 rows affected, 2 warnings (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 2
MariaDB [db1]> select * from t2; # 查看存入的记录值
+------+
| id |
+------+
| 0 | # -1存为了0,因为字段是无符号的,所以最小值为0
| 0 | # 0依然是0
| 255 | # 255依然是255
| 255 | # 256存为了255,因为字段是无符号的,所以最大值为255
+------+
4 rows in set (0.00 sec)
int
int[(m)][unsigned][zerofill] 整数,数据用于保存一些范围的整数。
数值范围:
有符号:-2147483648 ~ 2147483647
无符号:0 ~ 4294967295
验证:
验证有符号的 int:
MariaDB [db1]> create table t3(id int); # 创建t3表,设置id字段,类型为int,默认为有符号的整数
Query OK, 0 rows affected (0.01 sec)
MariaDB [db1]> desc t3; # 查看t3表结构
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | | # 默认宽度为11,因为是有符号的
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
MariaDB [db1]> insert into t3 values(-2147483649),(-2147483648),(2147483647),(2147483648); # 插入4条记录
Query OK, 4 rows affected, 2 warnings (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 2
MariaDB [db1]> select * from t3; # 查看存入的记录值
+-------------+
| id |
+-------------+
| -2147483648 | # -2147483649存为了-2147483648,因为字段是有符号的,所以最小值为-2147483648
| -2147483648 | # -2147483648依然是-2147483648
| 2147483647 | # 2147483647依然是2147483647
| 2147483647 | # 2147483648存为了2147483647,因为字段是有符号的,所以最大值为 2147483647
+-------------+
4 rows in set (0.00 sec)
验证无符号的 int:
MariaDB [db1]> create table t4(id int unsigned); # 创建t4表,设置id字段,类型为int,unsigned设置为无符号的
Query OK, 0 rows affected (0.01 sec)
MariaDB [db1]> desc t4; # 查看t4表结构
+-------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id | int(10) unsigned | YES | | NULL | | # 宽度为10,因为是无符号的
+-------+------------------+------+-----+---------+-------+
1 row in set (0.00 sec)
MariaDB [db1]> insert into t4 values(-1),(0),(4294967295),(4294967296); # 插入4条记录
Query OK, 4 rows affected, 2 warnings (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 2
MariaDB [db1]> select * from t4; # 查看存入的记录值
+------------+
| id |
+------------+
| 0 | # -1存为了0,因为字段是无符号的,所以最小值为0
| 0 | # 0依然是0
| 4294967295 | # 4294967295依然是4294967295
| 4294967295 | # 4294967296存为了4294967295,因为字段是无符号的,所以最大值为4294967295
+------------+
4 rows in set (0.00 sec)
bigint
bigint[(m)][unsigned][zerofill] 大整数,数据类型用于保存一些范围的整数。
数值范围:
有符号:-9223372036854775808 ~ 9223372036854775807
无符号:0 ~ 18446744073709551615
验证:
验证有符号的 bigint:
MariaDB [db1]> create table t5(id bigint); # 创建t5表,设置id字段,类型为bigint,默认为有符号的
Query OK, 0 rows affected (0.01 sec)
MariaDB [db1]> desc t5; # 查看t5表结构
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id | bigint(20) | YES | | NULL | | 默认宽度为20
+-------+------------+------+-----+---------+-------+
1 row in set (0.00 sec)
MariaDB [db1]> insert into t5 values(-9223372036854775809),(-9223372036854775808),(9223372036854775807),(9223372036854775808); # 插入4条记录
Query OK, 4 rows affected, 2 warnings (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 2
MariaDB [db1]> select * from t5; # 查看存入的记录值
+----------------------+
| id |
+----------------------+
| -9223372036854775808 | # -9223372036854775809存为了9223372036854775808,因为字段是有符号的,所以最小值为-9223372036854775808
| -9223372036854775808 | # -9223372036854775808依然是9223372036854775808
| 9223372036854775807 | # 9223372036854775807依然是9223372036854775807
| 9223372036854775807 | # 9223372036854775808存为了9223372036854775807,因为字段是有符号的,所以最大值为9223372036854775807
+----------------------+
4 rows in set (0.00 sec)
验证无符号的 bigint:
MariaDB [db1]> create table t6(id bigint unsigned); # 创建t6表,设置id字段,类型为bigint,unsigned设置为无符号的
Query OK, 0 rows affected (0.01 sec)
MariaDB [db1]> desc t6; # 查看t6表结构
+-------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| id | bigint(20) unsigned | YES | | NULL | | # 宽度和有符号的宽度一样,都是20
+-------+---------------------+------+-----+---------+-------+
1 row in set (0.00 sec)
MariaDB [db1]> insert into t6 values(-1),(0),(18446744073709551615),(18446744073709551616); # 插入4条记录
Query OK, 4 rows affected, 2 warnings (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 2
MariaDB [db1]> select * from t6; # 查看存入的记录值
+----------------------+
| id |
+----------------------+
| 0 | # -1存为了0,因为字段是无符号的,所以最小值为0
| 0 | # 0依然是0
| 18446744073709551615 | # 18446744073709551615依然是18446744073709551615
| 18446744073709551615 | # 18446744073709551616存为了18446744073709551615,因为字段是无符号的,所以最大值为18446744073709551615
+----------------------+
4 rows in set (0.00 sec)
用 zerofill 测试整数类型的显示宽度
MariaDB [db1]> create table t7(id int(3) zerofill); # 创建t7表,设置id字段,宽度为3,zerofill指当没有达到设定的宽度的时候,前面用0来填充
Query OK, 0 rows affected (0.01 sec)
MariaDB [db1]> desc t7; # 查看t7表结构
+-------+--------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------------------+------+-----+---------+-------+
| id | int(3) unsigned zerofill | YES | | NULL | |
+-------+--------------------------+------+-----+---------+-------+
1 row in set (0.00 sec)
MariaDB [db1]> insert into t7 values(1),(11),(111),(1111); # 插入4条记录
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
MariaDB [db1]> select * from t7; # 查看存入记录值
+------+
| id |
+------+
| 001 | # 插入值为1,显示宽度不足3,前面填充2个0
| 011 | # 插入值为11,显示宽度不足3,前面填充1个0
| 111 | # 插入值为111,显示宽度刚好为3,不做任何改变
| 1111 | # 插入值为1111,显示宽度大于3,不做任何改变,依然可以存入
+------+
4 rows in set (0.00 sec)
注意:
为整数类型字段指定类型宽度,仅仅只是指定查询结果的显示宽度,与存储范围无关,存储范围如下:
其实我们完全没有必要为整数类型指定显示宽度,使用默认的就可以了,默认的显示宽度,都是在最大值的基础上加1。
有符号和无符号的最大数字需要的显示宽度均为10,而针对有符号的最小值则需要11位才能显示完全,所以int类型默认的显示宽度为11是非常合理的。
int 的存储宽度是4个Bytes,即32个bit,即2**32
无符号最大值为:4294967296-1
有符号最大值为:2147483648-1
二、浮点型
浮点类型:float, double, decimal
定点数类型 dec 等同于 decimal
作用:存储薪资、身高、体重、体质参数等。
float
FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
定义:单精度浮点数(非准确小数值),m是数字总个数,d是小数点后个数。m最大值为255,d最大值为30
有符号:-3.402823466E+38 to -1.175494351E-38
1.175494351E-38 to 3.402823466E+38
无符号:1.175494351E-38 to 3.402823466E+38
精确度:随着小数的增多,进度变的不准确
double
DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
定义:双精度浮点数(非准确小数值),m是数字总个数,d是小数点后个数。m最大值为255,d最大值为30
有符号:-1.7976931348623157E+308 to -2.2250738585072014E-308
2.2250738585072014E-308 to 1.7976931348623157E+308
无符号:2.2250738585072014E-308 to 1.7976931348623157E+308
精准度:随着小数的增多,精度比float要高,但也会变得不准确
decimal
decimal[(m[,d])] [unsigned] [zerofill]
定义:准确的小数值,m是数字总个数(负号不算),d是小数点后个数。 m最大值为65,d最大值为30
精准度:随着小数的增多,精度始终准确
对于精确数值计算时需要用此类型,decaimal能够存储精确值的原因在于其内部按照字符串存储。
验证:
float 类型:
MariaDB [db1]> create table t8(id float(256,31));
ERROR 1425 (42000): Too big scale 31 specified for 'id'. Maximum is 30. # 超过30直接报错
MariaDB [db1]>
MariaDB [db1]> create table t8(id float(256,30));
ERROR 1439 (42000): Display width out of range for 'id' (max = 255) # 超过255直接报错
MariaDB [db1]>
MariaDB [db1]> create table t8(id float(255,30)); # 建表成功
Query OK, 0 rows affected (0.01 sec)
double 类型:
MariaDB [db1]> create table t9(id double(256,31));
ERROR 1425 (42000): Too big scale 31 specified for 'id'. Maximum is 30. # 超过30直接报错
MariaDB [db1]>
MariaDB [db1]> create table t9(id double(256,30));
ERROR 1439 (42000): Display width out of range for 'id' (max = 255) # 超过255直接报错
MariaDB [db1]>
MariaDB [db1]> create table t9(id double(255,30)); # 建表成功
Query OK, 0 rows affected (0.01 sec)
decimal 类型:
MariaDB [db1]> create table t10(id decimal(256,31));
ERROR 1425 (42000): Too big scale 31 specified for 'id'. Maximum is 30. # 超过30直接报错
MariaDB [db1]>
MariaDB [db1]> create table t10(id decimal(256,30));
ERROR 1426 (42000): Too big precision 256 specified for 'id'. Maximum is 65. # 超过65直接报错
MariaDB [db1]>
MariaDB [db1]> create table t10(id decimal(65,30)); # 建表成功
Query OK, 0 rows affected (0.01 sec)
MariaDB [db1]> desc t8; # 查看t8表结构
+-------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id | float(255,30) | YES | | NULL | |
+-------+---------------+------+-----+---------+-------+
1 row in set (0.01 sec)
MariaDB [db1]> desc t9; # 查看t9表结构
+-------+----------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------+------+-----+---------+-------+
| id | double(255,30) | YES | | NULL | |
+-------+----------------+------+-----+---------+-------+
1 row in set (0.00 sec)
MariaDB [db1]> desc t10; # 查看t10表结构
+-------+----------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------+------+-----+---------+-------+
| id | decimal(65,30) | YES | | NULL | |
+-------+----------------+------+-----+---------+-------+
1 row in set (0.00 sec)
MariaDB [db1]> insert into t8 values(1.1111111111111111111111111111111); # 插入小数到t8表,小数后31位
Query OK, 1 row affected (0.00 sec)
MariaDB [db1]> insert into t9 values(1.1111111111111111111111111111111); # 插入小数到t9表,小数后31位
Query OK, 1 row affected (0.00 sec)
MariaDB [db1]> insert into t10 values(1.1111111111111111111111111111111); # 插入小数到t10表,小数后31位
Query OK, 1 row affected, 1 warning (0.00 sec)
MariaDB [db1]> select * from t8; # 查看t8存入记录
+----------------------------------+
| id |
+----------------------------------+
| 1.111111164093017600000000000000 | # 随着小数的增多,精度开始不准确
+----------------------------------+
1 row in set (0.00 sec)
MariaDB [db1]> select * from t9; # 查看t9存入记录
+----------------------------------+
| id |
+----------------------------------+
| 1.111111111111111200000000000000 | # 精度比float要准确点,但随着小数的增多,同样变得不准确
+----------------------------------+
1 row in set (0.00 sec)
MariaDB [db1]> select * from t10; # 查看t10存入记录
+----------------------------------+
| id |
+----------------------------------+
| 1.111111111111111111111111111111 | # 精度始终准确,d为30,于是只留了30位小数
+----------------------------------+
1 row in set (0.00 sec)
共有 0 条评论