MySQL之字段属性

GrowthDBA 2021-08-20

660

回想一下DDL章节定义表结构举的student学生表例子，定义表结构的时候，我们在student_id字段的数据类型后面添加了AUTO_INCREMENT，这就是字段的自增属性，除了自增属性外，还有一些属性我们工作中会经常用到，今天就来介绍一下常用的字段属性。

默认值

还是拿上次student学生表来举例：

CREATE TABLE `student` (
  `student_id` int NOT NULL AUTO_INCREMENT COMMENT '学生编号',
  `student_name` varchar(20) NOT NULL COMMENT '学生姓名',
  `address` varchar(100) DEFAULT NULL COMMENT '家庭住址',
  PRIMARY KEY (`student_id`)
) ENGINE=InnoDB COMMENT='学生表';

就像在DML那篇文章中所说，含有AUTO_INCREMENT的student_id字段，插入数据时可以用0或者NULL来代替具体值。同样地，我们现在有这样一个需求，比如我们招收的学生都来自于北京，当我们要录入学生信息时，想实现家庭住址字段默认插入'北京市'这个信息，该怎么办，那就轮到默认值属性出场了。格式如下：

[字段名] [字段数据类型] DEFAULT [默认值]

下面我们来实现一下上面的诉求：

[root@localhost][employee]> ALTER TABLE `student` MODIFY `address` VARCHAR(100) DEFAULT '北京市' COMMENT '家庭住址';
Query OK, 0 rows affected (1.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

我们通过DDL语句调整了字段address的默认值，再来看一下表结构和目前的表中的数据。

插入一条数据后看看默认值是不是起了作用：

[root@localhost][employee]> INSERT INTO `student`(`student_id`, `student_name`) VALUES(0,'王子');
Query OK, 1 row affected (0.02 sec)

我们的插入语句并没有指定address的值，但是可以看到插入结果是按照我们规定的默认值'北京市'来设置的。
如果我们不设置默认值，其实就相当于指定的默认值为NULL，比如student表添加一个extra额外信息字段且没有设置该字段的默认值，那它的默认值就是NULL。

小提示

细心的你可能会发现一个现象，使用DDL语句调整字段的默认值时，不会对表中已有数据做修改，并且新增字段也不会对原始数据做修改，插入数据如果指定具体值的话，会按照指定的值进行保存：

由此观之，调整字段默认值、新增有/无默认值字段操作不会对原始数据对应的字段数据造成影响，默认值只对执行完DDL语句之后的新数据生效，如果指定具体值，会按照指定的值进行数据插入。

NOT NULL属性

有时候我们需要要求表中的某些字段中必须有值，不能存放NULL值，那么可以用这样的语法来定义这个字段：

[字段名] [字段数据类型] NOT NULL

其实我们的student表的student_name字段就是不允许为空的：

CREATE TABLE `student` (
  `student_id` int NOT NULL AUTO_INCREMENT COMMENT '学生编号',
  `student_name` varchar(20) NOT NULL COMMENT '学生姓名',
  `address` varchar(100) DEFAULT NULL COMMENT '家庭住址',
  PRIMARY KEY (`student_id`)
) ENGINE=InnoDB COMMENT='学生表';

所以，我们就不能往这个字段里插入NULL值：

[root@localhost][employee]> INSERT INTO `student`(`student_id`, `student_name`, `address`, `extra`) VALUES(30002,NULL,'上海市陆家嘴','交换生');
ERROR 1048 (23000): Column 'student_name' cannot be null

看到报了个错，提示student_name字段不能存储NULL值。

另外，一旦对某个字段定义了NOT NULL属性，那这个字段的默认值就不允许为NULL了。同时student_name字段也没有指定默认值，意味着我们在使用INSERT插入行时必须显式的指定这个字段的值，而不能省略它，比如这样就会报错的：

[root@localhost][employee]> INSERT INTO `student`(`student_id`, `address`, `extra`) VALUES(30003,'上海市徐汇区','交换生');
ERROR 1364 (HY000): Field 'student_name' doesn't have a default value

所以没有设置默认值的字段，在使用INSERT语句插入记录的时候不能省略掉这个字段的值。

主键

有时候在我们的表里可以通过某个字段或者某些字段确定唯一的一条记录，我们就可以把这个字段或者这些字段称为候选键。比如在学生表student中，只要我们知道某个学生的学生编号，就可以确定一个唯一的学生信息，也就是一条记录。一个表可能有多个候选键，我们可以选择一个候选键作为表的主键
。一个表最多只能有一个主键，主键的值不能重复，通过主键可以找到唯一的一条记录。如果我们的表中有定义主键的需求可以选用下边这两种方式之一来指定主键：

1、如果主键只是单个字段的话，可以直接在该字段后声明PRIMARY KEY：

CREATE TABLE `student` (
  `student_id` INT(11) PRIMARY KEY COMMENT '学生编号',
  ...

2、我们也可以把主键的声明单独提取出来：

CREATE TABLE `student` (
  `student_id` int NOT NULL AUTO_INCREMENT COMMENT '学生编号',
  ...
  PRIMARY KEY (`student_id`)
);

在我们创建表的时候就声明了主键的话，MySQL会对我们插入的记录做校验，如果新插入记录的主键值已经在表中存在了，那就会报错。另外，主键字段默认是有NOT NULL属性。

UNIQUE属性

对于不是主键的其他候选键，如果也想让MySQL在我们向表中插入新记录的时候帮助我们校验一下某个字段或者字段组合的值是否重复，那么我们可以把这个字段或字段组合添加一个UNIQUE属性，表明该字段或者字段组合的值是不允许重复的。与我们在建表语句中声明主键的方式类似，为某个字段声明UNIQUE属性的方式也有两种：

1、为单个字段声明UNIQUE属性，可以直接在该字段后填写UNIQUE或者UNIQUE KEY：

CREATE TABLE `student` (
  ...
  `student_name` varchar(20) UNIQUE/UNIQUE KEY COMMENT '学生姓名',
  ...

2、我们也可以把UNIQUE属性的声明单独提取出来，用这样的形式声明：

UNIQUE [约束名称] (字段名1, 字段名2, ...)
UNIQUE KEY [约束名称] (字段名1, 字段名2, ...)

为某个字段添加了一个UNIQUE属性，从此我们插入的记录的该字段的值就不能重复，所以为某个字段添加一个UNIQUE属性也可以认为是为这个表添加了一个约束，我们就称之为UNIQUE约束。每个约束都可以有一个名字，像主键也算是一个约束，它的名字就是默认的PRIMARY。不过一个表中可以为不同的字段添加多个UNIQUE属性，也就是添加多个UNIQUE约束，每添加一个UNIQUE约束，我们就可以给它起个名，这也是上边的约束名称的含义。不过约束名称是被中括号[]扩起来的，意味着我们写不写都可以，如果不写的话MySQL自己会帮我们起名。值得注意的是，对于多个字段的组合具有UNIQUE
属性的情况，必须使用这种单独声明的形式。如果表中为某个字段或者字段组合定义了UNIQUE属性的话，MySQL会对我们插入的记录做校验，如果新插入记录在该字段或者字段组合的值已经在表中存在了，那就会报错。同时还有一点需要注意，如果设置了UNIQUE属性，最好配合NOT NULL属性一起使用，因为MySQL会认定多个NULL值是不重复的。

外键

插入到学生成绩表student_score中的student_id学生编号字段中的值必须能在学生表student中的student_id字段中找到，否则如果一个学生编号只在成绩表里出现，而在基本信息表里找不到相应的记录的话，就相当于插入了不知道是哪个学生的成绩，这显然是荒谬的。为了防止这样荒谬的情况出现，MySQL给我们提供了外键约束机制。定义外键的语法是这样的：

CONSTRAINT [外键名称] FOREIGN KEY(字段1, 字段2, ...) REFERENCES 父表名(父字段1, 父字段2, ...);

其中的外键名称也是可选的，一个名字而已，我们不自己命名的话，MySQL自己会帮助我们命名。如果A表中的某个字段或者某些字段依赖与B表中的某个字段或者某些字段，那么就称A表为子表，B表为父表。子表和父表可以使用外键来关联起来，student_score表的student_id字段依赖于student的student_id字段，所以student就是一个父表，student_score就是子表。我们可以在student_score的建表语句中来定义一个外键：

CREATE TABLE `student_score` (
  `student_id` INT(11) NOT NULL COMMENT '学生编号',
  `subject` VARCHAR(20) NOT NULL COMMENT '学科名',
  `score` TINYINT DEFAULT NULL COMMENT '成绩',
  PRIMARY KEY (`student_id`,`subject`),
  CONSTRAINT FOREIGN KEY(`student_id`) REFERENCES `student`(`student_id`)
) ENGINE=InnoDB COMMENT='学生成绩表';

这样，在对student_score表插入数据的时候，MySQL都会为我们检查一下插入的学生编号是否能在student表中找到，如果找不到则会报错。注意：父表中被子表依赖的字段或者字段组合必须建立索引，如果该字段或者字段组合已经是主键或者有UNIQUE属性，那么它们也就被默认建立了索引。

AUTO_INCREMENT属性

AUTO_INCREMENT翻译成中文可以理解为自动增长，简称自增。如果一个表中的某个字段的数据类型是整数类型或者浮点数类型，那么这个字段可以设置AUTO_INCREMENT属性。当我们把某个字段设置了AUTO_INCREMENT属性之后，如果我们在插入新记录的时候不指定该字段的值，或者将该字段的值显式地指定为NULL或者0，那么新插入的记录在该字段上的值就是当前该字段的最大值加1后的值。

[字段名] [字段数据类型] AUTO_INCREMENT

这个属性我们已经见过很多次了，还是有一些注意事项需要注意：

1、一个表中最多有一个具有AUTO_INCREMENT属性的字段。

2、具有AUTO_INCREMENT属性的字段必须建立索引。主键和具有UNIQUE属性的字段会自动建立索引。

3、拥有AUTO_INCREMENT属性的字段就不能再通过指定DEFAULT属性来指定默认值。

4、一般拥有AUTO_INCREMENT属性的字段都是作为主键的属性，来自动生成唯一标识一条记录的主键值。

字段的注释

建表语句的末尾可以添加COMMENT语句来给表添加注释，也可以在每一个字段末尾添加COMMENT语句来为字段来添加注释：

ZEROFILL属性

对于无符号整数类型的字段，我们可以在查询数据的时候让数字左边补0，如果想实现这个效果需要给该字段加一个ZEROFILL属性（也可以理解为这是一个属于数据类型的属性），直接上测试数据：

CREATE TABLE `zerofill_table` (
  `columns1` INT(0) UNSIGNED ZEROFILL,
  `columns2` INT(1) UNSIGNED ZEROFILL,
  `columns3` INT(2) UNSIGNED ZEROFILL,
  `columns4` INT(3) UNSIGNED ZEROFILL,
  `columns5` INT(4) UNSIGNED ZEROFILL,
  `columns6` INT(5) UNSIGNED ZEROFILL,
  `columns7` INT(6) UNSIGNED ZEROFILL,
  `columns8` INT(7) UNSIGNED ZEROFILL,
  `columns9` INT(11) UNSIGNED,
  `columns10` INT UNSIGNED,
  `columns11` INT UNSIGNED ZEROFILL
) ENGINE=InnoDB COMMENT='ZEROFILL测试表';

INSERT INTO `zerofill_table` VALUES(8,8,8,8,8,8,8,8,8,8,8);

看一下表中的数据：

类型INT后边都加了一个()，()里面的数字就是所谓的显示宽度。显示宽度是在查询语句显示的结果中，如果声明了ZEROFILL属性的整数字段的实际值的位数小于显示宽度时，会在实际值的左侧补0，使补0的位数和实际值的位数相加正好等于显示宽度。
但是，有几点需要注意：

‍‍‍‍‍‍‍该字段必须是整数类型的；
该字段必须有UNSIGNED ZEROFILL的属性；
该字段的实际值的位数必须小于显示宽度；
在创建表的时候，如果声明了ZEROFILL属性的字段没有声明UNSIGNED属性，那MySQL会为该字段自动生成UNSIGNED属性；
每个整数类型都会有默认的显示宽度。比如TINYINT的默认显示宽度是4，INT的默认显示宽度是(11)... 如果加了UNSIGNED属性，则该类型的显示宽度减1，比如TINYINT UNSIGNED的显示宽度是3，INT UNSIGNED的显示宽度是10；
显示宽度并不会影响实际类型的实际存储空间。显示宽度仅仅是在展示查询结果时，如果整数的位数不够显示宽度的情况下起作用的，并不影响该数据类型要求的存储空间以及该类型能存储的数据范围，也就是说INT(1)和INT(10)仅仅在展示时可能有区别，在别的方面没有任何区别；
只有字段的实际值的位数小于显示宽度时才会补0，实际值的位数大于显示宽度时照原样输出；
只有在查询声明了ZEROFILL属性的字段时，显示宽度才会起作用，否则忽略显示宽度这个东西的存在；
有UNSIGNED ZEROFILL属性的INT(0)和INT，最终显示效果一样。

小提示

注意，有的属性是冲突的，一个列不能具有两个冲突的属性，。如一个列不能既声明为PRIMARY KEY，又声明为UNIQUE KEY，不能既声明为DEFAULT NULL，又声明为NOT NULL。大家在使用过程中需要注意这一点。
一张表中只能定义一个主键，却可以定义多个UNIQUE约束。
规定：主键字段不允许存放NULL，而声明了UNIQUE属性的字段可以存放NULL，而且NULL可以重复地出现在多条记录中。

小结

通过今天的学习，我们掌握了字段属性的设置方法和一些注意事项。字段属性在做数据库表结构设计时还是很有用的，小提示中的内容同样重要。本文主要是在小孩子4919大佬的文章基础上做了测试和知识点的补充，写文章的同时，也梳理了我的知识库。希望今天的内容可以帮助到大家。大道至简，贵在坚持，共勉！

参考资料

* 小孩子4919《MySQL是怎样使用的：从零蛋开始学习MySQL》

end

mysql

文章转载自GrowthDBA，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL之字段属性

评论