目录：

一、字段类型

字段类型：MySQL中用来规定实际存储的数据格式

字段类型在定义表结构的时候设定

设定好字段类型后，插入数据时必须与字段类型对应；否则数据错误

字段类型作用：强制规范录入的数据格式，保证数据的有效性

MySQL有四大数据类型：整数类型、小数类型、字符串类型、时间日期类型

1、整数类型

MySQL中为了数据空间的有效使用，设定了五种整数类型：

  • 迷你整形：tinyint，使用1个字节存储整数（-128~127）
  • 短整型：smallint，使用2个字节存储整数
  • 中整型：mediumint，使用3个字节存储整数
  • 标准整型：int，使用4个字节存储整数
  • 大整型：bigint，使用8个字节存储整数

数值型类型在MySQL中默认是有符号的，即有正负：无符号需要使用unsigned修饰整型，即纯正数

【例1】设计一个表记录个人信息：年龄、头发数量

# 年龄：没有负数，正常年龄也不超过200岁，迷你整型无符号即可
# 头发数量：没有负数，大概在几百万根，所以标准整型无符号即可
create table t1(
    age tinyint unsigned, # unsigned修饰整数，表示无符号（从0开始）
    haircount int unsigned
)charset utf8mb4;

【例2】设计一个表记录4S店的汽车销量信息：库存数量、销量、采购量

# 4S店经常是先卖后进货，所以库存可能为负数，一个店铺的库存数通常不会太多，那么小整型即可
# 销量通常全国一个月也就几万台，所以基本上小整型就够了，但是为了保证后续可能存在的爆发，那么中整型也是绝对够了的，而且不会是负数
# 采购通常是正向采购，所以不会出现负数，而采购数量与销量持平就好
create table t2(
    stock smallint,
    sales mediumint unsigned,
    purchase mediumint unsigned
)charset utf8;

2、显示宽度

显示宽度：int(L)，整数在数据库中显示的符号（数字+符号）个数

显示宽度并不表示存储长度，是用来填充显示位的
例如tinyint(3)，如果实际值是8，如果列指定了zerofill，查询后显示的结果就是008，左侧是用0来填充的。

显示宽度包含符号（如果允许为负数，-负号会增加一个宽度）

显示宽度可以主动控制：创建字段时加括号确定

显示宽度不会影响类型能表示的最大数值

可以通过zerofill让不够宽度的数值补充到对应宽度：在字段类型后使用zerofill

  • 有符号和无符号对应的宽度不一样
create table t1(
    a tinyint,
    b tinyint unsigned
)charset utf8;

LV0R4ET1WIMWHJRDT7MS.png

  • 可以主动控制显示宽度
alter table t1 add c tinyint(2) unsigned;

image-20210224222118223.png

  • 显示宽度不影响数据的大小
# 小于显示宽度
insert into t1 values(1,1,1); 
# 大于显示宽度
insert into t1 values(100,100,100);

image-20210224222149684.png

  • 可以通过zerofill让小于显示宽度的数值前置补充0到显示宽度
# 0填充只能针对正数
alter table t1 add d tinyint(2) zerofill; 
insert into t1 values(1,1,1,1);

LHY6WM1V7GP0TXUUUUKAA.png

BIOWQ8ELJF_RBIOWEQAKF.png

3、小数类型

浮点型

浮点数：float/double，存储不是特别精确的数值数据

浮点数又称之为精度数据，分为两种

  • 单精度：float，使用4个字节存储，精度范围为6-7位有效数字
  • 双精度：double，使用8个字节存储，精度范围为14-15位有效数字

浮点数超过精度范围会自动进行四舍五入

精度可以指定整数和小数部分

  • 默认不指定，整数部分不超过最大值，小数部分保留2位
  • 可以指定：float/double(总长度,小数部分长度)

可以使用科学计数法插入数据：AEB，A * 10 ^ B

【例】记录商品的价格

