数据库设计

线性表构建树结构

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

如一个树形目录：

Directory（目录表）

id	parent_id	name	deep
1	0	根（虚无，不显示）	0
2	1	电子产品	1
3	1	家具	1
4	2	手机	2
5	3	沙发	2

存储过程

eg:用户注册

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接口，用于定义调用存储过程的方法：

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目录下：

<?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代码块，它们可以在数据库中存储并重复使用。尽管它们在某些方面相似，但它们之间存在一些关键区别：

区别

返回值：
- PROCEDURE：可以有输出参数，但不直接返回值。
- FUNCTION：必须返回一个值。
调用方式：
- PROCEDURE：通常通过CALL语句调用。
- FUNCTION：可以作为表达式的一部分在SQL查询中使用。
事务控制：
- PROCEDURE：可以包含事务控制语句（如COMMIT和ROLLBACK）。
- FUNCTION：通常不允许包含事务控制语句。
副作用：
- PROCEDURE：可以有副作用，如修改表数据。
- FUNCTION：通常不应该有副作用，应该保持幂等性。

用户注册逻辑的选择

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

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

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

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

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
);

存储过程如下：

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');

解释

参数：存储过程RegisterUser接受三个输入参数：username、email和password。
事务控制：使用START TRANSACTION和COMMIT确保插入操作的原子性。
插入操作：将用户信息插入到users表中。
获取插入的用户ID：使用LAST_INSERT_ID()获取插入的用户ID，并将其存储在变量v_user_id中。
输出用户ID：可以选择性地输出插入的用户ID。

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