MySQL的触发器创建与使用——使用Baidu Comate生成与触发测试完整过程

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群版 2核4GB 100GB
推荐场景:
搭建个人博客
云数据库 RDS MySQL,高可用版 2核4GB 50GB
简介: MySQL的触发器创建与使用——使用Baidu Comate生成与触发测试完整过程

DDL与DML

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `createDate` datetime DEFAULT NULL,
  `userName` varchar(20) DEFAULT NULL,
  `pwd` varchar(36) DEFAULT NULL,
  `phone` varchar(11) DEFAULT NULL,
  `age` tinyint(3) unsigned DEFAULT NULL,
  `sex` char(2) DEFAULT '男',
  `introduce` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
 
CREATE TABLE `result` (
  `rid` int(11) NOT NULL AUTO_INCREMENT COMMENT '成绩编号',
  `testName` varchar(255) DEFAULT NULL COMMENT '测试名称',
  `score` double(4,2) DEFAULT NULL COMMENT '成绩',
  `studentId` int(11) DEFAULT NULL COMMENT '学生id',
  PRIMARY KEY (`rid`),
  KEY `studentId` (`studentId`),
  CONSTRAINT `result_ibfk_1` FOREIGN KEY (`studentId`) REFERENCES `student` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
insert into student values(0,'2024-02-25 10:10:10','赵灵儿','123',
'15612345678',16,'女','逍遥哥哥,你终于找到我了。');
insert into student values(0,'2024-02-25 10:10:10','王语嫣','123',
'15612345678',17,'女','慕容复,我和你不共戴天。');
insert into student values(0,'2024-02-25 10:10:10','龙姑娘','123',
'15612345678',22,'女','我想过过过儿过过的日子。');
insert into student values(0,'2024-02-25 10:10:10','杨过','123',
'15612345678',18,'男','一遇杨过误终身。');
insert into student values(0,'2024-02-25 10:10:10','杨逍','123',
'15612345678',27,'男','杨过跟程英的大儿子。');
insert into student (userName,age,introduce)values('黄衣女子',26,'杨过与龙姑娘的大女儿。');
 
insert into result values(0,'政治',96,1);
insert into result values(0,'地理',86,5);
insert into result values(0,'政治',77,3);
insert into result values(0,'英语',99,3);
insert into result values(0,'历史',22,6);
insert into result values(0,'化学',69,2);
insert into result values(0,'Java',79,5);
insert into result values(0,'Java',67,6);
insert into result values(0,'政治',97,2);
insert into result values(0,'物理',76,6);
insert into result values(0,'物理',82,2);
insert into result values(0,'化学',56,4);

创建触发器提问

根据DDL与DML写一个触发器,要求在添加用户信息的时候不允许添加姓名为张王李赵的userName。

返回结果:

为了实现在添加用户信息时不允许添加姓名为张、王、李、赵的userName,您可以创建一个BEFORE INSERT触发器。以下是一个可能的实现:

DELIMITER //
 
CREATE TRIGGER tr_before_insert_student
BEFORE INSERT ON student
FOR EACH ROW
BEGIN
    IF NEW.userName IN ('张', '王', '李', '赵') THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '不允许添加姓名为张、王、李、赵的用户';
    END IF;
END;
 
//
 
DELIMITER ;

这个触发器会在向student表插入新记录之前执行。如果新记录的userName字段值为'张'、'王'、'李'或'赵',触发器会发出一个错误信号,阻止插入操作并显示相应的错误消息。

请注意,上述代码假设您想要完全匹配'张'、'王'、'李'或'赵'这些单独的姓。如果您想要阻止包含这些字作为任何一部分的姓名(例如,'张三'、'王五'、'李丽'、'赵阳'),则需要使用LIKE操作符或正则表达式来进行更复杂的匹配。

如果您想要进行部分匹配,可以修改触发器如下:

DELIMITER //
 
CREATE TRIGGER tr_before_insert_student
BEFORE INSERT ON student
FOR EACH ROW
BEGIN
    IF NEW.userName LIKE '张%' OR NEW.userName LIKE '王%' OR NEW.userName LIKE '李%' OR NEW.userName LIKE '赵%' THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '不允许添加姓名以张、王、李、赵开头的用户';
    END IF;
END;
 
//
 
DELIMITER ;

这个修改后的版本会阻止任何以'张'、'王'、'李'或'赵'开头的userName被插入到student表中。

添加数据并测试触发器

写个DML语句要求添加一位叫做王晓易的人,其它信息随机。

为了向student表中添加一位叫做王晓易的人,并且其它信息随机,您可以使用以下的DML(Data Manipulation Language)语句。请注意,随机数据是使用MySQL内置的函数生成的,可能并不完全符合实际情况,但可用于测试或示例目的。

INSERT INTO `student` (`createDate`, `userName`,  `phone`, `age`, `sex`, `introduce`)
VALUES (
    NOW(), -- 当前日期和时间
    '王晓易',
    CONCAT('1', FLOOR(RAND() * (999999999 - 100000000 + 1)) + 100000000), -- 随机生成11位手机号(以1开头)
    FLOOR(RAND() * (50 - 16 + 1)) + 16, -- 随机生成16到50之间的年龄
    IF(RAND() > 0.5, '男', '女'), -- 随机分配性别
    CONCAT('这是', '王晓易', '的介绍。') -- 简单的介绍文本(可选,仅作示例)
);

这条INSERT INTO语句会向student表中添加一条新记录。userName字段被明确设置为'王晓易',而其他字段则被设置为随机值:

createDate:设置为当前日期和时间。

phone:生成一个随机的11位手机号码,以1开头。

age:生成一个介于16到50岁之间的随机年龄。

sex:使用RAND()函数随机分配'男'或'女'。

introduce:一个简单的介绍文本,包含用户名(可选,仅作示例)。

请根据您的实际需求调整这些随机生成数据的规则。如果您需要更真实的随机数据,可能需要考虑使用专门的数据生成工具或库。

实际测试结果-触发成示例

正常添加非触发器信息

非限制数据正常添加。

总结

在本次操作中,我们首先创建了两个数据表:student和result,分别用于存储学生信息和成绩信息。其中,student表包含了学生的基本信息字段,如学号、创建日期、用户名、密码、电话、年龄、性别和介绍等;而result表则记录了学生的成绩信息,包括成绩编号、测试名称、成绩和学生ID等。

随后,我们针对student表创建了一个重要的触发器tr_before_insert_student,其目的是为了实现在添加用户信息时,不允许添加姓名为张、王、李、赵的userName。该触发器会在向student表插入新记录之前执行检查,如果新记录的userName字段值包含被限制的姓氏,则会发出一个错误信号,从而阻止插入操作并给出相应的错误提示。这为我们的数据录入提供了一层有效的校验机制,确保了数据的准确性和合规性。

为了验证触发器的有效性,我们尝试向student表中插入了多条数据,包括合规的和不合规的用户名。通过实际测试,我们观察到当插入包含被限制姓氏的用户名时,触发器成功阻止了数据的插入,并给出了预设的错误消息。而对于其他合规的用户名,数据则能够正常添加到表中。

此外,我们还展示了如何向student表中插入一条特定用户名(如“王晓易”)的记录,同时为其他字段生成随机数据。这种插入方式可以应用于测试场景,其中随机数据能够帮助我们模拟更真实的数据环境。当然,在实际应用中,我们还需要根据具体需求来调整随机数据的生成规则,以确保数据的真实性和可用性。

综上所述,通过本次操作,我们不仅掌握了DDL和DML的基本用法,还学会了如何创建并使用触发器来约束数据的插入操作。这为我们今后在数据库管理和数据校验方面提供了有力的支持。

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
3天前
|
数据库
基于PHP+MYSQL开发制作的趣味测试网站源码
基于PHP+MYSQL开发制作的趣味测试网站源码。可在后台提前设置好缘分, 自己手动在数据库里修改数据,数据库里有就会优先查询数据库的信息, 没设置的话第一次查询缘分都是非常好的 95-99,第二次查就比较差 , 所以如果要你女朋友查询你的名字觉得很好 那就得是她第一反应是查和你的缘分, 如果查的是别人,那不好意思,第二个可能是你。
15 3
|
5天前
|
运维 关系型数据库 MySQL
实时计算 Flink版产品使用问题之在处理MySQL表新增数据记录时,没有正确触发变更事件,该如何解决
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
1月前
|
存储 SQL 关系型数据库
MySQL数据库进阶第四篇(视图/存储过程/触发器)
MySQL数据库进阶第四篇(视图/存储过程/触发器)
|
1月前
|
存储 SQL 关系型数据库
MySQL周内训参照4、触发器-插入-修改-删除
MySQL周内训参照4、触发器-插入-修改-删除
20 1
|
1月前
|
存储 SQL 关系型数据库
MySQL存储过程_触发器_游标——Baidu Comate
MySQL存储过程_触发器_游标——Baidu Comate
20 0
|
1月前
|
存储 关系型数据库 MySQL
Baidu Comate Web在线出题能力测试——MySQL出题能力
Baidu Comate Web在线出题能力测试——MySQL出题能力
22 0
|
20天前
|
JSON JavaScript 测试技术
Postman接口测试工具详解
Postman接口测试工具详解
27 1
|
10天前
|
XML JSON 测试技术
Postman接口测试工具详解
📚 Postman全攻略:API测试神器!📚 发送HTTP请求,管理集合,写测试脚本,集成CI/CD。从安装配置到环境变量、断言、数据驱动测试,一步步教你如何高效测试RESTful API。实战案例包含GET、POST、PUT、DELETE请求。用Newman在命令行跑集合,自动化测试不发愁!👉 [洛秋小站](https://www.luoqiu.site/) 学更多!🚀
21 1
|
17天前
|
数据采集 测试技术
常见测试测量接口的比较:PXI、PXIe、PCI、VXI、GPIB、USB
常见测试测量接口的比较:PXI、PXIe、PCI、VXI、GPIB、USB
17 2
|
25天前
|
存储 JSON 测试技术
软件测试之 接口测试 Postman使用(下)
软件测试之 接口测试 Postman使用(下)
24 2