MySQL数据类型基本理解

1.MySQL为什么要提供数据类型?

如果没有数据类型, 那么MySQL就不知道需要分配多大的存储空间来存储你的数据

过大会浪费会造成资源浪费, 体积变大效率变低, 过小可能导致数据溢出不能完整的保存数据

所以MySQL提供数据类型的目的是：

• 为了让我们合理的分配存储空间, 降低数据库的体积
• 为了让我们合理的分配存储空间, 完整的保存数据
• 为了更好的对数据库进行优化

2.MySQL中有哪些数据类型?

• 整型类型 / 浮点类型 / 定点类型 / 字符类型 / 文本类型 / 枚举类型 / 集合类型 / 日期类型 / 布尔类型

3.整数类型 - 专门用来保存整数的

TINYINT 1 字节 (-128，127) (0，255) 小整数值

SMALLINT 2 字节 (-32 768，32 767) (0，65 535) 大整数值

MEDIUMINT 3 字节 (-8 388 608，8 388 607) (0，16 777 215) 大整数值

INT或INTEGER 4 字节 (-2 147 483 648，2 147 483 647) (0，4 294 967 295) 大整数值

BIGINT 8 字节 (-9,223,372,036,854,775,808，9 223 372 036 854 775 807) (0，18 446 744 073 709 551 615) 极大整数值

注意点:

• 1.MySQL中的整型和其它编程语言的整型一样, 也区分有符号和无符号
  • 默认情况下整型就是有符号的
  • 我们可以在数据类型的后面加上 unsigned 来将数据类型变成无符号的

    create table person2(
       id int,
       age tinyint unsigned
   );
   insert into person2 values (1, -128); #报错
   insert into person2 values (1, 127);
   insert into person2 values (1, 128);

• 2.在设计数据库的时候一定要合理的使用数据类型
  • 例如: 我们要保存一个人的年龄 (整数)。我们应该使用TINYINT类型, 因为人最多活到255岁已经上天了, 所以使用最小的整型即可。如果使用其它的整型, 就会造成资源浪费, 数据库体积变大, 效率变低…
• 3.在保存数据的时候, 如果超出了当前数据类型的范围, 那么就会报错

create table person(
    id int,
    age tinyint
);
insert into person values (1, -128); #在范围里
insert into person values (1, 127); #在范围里
insert into person values (1, 128); #报错

• 4.在设置整型的时候, 还可以设置整型数据将来显示的位宽
  • 例如: 现在设置将来显示整型的位宽是2, 现在存储的数据1, 那么将来查询出来的数据就会显示成 1[即：空格+1];
  • 比如日期不足两位补0：2020-2-3 – 2020-02-03
  • 注意点: 如果存储的数据没有指定的位宽宽, 那么就会自动补空格或者0, 如果大于或者等于了指定的位宽, 那么毛都不做

create table person3(
    id int,
    age tinyint(2) zerofill #2：字段不足两位变成两位，zerofill：用0填充
);
insert into person values (1, 1);   #01
insert into person values (1, 12);  #12
insert into person values (1, 123); #123

4.浮点类型 - 专门用来保存小数的

FLOAT(m, d) 4 字节 单精度

DOUBLE(m, d) 8 字节 双精度

m总位数, d小数位数

• float和double的区别
  • 占用存储空间大小不一样 float<double
  • 默认保留的小数位数不同：示例一、示例二 float<double
  • 保存数据的有效精度也不同：示例三 float<double

示例一: 默认保留的小数位数不同

create table person(
    id int,
    weight FLOAT,
    height DOUBLE
);
insert into person values (1, 1.12345678901234567890, 1.12345678901234567890);

结果：weight: 1.12346、
height: 1.1234567890123457

示例二: 手动指定小数的总位数和小数部分的位数

create table person2(
    id int,
    weight FLOAT(10, 6),
    height DOUBLE(10, 6)
);
insert into person2 values (1, 1.12345678901234567890, 1.12345678901234567890);

结果：weight: 1.123457、
height: 1.123457

示例三: 保存数据的有效精度也不同

create table person3(
    id int,
    weight FLOAT(20, 19),
    height DOUBLE(20, 19)
);
insert into person3 values (1, 1.12345678901234567890, 1.12345678901234567890);

结果：weight: 1.123456-8357467651000（6以后的数据就不精准了）、
height: 1.123456789012345-7000（5以后的数据就不精准了）

• 浮点类型特点
  • 和其它编程语言中一样, 浮点类型是不准确的
  • 所以在企业开发中千万不要使用浮点数来保存用户的准确(珍贵)信息(RMB)

5.定点类型 - 也是用于存储小数的,存储的比浮点类型精确

decimal(M, D)
m总位数, d小数位数

定点类型的本质:

• 是将数据分为两个部分来存储【整数、小数部分分开存储】, 每个部分都是整数。
• 所以定点数不要滥用, 因为非常消耗资源

create table person4(
    id int,
    weight decimal(21, 20),
    height decimal(21, 20)
);
insert into person4 values (1, 1.12345678901234567890, 1.12345678901234567890);

结果：weight: 1.12345678901234567890、height: 1.12345678901234567890 不丢失精度

6.字符类型 - 专门用来存储字符的

CHAR(size) 0-255 字节 定长字符串
VARCHAR(size) 0-65535字节 变长字符串

• char和varchar区别
  • 能够保存数据的容量不一样
  • char不会回收多余的字符, 要多少给多少
  • varchar会回收多余的字符, 用多少给多少
    • 例如: 通过 char(2)存储存储数据’a’, 存储的结果是’ a’;
    • 例如: 通过 varchar(2)存储存储数据’a’, 存储的结果是’a’;

