编辑代码

/**
用户管理系统
*/
--1.基础用户表
CREATE TABLE users (
    user_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    uuid CHAR(36) NOT NULL UNIQUE,
    username VARCHAR(50) NOT NULL UNIQUE,
    password_hash CHAR(97) NOT NULL COMMENT 'argon2格式',
    email VARCHAR(100) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
--2.带数据版本控制的用户表
ALTER TABLE users ADD COLUMN data_version INT NOT NULL DEFAULT 0;
--3.用户状态历史表
CREATE TABLE user_status_history (
    log_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    old_status ENUM('active','locked','deleted'),
    new_status ENUM('active','locked','deleted'),
    changed_by INT UNSIGNED,
    change_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);
--4.密码策略表
CREATE TABLE password_policies (
    policy_id TINYINT PRIMARY KEY,
    min_length TINYINT DEFAULT 8,
    require_upper boolean DEFAULT true,
    require_special CHAR (10)DEFAULT '!@#$%^&*'
);
--5.多列覆盖索引
CREATE INDEX idx_user_search ON users (last_name, first_name) 
INCLUDE (email, phone);
--6.函数索引
CREATE INDEX idx_email_domain ON users((SUBSTRING_INDEX(email, '@', -1)));
--7.全文搜索索引
ALTER TABLE user_profiles 
ADD FULLTEXT ft_idx_bio (bio) WITH PARSER ngram;
--8.空间索引（地理位置）
ALTER TABLE users 
ADD COLUMN geolocation POINT SRID 4326,
ADD SPATIAL INDEX idx_geo (geolocation);
--9.用户注册存储过程
DELIMITER $$
CREATE PROCEDURE RegisterUser(
    IN p_username VARCHAR(50),
    IN p_password VARCHAR(100),
    IN p_email VARCHAR(100)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;
    
    START TRANSACTION;
    INSERT INTO users (username, password_hash, email)
    VALUES (p_username, argon2_hash(p_password), p_email);
    
    INSERT INTO user_roles (user_id, role_id)
    VALUES (LAST_INSERT_ID(), 2); -- 默认普通用户角色
    COMMIT;
END$$
DELIMITER ;
--10.密码重置存储过程
CREATE PROCEDURE ResetPassword(
    IN p_user_id INT,
    IN p_new_password VARCHAR(100)
)
SQL SECURITY INVOKER
BEGIN
    UPDATE users 
    SET password_hash = argon2_hash(p_new_password),
        data_version = data_version + 1
    WHERE user_id = p_user_id
    AND status = 'active';
END;
--11.密码修改审计
CREATE TRIGGER tr_password_change
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    IF OLD.password_hash <> NEW.password_hash THEN
        INSERT INTO security_logs
        (user_id, event_type, details)
        VALUES (NEW.user_id, 'PASSWORD_CHANGE', 
               CONCAT('Changed by ', USER()));
    END IF;
END;
--12.防止直接删除用户
CREATE TRIGGER tr_prevent_user_delete
BEFORE DELETE ON users
FOR EACH ROW
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Use soft delete procedure instead';
--13.用户完整信息视图
CREATE VIEW v_user_details AS
SELECT u.*, p.phone, p.avatar_url, 
       GROUP_CONCAT(r.role_name) AS roles
FROM users u
LEFT JOIN user_profiles p ON u.user_id = p.user_id
LEFT JOIN user_roles ur ON u.user_id = ur.user_id
LEFT JOIN roles r ON ur.role_id = r.role_id
GROUP BY u.user_id;
--14.活跃用户统计视图
CREATE VIEW v_active_users AS
SELECT DATE(login_time) AS login_date,
       COUNT(DISTINCT user_id) AS active_users
FROM login_logs
WHERE success = TRUE
GROUP BY login_date;
--15.批量插入测试数据
INSERT INTO users (username, email, password_hash)
SELECT CONCAT('user', n), 
       CONCAT('user', n, '@test.com'),
       argon2_hash(CONCAT('pass', n))
FROM (SELECT 1 + units.i + tens.i * 10 AS n
      FROM (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 
            UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) units,
           (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 
            UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) tens) numbers
WHERE n BETWEEN 1 AND 100;
--16.安全删除用户
UPDATE users 
SET status = 'deleted', 
    username = CONCAT('deleted_', username),
    email = CONCAT('deleted_', email)
WHERE user_id = ?;
--17.创建只读数据库用户
CREATE USER 'report_user'@'%' IDENTIFIED BY 'SecurePass123!';
GRANT SELECT ON user_management.* TO 'report_user'@'%';
--18.行级安全策略
CREATE POLICY user_access_policy
ON users
FOR ALL
TO api_user
USING (user_id = current_user_id());
--19.清理旧日志
DELETE FROM login_logs 
WHERE login_time < NOW() - INTERVAL 180 DAY 
ORDER BY login_time LIMIT 1000;
--20.重建索引优化表
ALTER TABLE users ENGINE=InnoDB;
ANALYZE TABLE users;
--21.JSON字段查询
ALTER TABLE users ADD COLUMN metadata JSON;
SELECT user_id, metadata->'$.subscription.type' 
FROM users WHERE metadata->'$.preferences.dark_mode' = 'true';
--22.图形数据库关系
CREATE (u:User {userId: 1001, username: 'john_doe'})
CREATE (r:Role {name: 'Admin'})
CREATE (u)-[:HAS_ROLE]->(r)