初谈表的约束

南桥

发布于 2024-08-05 08:32:12

560

发布于 2024-08-05 08:32:12

文章被收录于专栏：南桥谈编程

概念

真正约束字段的是数据类型，但是数据类型约束很单一，需要有一些额外的约束，更好的保证数据的合法性，从业务逻辑角度保证数据的正确性。比如有一个字段是email，要求是唯一的。表的约束很多，这里主要介绍如下几个： null/not null,default, comment, zerofill，primarykey，auto_increment，unique key

表中一定要有各种约束，通过约束让我们未来插入数据库表中的数据是符合预期的。约束本质是通过技术手段，倒逼程序员插入正确的数据，反过来站在MySQL的视角，凡是插入进来的数据，都是符合数据约束的。因此约束的最终目标是保证数据的完整性和可预期性。

空属性

两个值：null（默认的）和not null(不为空)
数据库默认字段基本都是字段为空，但是实际开发时，尽可能保证字段不为空，因为数据为空没办法参与运算。

mysql> select NULL;
+------+
| NULL |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

mysql> select 1+NULL;
+--------+
| 1+NULL |
+--------+
|   NULL |
+--------+
1 row in set (0.00 sec)

实例：创建一个班级表，包含班级名和班级所在的教室。站在正常的业务逻辑中：如果班级没有名字，你不知道你在哪个班级如果教室名字可以为空，就不知道在哪上课

