学习笔记(34):mySQL数据库开发教程-限制某列的取值范围

立即学习:https://edu.youkuaiyun.com/course/play/4364/77174?utm_source=blogtoedu

# 使用触发器实现数据完整性，判断性别输入内容
create table student
(
sname varchar(10)
sex
mobile
email
)

create trigger limitsex
before insert on student
for each row
begin
declare mesg varchar(10)
if new.sex='男' or new.sex='女' then
set mesg='插入成功';
else select A002 into mesg;
end if
end

# 删除触发器
drop trigger limitsex

# 判断邮箱格式
create trigger limitemail
before insert on student
for each row
begin 
declare mesg varchar(10);
if new.email regexp '.+@.+' then
set mesg='插入成功';
else select A002 into mesg;
end if
end


select 'han@sohu.com' regexp '.+@.+' ------>返回1
返回1 true
返回0 false

# 判断手机号长度
create trigger limitMobile
before insert on student
for each row
begin 
declare mesg varchar(10);
if new.mobile regexp '[1][0-9]{10}' then
set mesg='插入成功';
else select A002 into mesg;
end if
end