create table t_11(
    goods_name varchar(20),
    goods_price float
)charset utf8;
insert into t_11 values('Nokia3310',199.99);
insert into t_11 values('Nokia6100',1999.9999);

定点型

定点型：decimal，能够保证精度的小数

定点数可以指定长度

  • decimal：默认
    • 整数部分为10位
    • 小数部分为0
  • decimal(有效位数, 小数位数)
    • 整数部分为：有效位数 - 小数位数
    • 有效数位不超过65个

定点数的存储模式不是固定长度，所以数据越大占用的存储空间越长，每9个数字使用4个字节存储

【例】记录个人资产情况：资产和负债

create table t1(
    money decimal(14,2);
    bet decimal(10,2);
)charset uft8mb4;

insert into t1 values(1111111111.12,1111111.999);
# 错误：进位导致正数部分超过指定范围
insert into t1 values(1111111111.12,99999999.999);

4、字符串类型

定长型

定长型：char(L)，指定固定长度的存储空间存储字符串

定长是指定存储长度

定长的长度是字符而不是字节

  • L的最大值是255
  • 实际存储空间：L字符数 * 字符集对应字节数

定长里存储的数据不能超过指定长度，但是可以小于指定长度

字符串数据使用单引号或者双引号包裹

定长的访问效率较高，但是空间利用率不高

【例】记录个人信息：身份证信息和手机号码

create table t1(
    id_number char(18),
    phone char(11)
)charset utf8mb4;
insert into t1 values('410000190001011234', '13666666666');

变长型

变长型：varchar(L)，根据实际存储的数据变化存储空间

变长型的存储空间是由数据的长度实际计算存储空间

变长型的L也是指字符而不是字节

  • L指定的是最大存储的数据长度
  • L最大值理论是65535
  • 变长需要额外产生1-2个字节，用来记录实际数据的长度
    • 数据长度小于256个，多1个字节
    • 数据长度大于256个，多2个字节
  • 实际存储空间：实际字符数 * 字符集对应字节数 + 记录长度

变长数据不能超过定义的最大长度

变长字符串在读取时需要进行长度计算，所以效率没有定长字符串高

【例】记录个人信息：用户名、密码、姓名、身份证

create table t1(
    username varchar(50),
    password varchar(20),
    name varchar(10),
    id_number char(18)
)charset utf8mb4;

insert into t1 values('user','123456','user1','410000190010101234');

文本字符串

文本字符串：text/blob，专门用来存储较长的文本

文本字符串通常在超过255个字符时使用

文本字符串包含两大类

  • text：普通字符
    • tinytext：迷你文本，不超过 $2 ^ 8 -1$ 个字符
    • text：普通文本，不超过 $2 ^ {16} - 1$ 个字符
    • mediumtext：中型文本，不超过 $2 ^ {24} - 1$ 个字符
    • longtext：长文本，不超过 $2 ^{32} - 1$ 个字符（4G）
  • blob：二进制字符（与text类似）
    • tinyblob、blob、mediumblob、longblob

文本字符串会自动根据文本长度选择适合的具体类型

一般在文本超过255个字符时，都会使用text（blob现在极少使用）

【例】记录新闻信息：标题、作者和内容

create table t1(
    author varchar(10),
    title varchar(50),
    content text
)charset utf8mb4;

