相关推荐recommended
MySQL的三种属性约束(性别默认男女),简单易懂
作者:mmseoamin日期:2023-12-13

1、DEFAULT:默认值约束

比如当插入一些数据为空或者没有插入数据的时候,我们可以给一个默认值

mysql> CREATE TABLE students (
    ->   no INT,
    ->   name VARCHAR(32),
    ->   sex CHAR(1) DEFAULT '男',
    ->   age INT(3),
    ->   email VARCHAR(255)
    -> );
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> desc students
    -> ;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| no    | int          | YES  |     | NULL    |       |
| name  | varchar(32)  | YES  |     | NULL    |       |
| sex   | char(1)      | YES  |     | 男      |       |
| age   | int          | YES  |     | NULL    |       |
| email | varchar(255) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

插入语句进行验证:

mysql> insert into students(no,name,age,email) values(1,'www',18,'123@qq.com');
Query OK, 1 row affected (0.01 sec)
mysql> select * from students;
+------+------+------+------+------------+
| no   | name | sex  | age  | email      |
+------+------+------+------+------------+
|    1 | www  | 男   |   18 | 123@qq.com |
+------+------+------+------+------------+
1 row in set (0.00 sec)
mysql> insert into students(no,name,sex,age,email) values(2,'zzz','女',18,'123@qq.com');
Query OK, 1 row affected (0.00 sec)
mysql> select * from students;
+------+------+------+------+------------+
| no   | name | sex  | age  | email      |
+------+------+------+------+------------+
|    1 | www  | 男   |   18 | 123@qq.com |
|    2 | zzz  | 女   |   18 | 123@qq.com |
+------+------+------+------+------------+
2 rows in set (0.00 sec)

 (如下同理)

2、使用ENUM类型限制字段取值

ENUM是一个字符串对象,其值是从列创建时定义的允许值列表中选择的

CREATE TABLE student (
  name VARCHAR(32),
  sex ENUM('男', '女'),
  age INT(3),
  email VARCHAR(255)
);

3、CHECK 检查约束
注意:(CHECK 约束:用于限制列中的值的范围,MySQL5.7不支持该约束,但写入语句不会报错,MySQL8.0版本支持该约束。)

create table test_user
(
id int primary key,
name VARCHAR(20),
sex VARCHAR(1),
check (sex='男'or sex='女')
);