MySQL数据类型

MySQL为什么要提供数据类型

  • 如果没有数据类型，那么MySQL就不知道需要分配多大的存储空间来存储你的数据
  • 过大会浪费会造成资源浪费，体积变大效率变低，过小可能导致数据溢出不能完整的保存数据
  • 所以MySQL提供数据类型的目的是为了让我们合理的分配存储空间，降低数据库的体积
  • 为了让我们合理的分配存储空间，完整的保存数据
  • 为了更好的对数据库进行优化

MySQL中数据类型

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

整数类型

  • 专门用来保存整数的
数据类型 概述
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）极大整数值

🐤注意点

  • MySQL中的整型和其它编程语言的整型一样，也区分有符号和无符号
  • 默认情况下整型就是有符号的
create table person(
    id int,
    age tinyint
);

insert into person values (1, -128);
insert into person values (2, 127);
insert into person values (3, 128);# 报错
  • 我们可以在数据类型的后面加上 unsigned 来将数据类型变成无符号的
create table person2(
    id int,
    age tinyint unsigned
);

insert into person2 values (1, -128);# 报错
insert into person2 values (2, 127);
insert into person2 values (3, 128);
  • 在设计数据库的时候一定要合理的使用数据类型
    • 例如：我们要保存一个人的年龄(整数)
    • 我们应该使用TINYINT类型，因为人最多活到255岁已经上天了，所以使用最小的整型即可
    • 如果使用其它的整型，就会造成资源浪费，数据库体积变大，效率变低...
  • 在保存数据的时候，如果超出了当前数据类型的范围，那么就会报错
  • 在设置整型的时候，还可以设置整型数据将来显示的位宽
    • 例如：现在设置将来显示整型的位宽是2，现在存储的数据1，那么将来查询出来的数据就会显示成 1，在1的前面加了一个空格，也可以更换成加0;
    • 如果存储的数据没有指定的位宽宽，那么就会自动补空格或者0，如果大于或者等于了指定的位宽，那么毛都不做
create table person3(
    id int,
    age tinyint(2) zerofill
);

insert into person3 values (1, 1);   # 01
insert into person3 values (2, 12);  # 12
insert into person3 values (3, 123); # 123

浮点类型

  • 专门用来保存小数的
数据类型 概述
FLOAT(m, d) 4字节，单精度
DOUBLE(m, d) 8字节，双精度
  • m总位数，d保留小数位数

🐤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
# height: 1.123456789012345-7000

定点类型

  • 也是用于存储小数的
  • 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

字符类型

  • 专门用来存储字符的
数据类型 概述
CHAR(size) 0-255字节，固定长度字符串
VARCHAR(size) 0-65535字节，可变长度字符串

🐤char和varchar区别

  • 能够保存数据的容量不一样
  • char不会回收多余的字符，要多少给多少
    • 例如：通过char(2)存储，存储数据'a'，存储的结果是' a';
  • varchar会回收多余的字符，用多少给多少
    • 例如：通过varchar(2)存储，存储数据'a'，存储的结果是'a';
create table person5(
    id int,
    name1 char(2),
    name2 varchar(2)
);

insert into person5 values (1, 'a', 'b');
insert into person5 values (2, '12', '34');
insert into person5 values (3, 'abc', 'def');	# 只要超出申请的范围就会报错
  • 由于是字符类型，所以传递值建议用单引号''
  • VARCHAR理论上可以存储65535个字符，但是实际会随着当前数据库的字符集改变
  • 65535 / 3 = 21845，由于utf8一个字符占用3个字节，所以varchar在utf8的表中最多只能存储21845个字符
  • 65535 / 2 = 32767，由于gbk一个字符占用2个字节，所以varchar在gbk的表中最多只能存储32767个字符

大文本类型

  • MySQL中每一行存储的数据是有大小限制的，每一行最多只能存储65534个字节
 create table person6(
    name1 char(3),
    name2 varchar(21845)# 在UTF8中相当于65535个字节
)charset=utf8;

 create table person6(
    # name1 char(3),
    name2 varchar(21845)# 在UTF8中相当于65535个字节
)charset=utf8;

 create table person6(
    # name1 char(3),
    name2 varchar(21844)# 在UTF8中相当于65535个字节
)charset=utf8;

🐤大文本类型

数据类型 概述
TINYTEXT 0-255字节，短文本字符串
TEXT 0-65535字节，长文本数据
MEDIUMTEXT 0-16777215字节，中等长度文本数据
LONGTEXT 0-4294967295字节，极大文本数据 
create table person7(
    name1 char(3),
    name2 TEXT# 不会报错，因为没有超出限制，实际只占用10个字节
)charset=utf8;
  • 大文本类型在表中并不会实际占用所能保存的字节数，而是利用10个字节引用了实际保存数据的地址

枚举类型

  • 和其它编程语言一样，如果某个字段的取值只能是几个固定值中的一个，那么就可以使用枚举
  • enum(值1, 值2, ...)
create table person8(
    id int,
    gender enum('男', '女', '妖')
);

insert into person8 values (1, '火');# 会报错
insert into person8 values (2, '男');# 不会报错
insert into person8 values (3, '女');# 不会报错
insert into person8 values (4, '妖');# 不会报错

🐤注意点

  • MySQL中的枚举类型和其它的编程语言一样，底层都是使用整型来实现的
  • 和其它编程语言不太一样的是，其它编程语言的枚举都是从0开始的，而MySQL的枚举是从1开始的
select gender+0 from person8;
  • 由于MySQL的枚举底层是使用整型实现的，所以我们在赋值的时候除了可以赋值固定的几个值其中的一个以外，我们还可以赋值对应的整数
insert into person8 values (5, 1);# 不会报错
insert into person8 values (6, 4);# 会报错

集合类型

  • 和编程开发中一样，如果某个字段的取值只能是几个固定值中的几个，那么就可以使用集合类型
  • set(值1, 值2, ...)
create table person9(
    id int,
    hobby set('篮球','足球','高尔夫球','足浴')
);

insert into person9 values (1, '篮球,足球,高尔夫球');# 不会报错
insert into person9 values (1, '橄榄球');	   # 会报错

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

🐤注意点

  • MySQL的集合类型也是使用整型来实现的
select hobby+0 from person9;
  • MySQL的集合类型是按照2(n)的方式来实现的
2(0) = 1
2(1) = 2
2(2) = 4
2(3) = 8

布尔类型

  • 专门用来保存真假的
create table person10(
    id int,
    flag boolean
);

insert into person10 values (1, '男');  # 会报错
insert into person10 values (1, true);  # 不会报错
insert into person10 values (2, false); # 不会报错

🐤注意点

  • MySQL中的布尔类型也是使用整型来实现的，0就表示假，1就表示真
  • 底层的本质是因为MySQL是使用C/C++来实现的，所以就是非零即真
insert into person10 values (3, 1); # 不会报错
insert into person10 values (4, 0); # 不会报错
insert into person10 values (5, 2); # 不会报错

日期类型

  • 专门用来保存时间的
数据类型 概述
DATE 3字节，YYYY-MM-DD，日期值
TIME 3字节，HH:MM:SS，时间值或持续时间
DATETIME 8字节，YYYY-MM-DD HH:MM:SS，混合日期和时间值
  • 在存储时间的时候，需要用单引号将时间括起来
create table person11(
    id int,
    filed1 DATE,
    filed2 TIME,
    filed3 DATETIME
);

insert into person11 values (1, '2020-02-02', '14:18:23', '2020-02-02 14:18:23');
posted @ 2020-08-02 10:46  BNTang  阅读(108)  评论(0)  编辑  收藏  举报