数据库设计

线性表构建树结构

大家都由根延伸而出,且根是一个虚无的东西.

如一个树形目录:

Directory(目录表)

一级目录深度为1,二级目录深度为2….

idparent_idnamedeep
10根(虚无,不显示)0
21电子产品1
31家具1
42手机2
53沙发2

存储过程

eg:用户注册

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
DELIMITER //

CREATE PROCEDURE RegisterUser(
IN p_username VARCHAR(50),
IN p_email VARCHAR(100),
IN p_password VARCHAR(255),
OUT p_result VARCHAR(50) -- 添加输出参数用于返回结果
)
BEGIN
-- 声明变量用于存储插入的用户ID和检查结果
DECLARE v_user_id INT;
DECLARE v_username_exists INT DEFAULT 0;
DECLARE v_email_exists INT DEFAULT 0;

-- 开始事务
START TRANSACTION;

-- 检查用户名是否存在
SELECT COUNT(*) INTO v_username_exists FROM users WHERE username = p_username;

-- 检查邮箱是否存在
SELECT COUNT(*) INTO v_email_exists FROM users WHERE email = p_email;

-- 如果用户名或邮箱已存在,则设置结果为失败并回滚事务
IF v_username_exists > 0 OR v_email_exists > 0 THEN
SET p_result = 'FAILURE';
ROLLBACK;
ELSE
-- 插入用户信息
INSERT INTO users (username, email, password)
VALUES (p_username, p_email, p_password);

-- 获取插入的用户ID
SET v_user_id = LAST_INSERT_ID();

-- 提交事务
COMMIT;

-- 设置结果为成功
SET p_result = 'SUCCESS';
END IF;
END //

DELIMITER ;

后端接收:

在Spring Boot项目中结合MyBatis调用存储过程的方法如下:

创建一个Mapper接口,用于定义调用存储过程的方法:

1
2
3
4
5
6
7
8
9
10
11
package com.example.yourapp.mapper;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

@Mapper
public interface UserMapper {
String registerUser(@Param("username") String username,
@Param("email") String email,
@Param("password") String password);
}

然后,创建一个XML映射文件(例如UserMapper.xml),放在src/main/resources/mapper目录下:

1
2
3
4
5
6
7
8
9
10
11
12
13
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.example.yourapp.mapper.UserMapper">
<select id="registerUser" statementType="CALLABLE">
{call RegisterUser(
#{username, mode=IN, jdbcType=VARCHAR},
#{email, mode=IN, jdbcType=VARCHAR},
#{password, mode=IN, jdbcType=VARCHAR},
#{result, mode=OUT, jdbcType=VARCHAR}
)}
</select>
</mapper>

现在,当你发送一个POST请求到/register端点时,Spring Boot将调用存储过程RegisterUser来执行用户注册操作,并返回结果。

删除

避免使用物理删除,用户删除时,数据还是要保留,用is_delete保存是否删除。(逻辑删除)

在树形数据库表中,删除的时候需要判断孩子节点,所以需要创建存储过程

一些AI回答的问题

PROCEDURE(存储过程)和FUNCTION(函数)

SQL中的PROCEDURE(存储过程)和FUNCTION(函数)都是预编译的SQL代码块,它们可以在数据库中存储并重复使用。尽管它们在某些方面相似,但它们之间存在一些关键区别:

区别

  1. 返回值

    • PROCEDURE:可以有输出参数,但不直接返回值。
    • FUNCTION:必须返回一个值。
  2. 调用方式

    • PROCEDURE:通常通过CALL语句调用。
    • FUNCTION:可以作为表达式的一部分在SQL查询中使用。
  3. 事务控制

    • PROCEDURE:可以包含事务控制语句(如COMMITROLLBACK)。
    • FUNCTION:通常不允许包含事务控制语句。
  4. 副作用

    • PROCEDURE:可以有副作用,如修改表数据。
    • FUNCTION:通常不应该有副作用,应该保持幂等性。

用户注册逻辑的选择

对于用户注册的逻辑,通常更适合使用PROCEDURE,因为:

  • 注册过程可能涉及多个步骤(如插入用户信息、发送验证邮件等),这些步骤可以通过存储过程的逻辑来实现。
  • 注册过程可能需要有副作用(如修改表数据)。
  • 注册过程可能需要事务控制以确保数据的一致性。

示例:用户注册的存储过程

以下是一个简单的用户注册存储过程的示例,假设我们有一个名为users的表,结构如下:

1
2
3
4
5
6
7
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
email VARCHAR(100) NOT NULL UNIQUE,
password VARCHAR(255) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

存储过程如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
DELIMITER //

CREATE PROCEDURE RegisterUser(
IN p_username VARCHAR(50),
IN p_email VARCHAR(100),
IN p_password VARCHAR(255)
)
BEGIN
-- 声明变量用于存储插入的用户ID
DECLARE v_user_id INT;

-- 开始事务
START TRANSACTION;

-- 插入用户信息
INSERT INTO users (username, email, password)
VALUES (p_username, p_email, p_password);

-- 获取插入的用户ID
SET v_user_id = LAST_INSERT_ID();

-- 提交事务
COMMIT;

-- 输出用户ID(可选)
SELECT v_user_id AS user_id;
END //

DELIMITER ;

调用存储过程

1
CALL RegisterUser('john_doe', 'john@example.com', 'hashed_password');

解释

  1. 参数:存储过程RegisterUser接受三个输入参数:usernameemailpassword
  2. 事务控制:使用START TRANSACTIONCOMMIT确保插入操作的原子性。
  3. 插入操作:将用户信息插入到users表中。
  4. 获取插入的用户ID:使用LAST_INSERT_ID()获取插入的用户ID,并将其存储在变量v_user_id中。
  5. 输出用户ID:可以选择性地输出插入的用户ID。

通过这种方式,用户注册的逻辑可以封装在一个存储过程中,便于管理和复用。