创建请销假系统
1.1 数据库表设计
1.1.1 设计学生表
主要字段有id
,student_id
,admission_year
,college_code
,gender
,student_name
,phone
, class
, password
校验输入phone是否合法
alter table students
add phone CHAR(11) NOT NULL CHECK (phone REGEXP '^[0-9]{11}$');
创建一个触发器,在插入新记录后自动填充student_id
字段。这个触发器会根据新插入的记录中的入学年份、学院编号、性别以及自增的id来生成student_id。
DELIMITER //
/*
创建一个触发器,用于在学生表中插入新记录后自动设置学生的唯一标识符student_id。
该触发器在插入操作完成后触发,对每个新插入的行执行。
触发器通过组合入学年份、学院代码、性别和学生编号生成一个新的student_id。
*/
CREATE TRIGGER set_student_id
AFTER INSERT
ON students
FOR EACH ROW
BEGIN
/*
组合学生的唯一标识符。
使用CONCAT将入学年份、学院代码(补齐至两位)、性别和学生编号(补齐至四位)连接起来。
确保student_id的唯一性和一定的结构化,方便后续查询和管理。
*/
SET @new_student_id = CONCAT(
NEW.admission_year,
LPAD(NEW.college_code, 2, '0'),
NEW.gender,
LPAD(NEW.id, 4, '0'));
/*
更新学生表,将新生成的student_id赋值给对应的新插入行。
这里使用WHERE id = NEW.id来确保只更新刚刚插入的那行数据。
*/
UPDATE students
Set student_id = @new_student_id
WHERE id = NEW.id;
end;
DELIMITER //
ALTER TABLE students
ADD COLUMN password_hash CHAR(64) NOT NULL DEFAULT SHA2('111111', 256);
Error1: 学号自动生成出错
zn_holiday> INSERT INTO zn_holiday.students (student_id, admission_year, college_code, gender, student_name) VALUES (null, 2024, '10', '01', '张三')
[2024-06-07 11:27:22] [23000][1048] Column 'student_id' cannot be null
Step1:
更改student_id字段允许默认为null值
zn_holiday> INSERT INTO zn_holiday.students (student_id, admission_year, college_code, gender, student_name) VALUES (null, 2024, '12', '01', '张三')
[2024-06-07 13:29:48] [HY000][1442] Can't update table 'students' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
Step2:
触发器试图更新正在被当前操作影响的表students
,而这种操作在Mysql中是不允许的,以避免循环或意外的副作用
触发器set_student_id
在检测到新纪录插入后尝试更新students
表中的student_id
字段。由于触发器是由对students
表的INSERT操作触发的,所以它不能反过来再更新同一张表,这会导致一种“直接或间接地引发自身的循环"。
为了使在插入时自动生成student_id
,可能的解决方案:
使用BEFORE INSERT触发器代替: 改变触发器为BEFORE INSERT
类型,并直接在触发器中设置NEW.student_id
的值,而不是执行UPDATE。这样就避免了在触发器内部对正在插入数据的表进行更新操作的问题
CREATE TRIGGER set_student_id
BEFORE INSERT
ON students
FOR EACH ROW
BEGIN
SET NEW.student_id = CONCAT(
NEW.admission_year,
LPAD(NEW.college_code, 2, '0'),
NEW.gender,
LPAD(NEW.id, 4, '0'));
END
Step3:
INSERT INTO zn_holiday.students (student_id, admission_year, college_code, gender, student_name) VALUES (null, 2024, '12', '01', '张三')
[2024-06-07 13:47:48] [22001][1406] Data truncation: Data too long for column 'student_id' at row 1
更改student_id
varchar(10)
为varchar(15)
;
**Error2:**向students表中插入哈希密码出错
向students表中插入哈希密码字段
zn_holiday> ALTER TABLE students
ADD COLUMN password_hash CHAR(64) NOT NULL DEFAULT SHA2('111111',256) comment '密码哈希值'
[2024-06-11 09:32:56] [42000][1064] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SHA2('111111',256) comment '密码哈希值'' at line 2
step1
MySQL并不直接支持在 DEFAULT
子句中使用函数来计算非静 态默认值。不能直接在 ALTER TABLE
语句中使用 SHA2()
或其他函数来为每个新记录动态生成默认的哈希密码。
先执行 ALTER TABLE
添加字段,然后通过更新语句设置默认的哈希密码
step2
后续在后端来设置哈希密码
1.1.2 设计请假类型表
CREATE table laave_types(
leave_type_id INT AUTO_INCREMENT PRIMARY KEY ,
leave_name VARCHAR(50) NOT NULL,
description TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
在这个SQL语句中:
leave_type_id
是主键字段,使用INT
类型并设置为自动递增(AUTO_INCREMENT
),确保每条记录都有唯一的ID。leave_name
字段用于存储请假类型的名称,如“病假”、“事假”等,类型为VARCHAR(50)
,并标记为NOT NULL
description
字段可选,用于存储请假类型的详细描述,类型为TEXT
,可以存储大量文本。created_at
和updated_at
字段分别记录请假类型创建和最后更新的时间,均使用TIMESTAMP
类型,并设置了默认值:CURRENT_TIMESTAMP
表示插入时的时间,以及ON UPDATE CURRENT_TIMESTAMP
表示每次更新记录时自动更新为当前
1.1.3设计请假申请表
CREATE TABLE leave_applications (
application_id INT AUTO_INCREMENT PRIMARY KEY,
student_id INT NOT NULL,
leave_type_id INT NOT NULL,
start_date DATE NOT NULL,
end_date DATE NOT NULL,
reason TEXT NOT NULL,
status ENUM('申请中', '批准', '拒绝', '已销假') DEFAULT '申请中',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (student_id) REFERENCES students(student_id),
FOREIGN KEY (leave_type_id) REFERENCES leave_types(leave_type_id)
);
application_id
是主键字段,自动递增。student_id
和leave_type_id
分别为外键,引用students
表的student_id
和leave_types
表的leave_type_id
,用于关联学生和请假类型,且这两个字段都被标记为NOT NULL
。start_date
和end_date
定义请假的起始和结束日期,类型为DATE
,并要求非空。reason
字段用于记录请假理由,类型为TEXT
,也是必填项。status
字段使用ENUM
类型来限定请假申请的状态,包括“申请中”、“批准”、“拒绝”和“已销假”,并设置了默认值为“申请中”。created_at
和updated_at
字段记录记录创建和最后更新的时间
**Error1:**status在遍历状态,无法创建该表
Step1:
查询mysql版本,是否为5.7及以上
不是mysql版本低的原因
Step2:
尝试移除DEFAULT ’申请中‘
[2024-06-07 15:01:04] [42000][1067] Invalid default value for 'status'
zn_holiday> CREATE TABLE leave_applications
(
application_id INT AUTO_INCREMENT PRIMARY KEY,
student_id INT NOT NULL,
leave_type_id INT NOT NULL,
start_date DATE NOT NULL,
end_date DATE NOT NULL,
reason TEXT NOT NULL,
status ENUM ('申请中','已批准','已拒绝','已销假'),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (student_id) references students (student_id),
FOREIGN KEY (leave_type_id) references laave_types (leave_type_id)
)
[2024-06-07 15:02:27] [HY000][1291] Column 'status' has duplicated value '???' in ENUM
Step3:
问题所在 : 未设置数据库引擎和字符编码格式
CREATE TABLE leave_applications
(
application_id INT AUTO_INCREMENT PRIMARY KEY,
student_id INT NOT NULL,
leave_type_id INT NOT NULL,
start_date DATE NOT NULL,
end_date DATE NOT NULL,
reason TEXT NOT NULL,
status ENUM('申请中', '已批准', '已拒绝', '已销假')default '申请中',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (student_id) references students (student_id),
FOREIGN KEY (leave_type_id) references leave_types (leave_type_id)
)engine = innodb default charset = utf8mb4;
Error2:请假类型表无法与students表建立外键
Step1:
外键类型不匹配:检查leave_applications
表中外键列的数据类型是否与它们所引用的表中相应列的数据类型完全一致。例如,student_id
和leave_type_id
的类型都应该是INT,并且长度、无符号属性等也要相同。
student_id VARCHAR(15) NOT NULL,
依然无法建立外键
Step2:
如果students表中的student_id没有建立索引,仍然无法建立外键
查看索引状态
SHOW INDEXES FROM students;
student_id存在索引。
step3:
将两个表中的student_id 都建立唯一索引,字符集编码都为utf8mb4_bin,成功
1.1.4 设计职员表
CREATE TABLE staffs
(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY comment '员工id',
staff_id VARCHAR(10) NOT NULL UNIQUE comment '员工工号',
staff_name VARCHAR(50) NOT NULL comment '员工姓名',
staff_level ENUM ('辅导员','主任','院长') NOT NULL comment '员工级别',
superior_staff_id VARCHAR(10) comment '上级员工工号', -- 上级员工工号,院长级别最高
password VARCHAR(50) DEFAULT '111111' NOT NULL comment '密码',
college_code CHAR(2) NOT NULL comment '学院编号',
FOREIGN KEY (superior_staff_id) REFERENCES staffs(staff_id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE = INNODB
DEFAULT CHARSET = utf8mb4;
staff_id
是主键,自动递增,确保每位职员有唯一的ID。staff_level
使用ENUM
类型定义了职员的三个等级:辅导员、主任、院长。staff_name
存储职员的姓名,并通过UNIQUE约束保证姓名的唯一性,避免重名。superior_staff_id
是一个自引用的外键,它可以指向staffs
表中的另一个职员,表示该职员的直接上级。对于最高级别的职员(如院长),这个字段可以设置为其自己的staff_id
,或者对于没有直接上级的情况设置为NULL
。
将职员id按照规则自动生成
CREATE TRIGGER set_staff_id
BEFORE INSERT
ON staffs
FOR EACH ROW
BEGIN
SET NEW.staff_id = CONCAT(
LPAD(NEW.id, 4, '0'),
LPAD(collegge_code, 2, '0')
);
end;
Error: 插入职员数据时出错
step1:
职员id自动生成错误更改为如下 成功
CREATE TRIGGER set_staff_id
BEFORE INSERT
ON staffs
FOR EACH ROW
BEGIN
SET NEW.staff_id = CONCAT(
LPAD(NEW.id, 4, '0'),
LPAD(NEW.college_code, 2, '0') -- 此处为NEW.college_code
);
end;
Error2: 插入多条数据出错
如果id
字段是自增的,那么在BEFORE INSERT
触发器中,NEW.id
的值可能还没有被数据库自动赋值。在这种情况下,需要使用LAST_INSERT_ID()
函数来获取最近一次插入操作的自增ID值。更改触发器如下
create definer = root@localhost trigger set_staff_id
before insert
on staffs
for each row
BEGIN
-- 假设id是自增主键,这里使用LAST_INSERT_ID()获取上一条记录的ID
-- 注意:这里可能需要根据实际情况调整逻辑
SET NEW.staff_id = CONCAT(
LPAD(LAST_INSERT_ID(), 4, '0'),
LPAD(NEW.college_code, 4, '0')
);
END;
1.1.5设计审批表单
CREATE TABLE approvals (
approval_id INT AUTO_INCREMENT PRIMARY KEY comment '审批id',
application_id INT NOT NULL comment '请假申请id',
approver_id VARCHAR(10) NOT NULL comment '审批人id',
approval_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP comment '审批时间',
approval_status ENUM ('待审批', '已通过', '已拒绝') DEFAULT '待审批'comment '审批状态',
comment TEXT comment '审批意见',
FOREIGN KEY (application_id) REFERENCES leave_applications(application_id),
FOREIGN KEY (approver_id) REFERENCES staffs(staff_id),
INDEX idx_approver_status (approver_id, approval_status)
)ENGINE = INNODB DEFAULT CHARSET = utf8mb4;
- 定义了表名为
approvals
,并设置了各个字段及数据类型。 - 使用
AUTO_INCREMENT
为approval_id
字段设置自动增长的主键。 - 对
application_id
和approver_id
添加了外键约束,分别引用leave_applications
表的application_id
和假设的staffs
表的staff_id
。 approval_time
默认值设为CURRENT_TIMESTAMP
,记录审批的时间。approval_status
使用ENUM
类型定义了审批的几种可能状态,并设置了默认值为待审批
。- 添加了
comments
字段来存储审批意见。
NE = INNODB DEFAULT CHARSET = utf8mb4;
- 定义了表名为`approvals`,并设置了各个字段及数据类型。
- 使用`AUTO_INCREMENT`为`approval_id`字段设置自动增长的主键。
- 对`application_id`和`approver_id`添加了外键约束,分别引用`leave_applications`表的`application_id`和假设的`staffs`表的`staff_id`。
- `approval_time`默认值设为`CURRENT_TIMESTAMP`,记录审批的时间。
- `approval_status`使用`ENUM`类型定义了审批的几种可能状态,并设置了默认值为`待审批`。
- 添加了`comments`字段来存储审批意见。
- 创建了一个索引`idx_approver_status`,如果在查询时经常需要根据审批人和审批状态过滤记录,这个索引可能会提升查询性能。