MySQL运维实战教程（2）MySQL常用数据类型与约束
一、MySQL 数据类型
在任何的数据库管理系统中，数据类型的选择和使用都是设计数据库架构的基础之一。它们不仅决定了存储数据的方式，还直接影响着查询效率、存储空间的利用以及数据完整性。在 MySQL 中，丰富的数据类型支持使得我们能够精准地表达和存储各种形式的数据，包括数字、文本、日期、时间和二进制数据等。
1、文本字符串类型
· CHAR
保存固定长度的字符（非字节），适合存储所有值的长度几乎相同的数据。因为长度总是固定的所以不容易出现更多的碎片。CHAR支持范围是0~255个字符（注意是字符，如果存储UTF8MB4格式的数据，则最多可以存放255*4个字节）
· VARCHAR
用于保存变长字符（需要注意字符和字节的区别，一个字母为1个字符，一个汉字也为1个字符，但是UTF8MB4编码下一个中文字符占用4个字节）。对于固定长度的数据来说使用VARCHAR比CHAR更占据空间，因为VARCHAR还需要额外的信息去记录数据的真实长度。但是对于不同长度的数据来说VACHAR就会比CHAR节约大量的空间。在进行表设计时VARCHAR(10)比VARCHAR(200)更节约内存，所以尽量为每个字段分配合适的长度。VARCHAR类型的数据最大长度理论是65535，但实际上只能设置65533，因为会额外使用2个字节标识变长的长度、1个字节标识NULL值。VARCHAR类型会有一个“255字节”的特殊标记位，使用这个特性在对列进行增长（仅限增长，因为缩减长度可能会涉及数据删除）时，只要不超过255就可以实现inplace，不需要迁移原数据，可以快速的完成
· TEXT | BLOB
用于存放长文本、大对象数据，区别在于BLOB家族存放的是二进制类型数据，没有排序规则和字符集（比如音频、照片），而TEXT存放的是包含排序规则和字符集的文本。根据长度的不同分为TINYTEXT（255字符）、TEXT（65535字符）、MEDIUMTEXT（16777215字符）、BIGTEXT（4G）。MySQL不能将这种类型数据的完整字符串放入索引（只能使用前缀索引），也就不能使用索引完成排序。而且此类型数据无法使用内存临时表，在进行排序操作时必须创建磁盘临时表，又由于该类型存放的数据内容通常比较大，容易导致磁盘IO频繁，影响数据库性能。所以对于这种大字段类型的数据建议放到专门的文件服务器，数据库端只存文件路径。如果必须要放TEXT或者BLOB类型的数据，建议分离到单独的扩展表中，查询时不要使用SELECT * 取到了TEXT或BLOB字段，而是只取必要的字段
· JSON：5.7支持JSON格式的数据，不需要预先定义数据格式，想插入什么就插入什么，更灵活。但缺点也是因为太灵活，对业务没有约束，在管理上可能会带来麻烦，属于双刃剑
2、数字类型
· 整数类型
适用于存储整数的数据类型，通过SIGNED（有符号）和UNSIGNED（无符号，默认值）来确定整数是否允许存储负值。整数类型可分为INT、TINYINT、SMALLINT、MEDIUMINT、BIGINT，区别在于存储的范围不同，最常用的为INT，无符号时取值范围为42亿，有符号时数据范围为-2147483648至2147483647。虽然可以为整数类型的数据设置长度，例如INT(5)，但是这里的长度和数据的实际有效范围并没有关系，只是限制了客户端显示的长度
· 浮点类型
用于存储浮点数值，可分为 FLOAT、DOUBLE以及DECIMAL，主要区别在于精度不同，其中DECIMAL是高精度数字类型，适用于财务等要求高精度的场景，FLOAT和DOULE都不推荐继续使用，MySQL 已经提示会废弃该两种类型
3、时间和日期类型
用于存储年月日时分秒等数据，可分为 DATE、TIME 、TIMESTAMP 和 DATETIME，其中TIMESTAMP 和 DATETIME 都是DATE类型和TIME类型的组合，以YYYY-MM-DD HH:MM:SS的格式表示年/月/日/时/分/秒，比如'20200606213940'或'2020-06-06 21:39:40'。两者的区别在于TIMESTAMP需要4个字节的存储空间，而 DATETIME需要8个字节的存储空间，但是TIMESTAMP存储的时间范围会小很多，只能存储1970年到2038年之间的数据。所以现在较多使用 DATETIME 来存储时间，并且配置 CURRENT_TIMESTAMP 或 ON UPDATE CURRENT_TIMESTAMP 来自动拉取当前时间。
4、枚举类型
在MySQL中使用ENUM表示枚举类型，该类型数据可以让用户从给出的条件中进行单项选择，通常用于性别场景。ENUM类型的数据在存储时非常紧凑，每个枚举值在表中会被保存为整数进行存储，所以会压缩得非常小。在进行表设计的时候可以考虑对一些会出现固定值的字段类型改为枚举类型，比如让用户选择最爱吃的水果，给出固定的枚举类型进行选择，这样在底层存储的将是整数而不是字符串。虽然ENUM在存储上会更高效，但是枚举值不宜过多和经常变更。
5、集合类型
在MySQL中使用SET表示枚举类型，最多可定义 64 个集合值，程序可以从这些集合中取0个或多个值
二、MySQL 数据约束
数据约束（Constraints）用于限制列中存储的数据，以确保数据的准确性、一致性和完整性。常见的数据约束类型包括 主键约束、外键约束、唯一约束、检查约束等
1、主键约束（PRIMARY KEY）
主键约束用于标识表中每一行数据的唯一性。每个表只能有一个主键约束，主键字段的值必须唯一，且不能为空。使用复合主键可以同时给多个字段设置一个主键约束，但是这个组合后的值也必须是唯一的。基于主键索引进行查询时仅需要一次索引查找，无需回表。
主键约束最好建立在跟业务不相关的字段上，比如自增ID字段。如果使用业务字段做主键，无法预测未来会不会因为业务需要出现业务字段重复或者重用的情况，比如使用手机号作为主键会存在号码被回收换新用户的情况、使用身份证做主键的话，不一定所有用户都愿意暴露这样的隐私信息。并且有业务逻辑的字段做主键往往也不容易保证有序插入，这样写数据成本相对较高。设置主键约束的字段最好永远不会进行手动更新。最好符合单调递增场景的字段，即每次插入一条新记录都是追加操作，这样就不涉及挪动其他数据行的情况，也不会触发叶子节点的分裂
#写法1 CREATE TABLE t1 ( id int PRIMARY KEY， name varchar(10) ); #写法2 CREATE TABLE t1 ( id INT, name VARCHAR(10), PRIMARY KEY (id) ); INSERT INTO t1 values (1,'zhangsan')(2,'lisi')(3,'wangwu'); #正常插入三条数据 INSERT INTO t1 values (4,'zhaoliu')(2,'wangba'); #由于ID为2的数字已经存在，报错 #后期添加约束示例，需要先确保没有字段的值是重复且为NULL的 ALTER TABLE t3 ADD PRIMARY KEY (id) #删除主键索引 ALTER TABLE t5 DROP PRIMARY KEY
对有重复值但是没主键的表，如何加主键
#方法1：重复数据没用的话先清理掉再加主键，建议先将原表复制出一张新表再做清理
#方法2：创建一个新的自增长列，然后删除旧列，推荐
alter table student add new_id int auto_increment primary key;
对有主键约束的表插入重复值的办法
# 方法1：使用ignore将重复值跳过
insert ignore into student values (1,'tanglu'),(2,''tanglu2),(3,'tanglu3')
# 方法2：使用replacce替换重复的值
replace into student values (1,'tanglu'),(2,''tanglu2),(3,'tanglu3')
# 方法3：on deplicate key update
2、自增约束（AUTO_INCREMENT）
· 整数类型的字段（TINYINT、SMALLINT、MEDIUMINT、INT 和 BIGINT）才可以添加自增约束
· 自增字段通常不用手动插入数值，如果有手动插入数据，MySQL会重置自增约束字段的自增基数，下次新增数据的时候会从手动插入的数据值开始自增
· 一张表只能有一个自增列，自增列必须是键列（主键列或唯一键列），常定义在主键索引上
· 自增字段的值默认从1开始递增，当达到自增整型类型上限值时，再次自增插入会报重复错误
· 当向包含AUTO_INCREMENT的主键列上添加0或者NULL时，数值会自动自增
· 在8.0以前，自增值是保存在内存中的，如果删除过数据后再重启数据库会发现自增值出现了回退。而如果发生主键冲突或者事务回滚，自增值则会发生不连续现象
在表结构设计时如果用自增字段做主键建议使用BIGINT，因为INT的范围最大只能达到42亿的级别，在互联网业务场景中的一些流水表、日志表，如果每天有1000W的数据量，仅420天就可以达到INT 类型的上限，到时候再进行表结构变更的代价会更大。
CREATE TABLE student(
id bigint not null primary key auto_increment
name varchar(10)
);
insert into student(name) values ('tanglu'); # id列的值默认从1开始递增
create table student(
id int not null primary key auto_increment=100 #指定自增开始值
name varchar(10) # id列默认从100开始
);
3、外键约束（FOREIGN KEY）
· 外键约束类似于子表的概念，实现表和表之间的关联
· 一张表可以有多个外键约束，外键约束需要在子表中定义，指明外键字段以及外键字段所引用的主表中的主键字段
· 外键约束分了多种工作方式（在创建时指明）
Cascade：在父表上进行数据更新时会同步更新子表相匹配的记录
Set null：在父表上进行数据更新时会将子表相匹配的记录修改为null
No action：如果子表中有相关记录则不允许父表进行更新
· MySQL 系统会根据外键约束的定义，监控对主表中数据的删除操作。如果发现要删除的主表记录正在被从表中某条记录的外键字段所引用，MySQL 就会提示错误，从而确保了关联数据不会缺失
· 由于外键约束需要消耗额外的资源，在高并发场景下可能会因为外键约束导致性能受损明显，所以在实际开发过程中外键使用得并不多。通常是在应用层面去完成这些附加逻辑实现同样的功能，确保数据的一致性
CREATE TABLE t2 (
fid INT,
phone VARCHAR(16),
address VARCHAR(50),
constraint fk_t1 FOREIEN KEY(fid) REFERENCES t1(id) #t2表的fid列为t1表id列的外键，外键名为fk_t1，其值来自t1表id列
#constraint fk_t1 FOREIEN KEY(fid) REFERENCES t1(id) ON UPDATE CASCADE ON DELETE SET NULL #把修改操作设置为级联修改，删错设置为set null
);
#删除外键约束
ALTER TABLE t2 DROP FOREIGN KEY fk_t14、非空约束（NOT NULL）
该约束可以让指定列中的值不能为空值（NULL），建议在非必要的前提下所有字段都有该约束的存在，然后为约束字段设置一个默认值来确保字段不存在NULL值。因为索引在遇到NULL时会需要额外的空间来作为保存判断标志位。在进行比较时IS NOT NULL这样的语句是不会走到索引、在进行计算时遇到NULL值返回永远为空，会使查询结果出错（但是有一些字段可能必须存在NULL以表示它的本来含义，比如一些数值字段，如果设置了默认值会让平均值的计算出现差错，设置为NULL则不会参与计算）
#建表时创建约束示例
CREATE TABLE t3 (
id INT NOT NULL,
name VARCHAR(10) NOT NOLL
);
#后期添加约束示例，需要先确保没有字段的值为NULL
ALTER TABLE t3 MODIFY email VARCHAR(20) NOT NULL
5、唯一性约束（UNIQUE KEY）
· 用于约束列中的值不能重复，比如身份证号、手机号、用户ID等信息一般是不可重复的
· 唯一约束只对有值的字段进行判断，如果字段为空是可以重复出现的
· 相比主键约束，一张表可以有多个唯一约束，唯一约束修改起来也更方便
· 添加唯一约束的字段也会自动创建唯一索引，唯一索引的名称和唯一约束名相同；多个字段可以建立复合唯一约束，如果没有自定义复合索引的名称，则使用第一个字段作为索引名
· 删除唯一约束只能通过删除唯一索引的方式来实现
#建表时创建约束示例
CREATE TABLE t4(
id INT,
certid INT UNIQUE #certid这列的值不能重复
email VARCHAR(20) UNIQUE #email列的值不能重复
);
#后期添加约束示例，需要先确保没有字段的值是重复的
ALTER TABLE t3 MODIFY email VARCHAR(20) UNIQUE
#删除唯一约束
ALTER TABLE t3 DROP INDEX email
6、检查约束（CHECK）
8.0开始支持使用，限制某个字段的值必须符合一定预期
CREATE TABLE test5( id INT, last_name VARCHAR(10), salary DECIMAL(10,2) CHECK(salary > 2000) )
7、默认值约束（DEFAULT）
用于设置约束对应列中的值的默认值。设置了默认约束以后，除非默认值就是空值，否则不能插入空值。在设置默认约束的时候支持使用函数，如default now()
create table t5(
id int primary key,
name varchar(10) not null,
sex enum('man','woman','unkown') not null default 'unkown'
);
insert into t5 values (1,'tanglu'); #不填写第三个字段的内容，则采用default的值
insert into t5 values (1,'tanglu',default,); #也可以直接写为default
8、注释（COMMENT）
用户对字段或者表进行说明，便于维护
create table student (
id int not null primary key comment '学号',
name varchar(10) not null comment '姓名'
);
9、复合主键约束（mysql服务的用户授权就采用了这种方法，同时判断多个字段的值来决定最终的结果是否重复）
create table t2 (
id int，
certid int
name varchar(10)
primary key(id,certid) #同时给2列设置主键约束，只有当2列数据都重复的时候才会报错
);
评论