MySQL进阶

第一章、名词解释

第二章、数据类型

2.1、数据类型简介

2.2、数据类型对应属性

2.3、整数类型

• 宽度

CREATE TABLE test_int(
f1 INT,
f2 INT(5),
f3 INT(5) ZEROFILL
);

INSERT INTO test_int(f1,f2) 
VALUES(123,123),(123456,123456);

INSERT INTO test_int(f3) 
VALUES(123),(123456);

SELECT * FROM test_int;
1
2
3
4
5
6
7
8
9
10
11
12
13

1.显示宽度在有ZEROFILL时才有用，如果数据宽度 < 字段定义宽度，则前面使用0填充。
2.除此之外，及时定义了字段宽度，只要数据不超过字段类型本身的范围，就可以成功插入表中。
3.从MySQL8.0.17开始，整数数据类型不推荐使用显示宽度属性。

• 适用场景

• 选择方法
  考虑存储空间和可靠性的平衡问题，要是为了节省存储空间，使用的整数类型取值范围太小，一旦遇到超出取值范围的情况，可能引起系统错误，影响可靠性。实际上，系统故障产生的成本远远超过增加几个字段存储空间所产生的成本。

2.4、浮点类型

• 为什么MySQL的浮点数类型会存在误差？

在一些对精度要求较高的项目中，千万不要使用浮点数。MySQL提供了精准数据类型：DECIMAL。

2.5、定点数类型

1.定义DECIMAL(5,2)的类型，表示该列取值范围为-999.99~999.99。
2.定点数在MySQl里是以字符串的形式存储的，这就决定了它一定是精准的。
3.如果定点数类型不指定精度和标度时，默认为（10,0）。
4.如果数据的精度超出定点数的数据精度范围时，MySQl同样会进行四舍五入处理。

2.6、日期和时间类型

• TIME

使用CURRENT_TIME()或NOW()函数，会插入当前系统的日期。

• DATE

使用CURRENT_DATE()或NOW()函数，会插入当前系统的日期。

• DATETIME,TIMESTAMP
  
  使用CURRENT_TIMESTAMP()或NOW()函数，会插入当前系统的日期时间。
• 开发经验
  

2.7、文本字符串类型

2.7.1、CHAR和VARCHAR区别

1.CHAR

• 如果保存时，数据实际长度比声明的长度小，则会在右侧填充空格以达到指定长度。
• 当MySQL检索CHAR类型的数据时，CHAR类型的数据会去除尾部的空格。

2.VARCHAR

• VARCHAR(M)定义时，必须指定长度M，否则报错。
• MySQL4.0版本以下，varchar(20)指的是20字节，如果存放UTF8汉字，只能存6个（每个汉字三个字节）。MySQL5.0版本以上，varchar(20)指的是20个字符。
• 检索VARCHAR类型的字段数据时，会保留数据尾部的空格。

3.开发经验

2.7.2、TEXT

• 在向TEXT类型的字段保存和查询数据时，系统自动按照实际长度存储，不需要预先定义长度。
• 由于实际存储的长度不确定，MySQL不允许TEXT类型的字段做主键。

2.8、小结和建议

第三章、约束

3.1 约束的定义

约束是对表中字段的限制。

约束按照作用范围可以分为：列级约束和表级约束
列级约束：声明在对应字段的后面
表级约束：在表中所有字段都声明完，在所有字段的后面声明

3.2 非空约束

1.作用
限定某个字段/列的值不为空

2.关键字

NOT NULL
1

3.特点

• 所有类型的值都可以为NULL，包括INT，FLOAT等数据类型
• 非空约束只能出现在表对象的列上，只能某个列单独限制为非空，不能组合非空
• ‘’空字符串不等同于NULL，0也不是NULL

3.3 唯一性约束

1.作用
限制某个字段/列的值不能重复，允许出现多个空值：NULL

2.关键字

UNIQUE
1

3.特点

• 同一个表可以有多个唯一约束
• 唯一约束可以是某个列的值唯一，也可以是多个列组合的值唯一
• 唯一性约束允许列值为空
• 在创建唯一约束的时候，如果没有给唯一约束命名，则默认和列名相同
• MySQL会给唯一约束的列上默认创建一个唯一索引

4.举例

CREATE TABLE user（
  id INT,
  user_name VARCHAR(15),
  user_password VARCHAR(25),

  // 表级约束，复合的唯一索引
  CONSTRAINT uk_user_name_password UNIQUE(user_name,user_password)
）
1
2
3
4
5
6
7
8

3.4 主键约束

1.作用
用来唯一标识表中的一行记录

2.关键字

PRIMARY KEY
1

3.特点

