 | NULL | default | zerofill | 主键 | 自增长)
MySQL 表约束详解从建表到实操验证本文面向 MySQL 初学者通过可以直接执行的示例理解常见表约束。。文章目录MySQL 表约束详解从建表到实操验证一、什么是表约束二、准备实验环境三、空属性null 和 not null3.1 基本语法3.2 创建班级表四、默认值default4.1 基本语法4.2 实操示例五、列描述comment5.1 基本语法5.2 实操示例六、显示宽度与 zerofill6.1 基本语法6.2 实操示例七、主键primary key7.1 主键特点7.2 单列主键7.3 复合主键八、自增长auto_increment8.1 基本语法8.2 实操示例九、唯一键unique9.1 基本语法9.2 实操示例十、外键foreign key10.1 基本语法10.2 实操示例十一、索引index11.1 什么是索引11.2 创建和使用索引十二、综合建表示例十三、约束对比速查十四、常见问题14.1 default 和 not null 是否冲突14.2 主键和唯一键有什么区别14.3 建立索引后查询一定更快吗总结一、什么是表约束表约束是数据库对字段数据制定的规则。插入或修改数据时MySQL 会检查数据是否符合这些规则。本质通过技术手段限制错误数据进入数据库。目的保证数据的完整性、一致性和可预期性。常见约束not null、default、primary key、auto_increment、unique和foreign key。使用原则能由数据库保证的数据规则不应只依赖程序代码判断。二、准备实验环境先创建本次练习使用的数据库createdatabaseifnotexistsconstraint_demodefaultcharactersetutf8mb4;useconstraint_demo;执行效果Query OK, 1 row affected Database changedif not exists可以避免数据库已存在时直接报错。utf8mb4能够完整保存中文、特殊符号和表情。后续语句默认都在constraint_demo数据库中执行。三、空属性null 和 not null3.1 基本语法字段名 数据类型null字段名 数据类型notnullnull表示允许字段没有值也是字段的默认空属性。not null表示字段必须有值。null不等于数字0也不等于空字符串。实际开发中能确定必填的字段建议设置为not null。3.2 创建班级表createtableclass(idintprimarykey,class_namevarchar(30)notnull,classroomvarchar(30)null);插入正确数据insertintoclassvalues(1,java一班,a101);insertintoclass(id,class_name)values(2,java二班);select*fromclass;执行效果idclass_nameclassroom1java一班a1012java二班nullclass_name是必填字段因此不能省略。classroom允许为空省略后保存为null。插入错误数据insertintoclass(id,classroom)values(3,a102);执行效果ERROR 1364 (HY000): Field class_name doesnt have a default value该语句没有提供class_name。class_name使用了not null所以 MySQL 拒绝插入。四、默认值default4.1 基本语法字段名 数据类型default默认值插入数据时省略该字段MySQL 会自动使用默认值。用户显式传入数据时传入值优先于默认值。default和not null不冲突两者作用不同。not null检查值是否为空default负责在字段被省略时补值。4.2 实操示例createtablestudent(idintprimarykey,namevarchar(30)notnull,gendervarchar(10)notnulldefault未知,scoredecimal(5,2)default0.00);insertintostudent(id,name)values(1,张三);insertintostudentvalues(2,李四,男,92.50);select*fromstudent;执行效果idnamegenderscore1张三未知0.002李四男92.50第一条数据省略了gender和score因此使用默认值。第二条数据提供了完整内容因此保存用户传入的值。gender同时设置not null和default能够避免保存空值并在省略时自动补值。五、列描述comment5.1 基本语法字段名 数据类型comment字段说明comment用于说明字段含义不会限制字段中的数据。desc 表名通常看不到完整注释。可以使用show create table 表名查看建表语句和注释。良好的字段注释能够降低后续维护成本。5.2 实操示例createtableproduct(idintprimarykeycomment商品编号,namevarchar(50)notnullcomment商品名称,pricedecimal(10,2)notnullcomment商品价格单位为元)comment商品信息表;showcreatetableproduct;执行效果CREATE TABLE product ( id int NOT NULL COMMENT 商品编号, name varchar(50) NOT NULL COMMENT 商品名称, price decimal(10,2) NOT NULL COMMENT 商品价格单位为元, PRIMARY KEY (id) ) COMMENT商品信息表字段注释紧跟在字段定义后面。表注释写在右括号后面。MySQL 返回的建表语句可能会根据版本补充字符集等信息。六、显示宽度与 zerofill6.1 基本语法字段名int(5)zerofillint(5)中的5曾表示显示宽度不表示只能保存 5 位整数。zerofill会在查询显示时用0补足宽度。数据库内部保存的仍然是原始数值而不是带前导零的字符串。zerofill在 MySQL 8.0.17 起已废弃不建议在新项目中使用。6.2 实操示例createtableold_order_demo(order_noint(5)zerofill);insertintoold_order_demovalues(1),(25),(123456);select*fromold_order_demo;旧版本 MySQL 中的显示效果order_no0000100025123456数值宽度不足 5 位时查询结果在左侧补0。数值超过 5 位时不会被截断。新项目需要固定长度编号时建议由程序格式化或使用字符类型保存具有业务意义的编号。七、主键primary key7.1 主键特点主键用于唯一标识表中的一条记录。主键值不能重复也不能为null。一张表只能有一个主键约束。一个主键可以只包含一列也可以包含多列。主键通常选用没有业务含义、不会变化的整数编号。7.2 单列主键createtableuser_account(idintprimarykey,usernamevarchar(30)notnull);insertintouser_accountvalues(1,alice);insertintouser_accountvalues(2,bob);select*fromuser_account;执行效果idusername1alice2bob尝试插入重复主键insertintouser_accountvalues(1,carol);执行效果ERROR 1062 (23000): Duplicate entry 1 for key user_account.PRIMARY第一条编号为1的记录已经存在。主键约束阻止了重复编号进入数据表。7.3 复合主键createtablestudent_course(student_idint,course_idint,scoredecimal(5,2),primarykey(student_id,course_id));insertintostudent_coursevalues(1,101,90.00),(1,102,88.00),(2,101,95.00);复合主键由student_id和course_id共同组成。单独一列可以重复但两列的组合不能重复。例如(1, 101)再次出现时才会触发主键冲突。八、自增长auto_increment8.1 基本语法字段名 整数类型primarykeyauto_increment插入数据时不提供自增长字段MySQL 会生成新编号。新编号通常是在当前计数器基础上加1。自增长字段必须是整数类型并且必须建立索引。一张表通常只有一个自增长字段并将它作为主键。删除记录后MySQL 通常不会自动重复使用已经生成的编号。8.2 实操示例createtablearticle(idbigintprimarykeyauto_increment,titlevarchar(100)notnull,created_atdatetimenotnulldefaultcurrent_timestamp);insertintoarticle(title)values(mysql表约束);insertintoarticle(title)values(mysql索引基础);select*fromarticle;执行效果idtitlecreated_at1mysql表约束执行时的当前时间2mysql索引基础执行时的当前时间两条插入语句都没有提供id。MySQL 自动为记录分配了不同的主键值。current_timestamp会在插入记录时生成当前日期时间。九、唯一键unique9.1 基本语法字段名 数据类型unique唯一键保证字段中的非空值不能重复。一张表可以有多个唯一键。唯一键通常用于用户名、手机号、邮箱等业务上不能重复的字段。MySQL 的唯一键通常允许出现多个null因此必填字段应同时添加not null。9.2 实操示例createtablemember(idbigintprimarykeyauto_increment,usernamevarchar(30)notnullunique,phonevarchar(20)notnullunique);insertintomember(username,phone)values(alice,13800000001);insertintomember(username,phone)values(bob,13800000002);尝试插入重复手机号insertintomember(username,phone)values(carol,13800000001);执行效果ERROR 1062 (23000): Duplicate entry 13800000001 for key member.phone用户名和手机号分别具有唯一性。新记录的手机号与已有记录重复所以插入失败。十、外键foreign key10.1 基本语法foreignkey(当前表字段)references父表(父表字段)外键用于维护两张表之间的引用关系。子表外键值必须在父表中存在或者在允许时使用null。父表被引用的字段必须是主键或唯一键。外键能够保证引用完整性但也会增加写入和维护成本。10.2 实操示例createtabledepartment(idintprimarykeyauto_increment,namevarchar(30)notnullunique);createtableemployee(idintprimarykeyauto_increment,namevarchar(30)notnull,department_idint,constraintfk_employee_departmentforeignkey(department_id)referencesdepartment(id));insertintodepartment(name)values(研发部);insertintoemployee(name,department_id)values(小王,1);尝试引用不存在的部门insertintoemployee(name,department_id)values(小李,99);执行效果ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails当前没有编号为99的部门。外键阻止员工引用不存在的部门。真实项目还需要根据业务设计on delete和on update策略。十一、索引index11.1 什么是索引索引是一种帮助 MySQL 快速查找数据的数据结构可以理解为书籍的目录。优点减少扫描的数据量提高查询速度。代价索引需要占用磁盘空间和内存。影响新增、修改和删除数据时索引也需要同步维护。原则索引不是越多越好应根据实际查询条件创建。补充主键、唯一键和外键属于约束普通索引主要用于提升查询性能。11.2 创建和使用索引createindexidx_article_titleonarticle(title);showindexfromarticle;explainselect*fromarticlewheretitlemysql表约束;执行效果重点观察字段可能的结果含义keyidx_article_title实际使用的索引typeref通过索引查找匹配记录rows较小的数字预计需要检查的记录数create index用于创建普通索引。show index可以查看表中已有索引。explain用于分析查询计划不能只根据“创建成功”判断索引是否有效。示例数据量很小时优化器可能选择全表扫描这是正常现象。十二、综合建表示例下面把常用约束组合到一张用户表中createtableapp_user(idbigintprimarykeyauto_incrementcomment用户编号,usernamevarchar(30)notnulluniquecomment登录用户名,password_hashvarchar(255)notnullcomment密码哈希值,nicknamevarchar(50)notnulldefault新用户comment用户昵称,emailvarchar(100)uniquecomment电子邮箱,statustinyintnotnulldefault1comment状态1正常0禁用,created_atdatetimenotnulldefaultcurrent_timestampcomment创建时间,indexidx_app_user_created_at(created_at))engineinnodbdefaultcharsetutf8mb4comment应用用户表;查看表结构descapp_user;showcreatetableapp_user;showindexfromapp_user;id使用自增长主键唯一标识每个用户。username必填且唯一防止登录名重复。password_hash只保存密码哈希不应保存明文密码。nickname和status提供默认值简化插入操作。email可以为空但非空邮箱不能重复。created_at自动记录创建时间并建立普通索引。十三、约束对比速查约束或属性主要作用是否允许重复是否允许 nullnot null保证字段有值允许不允许default省略字段时自动补值允许取决于空属性primary key唯一标识记录不允许不允许unique保证业务值唯一不允许非空值重复通常允许多个auto_increment自动生成整数编号自动生成不同值不适用foreign key保证引用关系有效允许取决于空属性comment描述表或字段不限制不限制index提高查询速度允许允许十四、常见问题14.1 default 和 not null 是否冲突不冲突。not null表示不允许保存空值。default表示插入时省略字段就使用默认值。如果显式插入null即使设置了默认值not null字段仍会报错。14.2 主键和唯一键有什么区别一张表只能有一个主键但可以有多个唯一键。主键一定不能为null。MySQL 唯一键通常可以保存多个null。主键用于标识记录唯一键用于保证业务字段不重复。14.3 建立索引后查询一定更快吗不一定数据量过小或返回数据过多时全表扫描可能更合适。需要使用explain查看查询是否真正使用索引。频繁变化且区分度很低的字段通常不适合单独建立索引。索引会增加写操作成本应该结合查询需求设计。总结表约束负责阻止不符合规则的数据进入数据库。not null、default、主键、唯一键和外键解决不同的数据完整性问题。自增长常与整数主键搭配但生成的编号不保证连续。comment提升表结构可读性普通索引提升特定查询的访问速度。学习约束时应同时执行正确案例和错误案例观察 MySQL 的真实反馈。
