02.MySQL DDL的知识点总结

---

前言

在设计数据库的时候，究竟什么是好的原则？我们在创建数据表的时候需要注意什么？

---

一、CREATE TABLE

DROP TABLE IF EXISTS `player`;
CREATE TABLE `player`  (
  `player_id` int(11) NOT NULL AUTO_INCREMENT,
  `team_id` int(11) NOT NULL,
  `player_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `height` float(3, 2) NULL DEFAULT 0.00,
  PRIMARY KEY (`player_id`) USING BTREE,
  UNIQUE INDEX `player_name`(`player_name`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

1. 里面的数据表和字段都使用了反引号，这是为了避免它们的名称与 MySQL 保留字段相同，对数据表和字段名称都加上了反引号。
2. player_name 字段的字符集是 utf8，排序规则是utf8_general_ci，代表对大小写不敏感，如果设置为utf8_bin，代表对大小写敏感。
3. // TODO 介绍BTREE和HASH索引方式的区别。

二、ALTER TABLE

ADD 添加字段

ALTER TABLE player ADD (age int(11));

RENAME 重命名字段

ALTER TABLE player RENAME COLUMN age to player_age;

MODIFY 修改字段

ALTER TABLE player MODIFY (player_age float(3,1));

DROP 删除字段

ALTER TABLE player DROP COLUMN player_age;

数据表的常见约束

主键约束

即 UNIQUE+NOT NULL。一个数据表的主键只能有一个。主键可以是一个字段，也可以由多个字段复合组成。

外键约束

外键确保了表与表之间引用的完整性。一个表中的外键对应另一张表的主键。外键可以是重复的，也可以为空。

唯一性约束

除了对键进行约束外，还有字段约束。
唯一性约束和普通索引（NORMAL INDEX）之间是有区别的。唯一性约束相当于创建了一个约束和普通索引，目的是保证字段的正确性，而普通索引只是提升数据检索的速度，并不对字段的唯一性进行约束。

NOT NULL 约束

略。

DEFAULT

比如我们将身高 height 字段的取值默认设置为 0.00，即DEFAULT 0.00。

CHECK 约束

用来检查特定字段取值范围的有效性，CHECK 约束的结果不能为 FALSE，比如我们可以对身高 height 的数值进行 CHECK 约束，必须≥0，且＜3，即CHECK(height>=0 AND height<3)。

设计数据表的原则

1. 数据表的个数越少越好

数据表越少，说明实体和联系越简洁，即方便理解又方便操作。

2. 数据表中的字段个数越少越好

字段个数越多，就越可能导致数据冗余。能通过其它字段算出来的值，不必单独设置字段。

也要强调：字段个数少是相对的，我们通常在数据冗余和检索效率之间做取舍。为了效率，适当的增加冗余也是可取的。

3. 数据表中联合主键的字段个数越少越好

联合主键中的字段越多，占用的索引空间越大，加大理解难度，增加运行时间和索引空间，因此联合主键的字段个数越少越好。

4.使用主键和外键越多越好

关系越多，证明这些实体之间的冗余度越低，利用度越高。这样做的好处在于不仅保证了数据表之间的独立性，还能提升相互之间的关联使用率。

注意：外键越多，维护越难。外键的使用是有争议的。应做取舍。

首先，外键本身是为了实现强一致性，所以如果需要 正确性 > 性能 的话，还是建议使用外键，它可以让我们在数据库的层面保证数据的完整性和一致性。
当然不用外键，你也可以在业务层进行实现。不过，这样做也同样存在一定的风险，因为这样，就会让业务逻辑会与数据具备一定的耦合性。也就是业务逻辑和数据必须同时修改。而且在工作中，业务层可能会经常发生变化。
当然，很多互联网的公司，尤其是超大型的数据应用场景，大量的插入，更新和删除在外键的约束下会降低性能，同时数据库在水平拆分和分库的情况下，数据库端也做不到执行外键约束。另外，在高并发的情况下，外键的存在也会造成额外的开销。因为每次更新数据，都需要检查另外一张表的数据，也容易造成死锁。
所以在这种情况下，尤其是大型项目中后期，可以采用业务层来实现，取消外键提高效率。

建议：初学时和项目早期加上外键约束。项目后期监控性能消耗再做优化。

---

总结