• 主键约束相当于唯一约束和非空约束的组合，主键约束列不允许重复，也不允许出现空值
• 一个表最多只能有一个主键约束，建立主键约束可以在列级别创建，也可在表级别上创建
• 主键约束对应着表中的一列或多列（复合主键）
• 如果是多列组合的复合主键约束，那么这些列都不允许为空值，并且组合的值不允许重复
• 当创建主键约束时，系统默认会在所在列或列组合上建立对应的主键索引（能够根据主键查询的，就根据主键查询，效率更高）。如果删除主键约束了，主键约束对应的索引就自动删除了。
• 需要注意的一点是，不要修改主键字段的值。因为主键是数据记录的唯一标识，如果修改了主键的值，就有可能会破坏数据的完整性。

3.5 自增列

1.作用
某个字段的值自增

2.关键字

AUTO_INCREMENT
1

3.特点和要求

• 一个表最多只能有一个自增列
• 当需要产生唯一标志或顺序值时，可设置自增长
• 自增长列约束的列必须是键列（主键或唯一键列）
• 自增约束的列的数据类型必须是整数类型

4.新特性

CREATE TABLE test(
 id INT PRIMARY KEY AUTO_INCREMENT
);

INSERT INTO test(id)
VALUES(0),(0);

SELECT * FROM test;

DELETE FROM test WHERE id = 2;

INSERT INTO test(id)
VALUES(0);
1
2
3
4
5
6
7
8
9
10
11
12
13

此时再将id=3的记录删除，重启服务器，在MySQL5.7版本中再新增数据，id将从2开始，而MySQL8.0版本将从4开始。

• 在MySQL5.7系统中，对于自增主键的分配规则，是由InnoDB数据字典内部一个计数器来决定的，而该计数器只在内存中维护，并不会持久化到磁盘中。当数据库重启时，该计数器会被初始化。
• MySQL8.0将自增主键的计数器持久化到重做日志中。每次计数器发生变化，都会将其写入到重做日志中，如果数据库重启，InnoDB会根据日志中的信息来初始化计数器的内存值。

3.6 外键约束

1.作用
限定某个表的某个字段的引用完整性。
比如：员工表的员工所在部门的选择，必须在部门表能找到对应部分。

2.关键字

FOREIGN KEY
1

3 .主表和从表/父表和子表
主表(父表)︰被引用的表，被参考的表
从表（子表)︰引用别人的表，参考别人的表

4.特点

• 从表的外键列，必须引用/参考主表的主键列或唯一约束列，因为被依赖/参考的值必须是唯一的
• 在从表中指定外键约束，并且一个表可以建立多个外键约束
• 从表的外键列与主表被参照的列名字可以不相同，但是数据类型必须一样，逻辑意义必须一致
• 当创建外键约束时，系统默认会在所在列上建立对应的普通索引（根据外键查询效率很高）

5.使用示例

create table 主表名称（
  字段1 数据类型 primary key,
  字段2 数据类型 
）;

create table 从表名称（
  字段1 数据类型 primary key,
  字段2 数据类型,
  CONSTRAINT 外键约束名 FOREIGN KEY(从表的某个字段) references 主表名（被参考字段） 
）;
1
2
3
4
5
6
7
8
9
10

6.注意事项

在MysQL里，外键约束是有成本的，需要消耗系统资源。对强大并发的SQL操作，有可能会不适合。比如大型网站的中央数据库，可能会因为外键约束的系统开销而变得非常慢。所以，MySQL允许你不使用系统自带的外键约束，在应用层面完成检查数据一致性的逻辑。也就是说，即使你不用外键约束，也要想办法通过应用层面的附加逻辑，来实现外键约束的功能，确保数据的一致性。

7.阿里开发规范

【强制】不得使用外键与级联，一切外键概念必须在应用层解决。

说明:（概念解释）学生表中的student_id是主键，那么成绩表中的student_id则为外键。如果更新学生表中的studrnt_id，同时触发成绩表中的student_id更新，即为级联更新。外键与级联更新适用于单机低并发，不适合分布式、高并发集群﹔级联更新是强阻塞，存在数据库更新风暴的风险;外键影响数据库的插入速度。

3.7 CHECK约束

1.作用
检查某个字段的值是否符合xx要求，一般指的是值的范围（比如salary > 2000 ）。
2.关键字

CHECK
1

3.说明
MySQL5.7 可以使用check约束，但是check约束对数据验证没有任何作用。添加数据时，没有任何错误或警告，但是MySQL8.0 可以使用check约束。

3.8 DEFAULT约束

1.作用
给某个字段/列指定默认值，一旦设置默认值，在插入数据时，如果此字段没有显式赋值，则赋值为默认值。

2.关键字

DEFAULT
1

3.说明
为什么建表时，加not null default'' 或 default 0?
答：不想让表中出现null值。

• 不好比较，null是一种特殊值，比较时只能用专门的is null和is not null来比较。碰到运算符，通常返回null；
• 效率不高。影响提高索引效果；