示例一:

create table person(
    id int,
    name1 char(2),
    name2 varchar(2)
);
insert into person values (1, 'a', 'b');
insert into person values (1, '12', '34');
insert into person values (1, 'abc', 'def'); #只要超出申请的范围就会报错

示例二:

• 注意点: 由于是字符类型, 所以传递值建议用单引号’’
• 注意点：如果没有声明字符串大小，char会默认255字节。

create table person2(
    id int,
    name1 char(255),
    name2 varchar(255)
);

• 注意点: VARCHAR理论上可以存储65535个字符, 但是实际会随着当前数据库的字符集改变

# 65535 / 3 = 21845, 由于utf8一个字符占用3个字节, 所以varchar在utf8的表中最多只能存储21845个字符
# 65535 / 2 = 32767, 由于gbk一个字符占用2个字节,所以varchar在gbk的表中最多只能存储32767个字符
create table person3(
    id int,
    name1 char(255),
    name2 varchar(65535)
)charset=gbk;
Column length too big for column 'name2' (max = 21845); use BLOB or TEXT instead
Column length too big for column 'name2' (max = 32767); use BLOB or TEXT instead

7.大文本类型

7.1、MySQL中每一行存储的数据是有大小限制的, 每一行最多只能存储65534个字节，超出就会报错

验证：

 create table person(
    #name1 char(3),
    name2 varchar(21845) #在UTF8中相当于65535个字节
)charset=utf8;
# Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

7.2、大文本类型

实际生产环境中，一行的数据是很可能超出65535个字节的，因此采用大文本类型。也就是说大文本类型就是专门用于突破每行存储数据的大小限制的。

MySQL中提供了四种大文本类型:

TINYTEXT 0-255字节 短文本字符串

TEXT 0-65535字节 长文本数据

MEDIUMTEXT 0-16777215字节 中等长度文本数据

LONGTEXT 0-4294967295字节 极大文本数据

create table person2(
    name1 char(3),
    name2 TEXT #不会报错, 因为没有超出显示, 实际只占用10个字节
)charset=utf8;

注意点:

• 大文本类型在表中并不会实际占用所能保存的字节数, 而是利用10个字节引用了实际保存数据的地址。也就说是并不是将数据直接存储在数据库的表中，而是将数据存储到其他位置【内存或者磁盘】，然后调用地址即可。类似C的指针

8.枚举类型

和其它编程语言一样, 如果某个字段的取值只能是几个固定值中的一个, 那么就可以使用枚举
enum(值1, 值2, …);

create table person(
    id int,
    gender enum('男', '女', '妖')
);
insert into person values (1, '火'); #会报错
insert into person values (1, '男'); #不会报错
insert into person values (2, '女'); #不会报错
insert into person values (3, '妖'); #不会报错

注意点:

• MySQL中的枚举类型和其它的编程语言一样, 底层都是使用整型来实现的
  • 和其它编程语言不太一样的是, 其它编程语言的枚举都是从0开始的, 而MySQL的枚举是从1开始的

select gender+0 from person;

• 由于MySQL的枚举底层是使用整型实现的, 所以我们在赋值的时候除了可以赋值固定的几个值其中的一个以外，我们还可以赋值对应的整数

insert into person values (4, 1); #不会报错

insert into person values (4, 4); #会报错 没有四

9.集合类型

和编程开发中一样, 如果某个字段的取值只能是几个固定值中的几个【枚举是几个固定值中的一个】, 那么就可以使用集合类型
set(值1, 值2, …)

create table person(
    id int,
    hobby set('篮球','足球','高尔夫球','足浴')
);
insert into person values (1, '篮球,足球,高尔夫球'); #不会报错 7=1+2+4
insert into person values (1, '橄榄球');  #会报错

注意点:

• MySQL的集合类型也是使用整型来实现的

select hobby+0 from person;

• MySQL的集合类型是按照2(n)的方式来实现的。主要是为了方便位运算
  • 2(0) = 1
  • 2(1) = 2
  • 2(2) = 4
  • 2(3) = 8

insert into person values (1, '篮球,足球,高尔夫球'); #不会报错 7=1+2+4
insert into person values (2, '篮球'); #不会报错  1
insert into person values (3, '足球'); #不会报错  2
insert into person values (4, '高尔夫球'); #不会报错  4
insert into person values (5, '足浴'); #不会报错  8

10.布尔类型 - 专门用来保存真假的

create table person(
    id int,
    flag boolean
);
insert into person values (1, true); #不会报错
insert into person values (2, false); #不会报错
insert into person values (1, '男'); #会报错

# 也可以使用整数0/1插入数据
insert into person values (3, 1); #不会报错
insert into person values (4, 0); #不会报错
insert into person values (5, 2); #不会报错，应为C中非0即为真，所以2也可以。

注意点:

• MySQL中的布尔类型也是使用整型来实现的, 0就表示假, 1就表示真
  • 底层的本质是因为MySQL是使用C/C++来实现的, 所以就是’非零即真’

11.日期类型 - 专门用来保存时间的

DATE 3字节 YYYY-MM-DD 日期值

TIME 3字节 HH:MM:SS 时间值或持续时间

DATETIME 8字节 YYYY-MM-DD HH:MM:SS 混合日期和时间值

注意点: 在存储时间的时候, 需要用单引号将时间括起来

create table person(
    id int,
    filed1 DATE,
    filed2 TIME,
    filed3 DATETIME
);
insert into person values (1, '2020-02-02', '14:18:23', '2020-02-02 14:18:23');