—-数据类型篇

整数类型字节最小值最大值
TINYINT1有符号-128
无符号 0		有符号 128
无符号 255
SMALLINT2有符号-32768
无符号 0		有符号 32768
无符号 65535
MEDIUMINT2有符号-8388608
无符号 0		有符号 8388607
无符号 1677215
INT\INTEGER4有符号-2147483648
无符号 0		有符号 2147483647
无符号 4294967295
BIGINT8有符号-9223372036854775808
无符号 0		有符号 9223372036854775807
无符号 18446744073709551615

在整数类型中共有上述几种类型,如果超出类型范围的操作，会发生”OUT OF RANGE”的错误。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 创建一个测试表,int 默认长度是10
create table t1(id1 int,id2 int(5));
# 在表中插入数值1,可以发现数值没有异常
insert into t1 values(1,1);
# 修改id1和id2的字段类型，使其自动填充0
alter table t1 modify id1 int zerofill;
alter table t1 modify id2 int(5) zerofill;
# 查询表单
select * from t1;
+------------+-------+
| id1        | id2   |
+------------+-------+
| 0000000001 | 00001 |
+------------+-------+
1 row in set (0.00 sec)

这样我们就可以直观的看到数据的长度

接下来我们尝试添加超过约束长度限制的数据

insert into t1 values(1,1111111)

1
2
3
4
5
6
7
+------------+---------+
| id1        | id2     |
+------------+---------+
| 0000000001 |   00001 |
| 0000000001 | 1111111 |
+------------+---------+
2 rows in set (0.00 sec)

有些奇怪的是 id2 中的数据既没有被截断也没有报错。这是因为使用了我们使用了 zerofill，而 zerofill 会自动添加 unsigned 属性。

整数类型还有另外一个属性 AUTO_INCREMENT。在需要产生唯一标识符或顺序值时，可使用此属性，这个属性只用于整数类型。AUTO_INCREMENT 值一般从 1 开始，每行增加 1。在插入 NULL 或一个已有的整数时会自动填充当前列最大值+1。一个表中最多一个字段有 AUTO_INCREMENT 属性，对于任意想使用 AUTO_INCREMENT 的属性都应该添加 NOT NULL 约束，并定义为 PRIMARY KEY 或定义为 UNIQUE 键。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
create table AI
(
    ID int auto_increment not null primary key
);
create table AI
(
    ID int auto_increment not null,
    primary key (ID)
);
create table AI
(
    ID int auto_increment not null,
    UNIQUE(ID)
);
浮点数类型字节最小值最大值
FLOAT4±1.175494351E-38±3.402823466E+38
DOUBLE8±2.2250738585072014E-308±1.7976931348623157E+308
定点数类型字节描述
DEC(M,D)/DECIMAL(M,D)M+2最大取值范围与 DOUBLE 相同,给定 DECIMAL 的有效取值范围由 M 和 D 决定

MySQL 有两种表示小数的方式:浮点数和定点数。其中定点数在 MySQL 内部以字符串形式存放，比浮点数更精准，适合用来表示货币等精度高的数据。

浮点数和定点数都使用(M,D)的方式来进行表示。M 表示一共显示 M 位小数(整数位+小数位)
,其中 D 位位于小数点后面，M 和 D 又称为精度和标度。其中 decimal 不指定精度的时候默认精度位 10，标度位 0。float 和 double 在不指定默认的长度时会按照实际的精度来进行设置。

1
2
3
4
5
6
7
8
9
10
11
# 正常插入
insert into t1 values (1.23,1.23,1.23);
# 过长插入会自动舍去末位
insert into t1 (id1, id2, id3) values (1.234,1.234,1.23);
# 如果将id3的位置也设定为1.234则会抛出一个数据被截断警告。数据是可以被正常插入的，但如果是在传统的SQLMode则这条数据会插入失败。
# 清除长度限制后再进行插入测试
alter table t1 modify id1 float;
alter table t1 modify id2 double;
alter table t1 modify id3 decimal;
insert into t1 (id1, id2, id3) values (1.234,1.234,1.23);
# 全部插入成功，但id3还会抛出一个警告，并且能发现id3那一列的所有数据都被截断为1。这个例子就验证了前面的结论。
位类型字节最小值最大值
BIT(M)1~8BIT(1)BIT(64)

对于 BIT 类型，用于存放位字段值，BIT(M)可以用来存放多位二进制数，如果不写则默认为 1.对于位字段，直接使用 SELECT 命令将不会看到结果，可以用 bin(),或者 hex()函数来进行读取。

1
2
3
4
5
6
7
8
9
10
11
12
# 添加一个bit字段
alter table t1 add column id4 bit;
# 可以成功插入插入
insert into t1 (id1, id2, id3,id4) values (1.234,1.234,1,1);
查询的时候要讲bit转换为二进制或者十六进制进行显示(bin()\hex())
select id1,id2,id3,bin(id4) from t1;
# 尝试插入超过长度1的数据,报错超出范围
insert into t1 (id1, id2, id3,id4) values (1.234,1.234,1,2);
# 修改bit长度为2，再次进行插入，操作成功
alter table t1 modify id4 bit(2);
insert into t1 (id1, id2, id3,id4) values (1.234,1.234,1,2);
select id1,id2,id3,hex(id4) from t1;