mysql> create table if not exists myclass(
    -> class_name varchar(20) not null,
    -> class_room varchar(20) not null,
    -> other varchar(20) 
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> desc myclass;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| class_name | varchar(20) | NO   |     | NULL    |       |
| class_room | varchar(20) | NO   |     | NULL    |       |
| other      | varchar(20) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
3 rows in set (0.03 sec)

上述创建了一个myclass表，其中class_name和class_room具有非空约束，not null 意味着这一列的值不允许为空，必须提供值,在添加数据的时候必须插入数据，但是other不需要.

默认值

默认值：某一种数据会经常性的出现某个具体的值，可以在一开始就指定好，在需要真实数据的时候，用户可以选择性的使用默认值。

实例：

mysql> create table if not exists t14( name varchar(20) not null, age tinyint unsigned default 18, gender char(1) default '男' );
Query OK, 0 rows affected (0.03 sec)

mysql> desc t14;
+--------+------------------+------+-----+---------+-------+
| Field  | Type             | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+-------+
| name   | varchar(20)      | NO   |     | NULL    |       |
| age    | tinyint unsigned | YES  |     | 18      |       |
| gender | char(1)          | YES  |     | 男      |       |
+--------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

age tinyint unsigned default 18: 这是定义了第二个列 age。default 18 指定了默认值为18，如果插入数据时未指定 age 的值，则会默认为18。

gender char(1) default '男': 这是定义了第三个列 gender。default '男' 指定了默认值为 ‘男’，即性别默认为男性。如果插入数据时未指定 gender 的值，则会默认为 '男'。

因此，default如果设置了，用户将来插入，有具体的数据，就用用户的，没有就用默认的。

空属性和默认值对比

default和not null不冲突，是互相补充的。如果没有明确指定一列需要插入，就用default；如果建表中，对应列默认没有设置default，无法直接插入。

mysql> create table t16( name varchar(20) not null, age tinyint default 18, gender char(1) not null default '男' );

gender char(1) not null default '男': 这是定义了第三个列 gender。char(1) 表示这是一个固定长度为1的字符类型。not null 指定这一列的值不允许为空。default '男' 指定了默认值为 ‘男’，即性别默认为男性。如果插入数据时未指定 gender 的值，则会默认为 ‘男’。不可以插入NULL，因为有一个非空属性。

我们在建表的时候，没有指定属性default、not null，但是MySQL会自动给我们加上：

mysql> create table t17(
    -> name varchar(20),
    -> age tinyint unsigned
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> show create table t17\G
*************************** 1. row ***************************
       Table: t17
Create Table: CREATE TABLE `t17` (
  `name` varchar(20) DEFAULT NULL,
  `age` tinyint unsigned DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

列描述

列描述：comment，没有实际含义，专门用来描述字段，会根据表创建语句保存，用来给程序员或DBA来进行了解。

mysql> create table if not exists t18( name varchar(20) not null comment '用户名字', age tinyint unsigned default 18 comment '用户年龄', gender char(1) default '男' comment '性别' );

mysql> show create table t18\G
*************************** 1. row ***************************
       Table: t18
Create Table: CREATE TABLE `t18` (
  `name` varchar(20) NOT NULL COMMENT '用户名字',
  `age` tinyint unsigned DEFAULT '18' COMMENT '用户年龄',
  `gender` char(1) DEFAULT '男' COMMENT '性别'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

注意：not null和defalut一般不需要同时出现，因为default本身有默认值，不会为空。

zerofill

为了理解zerofill，先观察着两个表的区别；

不添加zerofill：

mysql> desc t19;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| a     | int unsigned | NO   |     | NULL    |       |
| b     | int unsigned | NO   |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> insert into t19 (a,b) values (1,2);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t19;
+---+---+
| a | b |
+---+---+
| 1 | 2 |
+---+---+
1 row in set (0.00 sec)

修改b的属性，添加zerofill：

mysql> alter table t19 modify b int unsigned zerofill not null;
Query OK, 0 rows affected, 1 warning (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> desc t19;
+-------+---------------------------+------+-----+---------+-------+
| Field | Type                      | Null | Key | Default | Extra |
+-------+---------------------------+------+-----+---------+-------+
| a     | int unsigned              | NO   |     | NULL    |       |
| b     | int(10) unsigned zerofill | NO   |     | NULL    |       |
+-------+---------------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> select * from t19;
+---+------------+
| a | b          |
+---+------------+
| 1 | 0000000002 |
+---+------------+
1 row in set (0.00 sec)

mysql> insert into t19 values (100,200);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t19;
+-----+------------+
| a   | b          |
+-----+------------+
|   1 | 0000000002 |
| 100 | 0000000200 |
+-----+------------+
2 rows in set (0.00 sec)

这次可以看到b的值由原来的2变成0000000002，这就是zerofill属性的作用，如果宽度小于设定的宽度（这里设置的是10），自动填充0。要注意的是，这只是最后显示的结果，在MySQL中实际存储的还是2。

主键

主键：primary key用来唯一的约束该字段里面的数据，不能重复，不能为空，一张表中最多只能有一个主键；主键所在的列通常是整数类型。不意味着一个表中的主键只能添加给一列，于是有了复合主键。

创建表的时候直接在字段上指定主键：

mysql> create table if not exists test_key(
    -> id int unsigned primary key comment '学号',
    -> name varchar(20) not null
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> desc test_key;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int unsigned | NO   | PRI | NULL    |       |
| name  | varchar(20)  | NO   |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

主键约束：主键对应的字段中不能重复，一旦重复，操作失败

可以根据主键对表格内容进行增删查改：

增加：

mysql> insert into test_key values (222,'liubei');
Query OK, 1 row affected (0.01 sec)

mysql> select * from test_key;
+-----+----------+
| id  | name     |
+-----+----------+
| 111 | zhangfei |
| 222 | liubei   |
+-----+----------+
2 rows in set (0.00 sec)

查找：

mysql> select * from test_key where id=222;
+-----+--------+
| id  | name   |
+-----+--------+
| 222 | liubei |
+-----+--------+
1 row in set (0.00 sec)

修改：

mysql> update test_key set name='zhugeliang' where id=222;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test_key;
+-----+------------+
| id  | name       |
+-----+------------+
| 111 | zhangfei   |
| 222 | zhugeliang |
+-----+------------+
2 rows in set (0.00 sec)

删除主键：

语法格式：alter table 表名 drop primary key;

mysql> alter table test_key drop primary key;
Query OK, 2 rows affected (0.09 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> desc test_key;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int unsigned | NO   |     | NULL    |       |
| name  | varchar(20)  | NO   |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

当表创建好以后但是没有主键的时候，可以再次追加主键语法格式：alter table 表名 add primary key(字段列表)

mysql> alter table test_key add primary key(id);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test_key;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int unsigned | NO   | PRI | NULL    |       |
| name  | varchar(20)  | NO   |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

复合主键

mysql> create table pick_course(
    -> id int unsigned,
    -> course_id int unsigned comment '课程代码',
    -> score tinyint unsigned comment '分数',
    -> primary key (id,course_id)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> desc pick_course;
+-----------+------------------+------+-----+---------+-------+
| Field     | Type             | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+-------+
| id        | int unsigned     | NO   | PRI | NULL    |       |
| course_id | int unsigned     | NO   | PRI | NULL    |       |
| score     | tinyint unsigned | YES  |     | NULL    |       |
+-----------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)