insert into t1 values(''佚名','给联合国的一封信','给联合国的一封信...');

枚举

枚举：enum，一种映射存储方式，以较小的空间存储较多的数据

枚举在定义后数据只能出现定义时其中的一种

枚举使用1-2个字节存储，最多可以设计65535个选项

枚举实际存储是使用数值，映射对应的元素数据，从1开始

【例】记录人群类型：小朋友、少年、青年、中年、老年，每个人实际只属于一种类别

create table t1(
    type enum('小朋友','少年','青年','中年','老年')
)charset utf8mb4;

insert into t_16 values('少年');
insert into t_16 values('仙人');  # 不存在的数据不能插入

image-20210225212012646.png

enum是建立映射关系，然后实际存储是数字，数值是按照元素顺序从1开始

# 可以使用字段 + 0来判定数据具体的效果（字符串转数值为0）
select type, type + 0 from t1;
insert into t1 values(5);

枚举定义原理

枚举数据 映射值
数据1 1
数据2 2
... ...
数据N N（小于65535）

数据存储（读取反过来）

graph LR A(指令开始)-->B[插入数据] B-->C[读取映射关系<br>元素==数值<br>数值==数值] C-->D[数值存储到字段] D-->E((结束))

集合

集合：set，一种映射存储方式，以较小的空间存储较多的数据

集合类似一种多选框，在定义后数据只能出现定义时其中的元素（可以是多个）

集合使用1-8个字节存储数据，最多可以设计64个元素

集合实际存储是使用数值（二进制位），映射对应的元素数据，每个元素对应一个比特位

  • 数据存在：对应位为1
  • 数据不存在：对应位为0

集合语法：set(元素1,元素2,...元素N)

集合定义原理

集合数据 映射位
数据1 00000001
数据2 00000010
... ...
数据8 10000000

数据存储（读取反过来）

graph LR A(指令开始)-->B[插入数据] B-->C[读取映射关系<br>元素选中==位值为1<br>元素未选中==位值为0] C-->D[转化成十进制存储] D-->E((结束))

【例】记录个人的球类爱好，有篮球、足球、羽毛球、网球、乒乓球、排球、台球、冰球

create table t1(
    hobby set('足球','篮球','羽毛球','网球','乒乓球','排球','台球','冰球')
)charset utf8;

insert into t1 values('足球');
insert into t1 values('冰球,台球,篮球');

集合建立的也是映射关系，映射方式是每个元素对应一个字节的比特位，从左边开始第一个对应字节从右边开始的第一位

# 可以通过字段 + 0的方式查看存储的具体数值
select hobby, hobby + 0 from t1;

# 可以通过插入数值来组合元素，但是需要确定对应的十进制转换成二进制有对应的元素对应
insert into t1 values(194);

# 冰球、台球、篮球
# 11000010 => 1 + 2 ^ 1 + 1 * 2 ^ 6 + 1 * 2 ^ 7 = 2 + 64 + 128 = 194

5、时间日期类型

年

年：year，MySQL中用来存储年份的类型

MySQL中使用1个字节存储年份

year能够表示的范围是1901-2155年（256年）

year的特殊值是：0000

year允许用户使用两种方式设计（效果一样）

  • year
  • year(4)

year数据可以用两种方式插入

  • 直接插入4位年，1901-2155之间都可以

  • 插入2位数，系统会自动匹配对应的年份

    • 69以前：系统加上2000
    • 69以后：系统加上1900
create table t1(
    y1 year,
    y2 year(4)
)charset utf8mb4;
insert into t1 values(1901,2155);
insert into t1 values(69,70);
insert into t1 values(00,0000);

时间戳

时间戳：timestamp，基于格林威治时间的时间记录

MySQL中时间戳表现形式不是秒数，而是年月日时分秒格式

  • YYYY-MM-DD HH:II::SS
  • YYYYMMDDHHIISS

timestamp使用4个字节存储，表示范围是1971年1月1日0时0分0秒-2155年12月31日23是59分59秒

timestamp的特点是所对应的记录不论哪个字段被更新，该字段都会更新到当前时间

注意：在MySQL8以后，取消了 timestamp 的默认自动更新，如果需要使用，需要额外使用属性： on update current_timestamp

【例】记录商品库存的最后更新时间

create table t_19(
    goods_name varchar(10),
    goods_inventory int unsigned,
    change_time timestamp
)charset utf8;

insert into t_19 values('Nokia3110',100,'1971-01-01 00:00:00');
insert into t_19 values('Nokia7100',100,'19710101000000');
# timestamp会在自己所在的记录任何位置被修改时自动更新时间
update t_19 set goods_inventory = 90;
# 在MySQL8中需要主动使用on update current_timestamp才会自动更新
alter table t_19 add c_time timestamp on update current_timestamp;
update t_19 set goods_inventory = 80;

image-20210225221152797.png

日期

日期：date，用来记录年月日信息

使用3个字节存储数据，存储区间是1000 - 9999年，跨度很大

存储日期的格式为：YYYY-MM-DD

存储的范围是：1001-01-01~9999-12-31

【例】记录个人生日

create table t_20(
    name varchar(10),
    birth date
)charset utf8mb4;

insert into t_20 values('A','2020-02-25');
insert into t_20 values('B','20200225');

日期时间

日期时间：datetime，用来综合存储日期和时间

使用8个字节存储数据

存储格式为：YYYY-MM-DD HH:II:SS

存储区间为：1000-01-01 00:00:00 到9999-12-31 23:59:59

实际开发中因为编程语言（PHP）的强大，实际存储的时候通常不会使用这种类型

  • 占用较大存储空间
  • 处理不够灵活（固定格式）
  • 使用int unsigned存储时间戳然后利用PHPdate进行格式处理

【例】记录个人具体的出生时间

create table t_21(
    name varchar(10),
    birth datetime
)charset utf8mb4;

insert into t_21 values('Jim','2000-12-12 12:12:12');
insert into t_21 values('Tom','10011212182323');

时间

时间：time，用来记录时间或者时间段

使用3个字节存储数据

数据范围是 -838:59:59 - 838:59:59

数据插入的格式分为两种

  • 时间格式：[H]HH:II:SS（[]表示可以没有）
  • 时间段格式：D HH:II:SS（D表示天）

time类型通常被用来做时间段计算：如多少天后的什么时间点（可以理解为过期检查）

【例】记录用户登录的具体时间

# 具体登录时间可以使用时间戳（包含年月日时分秒信息）
# 也可以时间datetime格式，或者date+time双字段格式（具体后面学习范式时会知道该怎么用）
create table t_22(
    login_time1 int unsigned,
    login_time2 datetime,
    login_date date,
    login_time3 time
)charset utf8mb4;

insert into t_22 values(12345678,'2000-12-12 12:12:12','2000-12-12','12:12:12');
insert into t_22 values(1234567,'2000-12-12 12:12:12','2000-12-12','3 12:12:12');

image-20210225221730764.png

6、总结

1、字段类型是用来规范数据的格式的

2、MySQL中有很多类型用来规范数据格式

  • 整数类型（常用）
    • 常用类型：tinyint、int
  • 小数类型（常用）
    • 常用类型：decimal、float
  • 字符串类型（常用）
    • 常用类型：char、varchar、text
  • 时间日期类型（不常用：通常使用真正时间戳存储数据，然后PHP进行灵活解读）

3、实际开发的时候，一定要仔细了解需求，根据需求判定好具体选用那种数据类型

  • 最原始的维护能够具有最大的通用性（选中类型）
  • 最小的消耗能够解决全部的问题（巧妙利用存储空间）

二、属性

属性：建立在字段类型之后，对字段除类型之外的其他约束

属性是在定义表字段的时候针对每个字段进行属性设定

设定好的属性可以通过查看表字段desc进行查看

数据在进行增删改（写）操作时需要在满足字段的要求同时还要满足属性的要求

1、NULL属性

NULL：数据是否允许为空

默认情况下数据是允许为空的

不为空设计：Not Null

一般有效的数据都必须设定为Not Null来保证数据的有效性

【例】用户信息表：用户名、密码、姓名、年龄、注册时间

create table t_23(
    username varchar(50) not null,
    password char(30) not null,
    name varchar(20),
    age tinyint unsigned,
    reg_time int unsigned not null
)charset utf8mb4;

insert into t_23 values('username','password','Jim',20,123456789);
# 错误操作：reg_time不能为空，而默认为空，所以系统报错
insert into t_23 (username,password) values('username','password');

2、Default属性

默认值：default，在设计表字段的时候给定默认数据，在后续字段操作（数据新增）的时候系统没有检测到字段有数据的时候自动使用的值

默认值在字段设计的时候使用（默认值需要满足数据类型规范）

默认值通常设计的是字段容易出现的数据

  • 一般字段的默认值默认是Null

默认值触发

  • 在系统进行数据插入时自动检测触发
  • 主动使用default关键字触发默认值

【例】用户开户：银行卡账号、身份证号码、姓名、账户余额

create table t_24(
    account varchar(19) not null,
    id_card char(18) not null,
    name varchar(20) not null,
    money decimal(16,2) default 0.00 not null
)charset utf8mb4;

# 默认触发
insert into t_24 (account,id_card,name) values('6226000000000001','44011120001212000x','Lily');

# 主动触发
insert into t_24 values('6226000000000002','440111200012120011','Tom',default);
insert into t_24 values('6226000000000003','440111200012120022','Jim',100);

3、主键

主键：primary key，用来保证整张表中对应的字段永远不会出现重复数据

主键在一张表中只能有一个

主键的另外一个特性是能够提升主键字段作为查询条件的效率（索引）

主键不能为空：Not Null（默认）

逻辑主键：数据没有具体业务意义，纯粹是一种数值数据

  • 逻辑主键通常是整数：int
  • 逻辑主键目的是方便检索和数据安全（不暴露数据真实信息）

复合主键：多个字段共同组成不能重复的数据

  • primary key(字段1,字段2,...字段N)
  • 联合主键使用不多，一般也不会超过2个字段

【例】银行账户信息：账户、姓名、余额

# 银行账户具有唯一性，不能重复，也不允许为空
create table t_25(
    account varchar(17) primary key,
    name varchar(20) not null,
    money decimal(16, 2) not null default 0.00
)charset utf8mb4;

# 复合主键
create table t_26(
    account varchar(17),
    name varchar(20),
    money decimal(16, 2) not null default 0.00,
    primary key(account,name)
)charset utf8mb4;

# 一般使用逻辑主键
create table t_27(
    id int unsigned primary key,
    account varchar(17) not null,
    name varchar(20) not null,
    money decimal(16, 2) not null default 0.00
)charset utf8mb4;

insert into t_27 values(1,'6226000000000001','Lily',default);
# 错误：主键1已经存在
insert into t_27 values(1,'6226000000000002','Tom',default);
# 联合主键就是联合字段加起来不重复即可
insert into t_26 values('6226000000000001','Tom',default);
insert into t_26 values('6226000000000002','Tom',default);

删除主键：主键只有一个，所以删除语法也比较特殊

alter table t_26 drop primary key;

后期新增主键：如果是针对业务主键需要保证字段数据没有Null数据且没有数据重复（一般主键都会在表创建时维护好）

alter table t_26 add primary key(account, name);

4、自增长属性

自增长：auto_increment，被修饰的字段在新增时，自动增长数据

自增长只能是整数类型，而且对应的字段必须是一个索引（通常逻辑主键）

一张表只能有一个自动增长

自增长数据可以理解为一种默认值，如果主动给值，那么自动增长不会触发

自增长由两个变量控制

  • 初始值：auto_increment_offset，默认是1
  • 步长：auto_increment_increment，默认值也是1
  • 查看自增长控制：show variables like 'auto_increment%';

【例】记录学生信息：学号和姓名

# 学生信息：学号自动增长
create table t_28(
    id int primary key auto_increment,
    stu_no int(8) zerofill not null,
    stu_name varchar(20) not null
)charset utf8mb4;

# 使用自增长（可以使用NULL或者default来触发）
insert into t_28 values(null, 1, 'Jim');
insert into t_28 values(default, 2, 'Tom');
# 主动控制：自增长的值会从当前最大的值开始自动增长
insert into t_28 values(10, 3, 'Lily');
insert into t_28 values(null, 4, 'Lucy');

image-20210225230547203.png

修改表中自增长的值：让下次自增长按照指定值开始

alter talbe t_28 auto_increment = 50;

注意：奇数会保留原值，偶数会自动加1（可能出现的情况）

修改自增长控制：步长和起始值（修改针对的是整个数据库，而非单张表）

# 当前用户当前连接有效（局部）
set auto_increment_increment = 2;
# 所有用户一直有效（全局）
set @@auto_increment_increment = 2;

5、唯一键

唯一键：unique key，用来维护数据的唯一性

一个表中可以有多个唯一键

唯一键与主键的区别在于唯一键允许数据为Null（而且Null的数量不限）

唯一键与主键一样，可以提升字段数据当做条件查询的效率（索引）

复合唯一键：多个字段共同组成

  • unique key (字段1, 字段2, ...字段N)
  • 一般不会出现，最多2个字段组成

【例】用户表：用户名唯一，而且经常作为查询条件

create table t_29(
    id int primary key auto_increment,
    username varchar(50) unique,
    password char(32) not null
)charset utf8mb4;

insert into t_29 values(null, 'username', 'password');
insert into t_29 values(null, Null, 'password');
insert into t_29 values(null, Null, 'password');
# 错误
insert into t_29 values(null,'username','password1');

image-20210225232520393.png

image-20210225232454781.png

【例】学生成绩表：一个学生只能有一个学科成绩，但是可以有多个学科

# 学号和学科编号共同组成唯一
create table t_30(
    id int primary key auto_increment,
    stu_name varchar(20) not null,
    course varchar(20) not null,
    score decimal(5,2),
    unique key(stu_name, course)
)charset utf8;

insert into t_30 values(null,'Jim','Math',50);
insert into t_30 values(null,'Jim','English',80);

删除唯一键：一张表中不止一个唯一键，所以删除方式是相对麻烦

  alter table 表名 drop index 唯一键名字;

例如：

alter table t_30 drop index `stu_name`;

image-20210225233038570.png

新增唯一键

alter table 表名 add unique key(字段列表);

例如：

alter table t_30 add unique key `stu_course`(stu_name, course);

image-20210225233147128.png

6、comment 属性

描述：comment，是用文字描述字段的作用的

描述如果涉及到字符集（中文）一定要在创建表之前设置好客户端字符集（否则会出现描述乱码）

【例】学生成绩表

create table t_31(
    id int primary key auto_increment,
    stu_no varchar(10) not null comment '学号',
    course_no varchar(10) not null comment '课程号',
    score decimal(5,2) comment '考试成绩',
    unique key `stu_course` (stu_no,course_no) comment '学号和课程号组成唯一键'
)charset utf8;

如果要查看描述信息，需要使用 show create table 查看

三、数据库记录长度

数据库记录长度：MySQL中规定一条记录所占用的存储长度最长不超过65535个字节

记录长度为表中所有字段预计占用的长度之和

所有字段只有允许Null存在，系统就会预留一个字节存储Null（多个Null也只要一个就好）

因为MySQL记录长度的存在，varchar永远达不到理论长度

一般数据长度超过255个字符都会使用text/blob进行存储（数据存储不占用记录长度）

【例】GBK表能存储的最大varchar字符串长度

create table t_32(
    content varchar(65535)
)charset gbk;   # 错误

create table t_32(
    content varchar(32767)
)charset gbk;   # 错误

create table t_32(
    content varchar(32766)
)charset gbk;

【例】UTF8表能存储的最大varchar字符串长度

create table t_33(
    content varchar(65535)
)charset utf8;  # 错误

create table t_33(
    content varchar(21844)
)charset utf8;

【例】Null也要占用一个字节

create table t_34(
    id tinyint,
    content varchar(21844)
)charset utf8;  # 错误

create table t_34(
    id tinyint not null,
    content varchar(21844) not null
)charset utf8;