init.sql

-- 创建数据库
CREATE DATABASE IF NOT EXISTS web;

-- 创建数据库用户
CREATE USER 'web'@'%' IDENTIFIED BY 'commentsystem';
GRANT ALL PRIVILEGES ON web.* TO 'web'@'%';
FLUSH PRIVILEGES;

-- 切换到新创建的数据库
USE web;

-- 创建用户表
CREATE TABLE IF NOT EXISTS users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) UNIQUE,
    avatar BLOB,
    email VARCHAR(255) UNIQUE,
    age INT UNSIGNED
);

-- CREATE TABLE IF NOT EXISTS users (
--     id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
--     name VARCHAR(255),
--     avatar BLOB,
--     email VARCHAR(255),
--     age INT UNSIGNED,
--     UNIQUE (name, email) -- 将 name 和 email 组合约束
-- );

-- 创建评论表
CREATE TABLE IF NOT EXISTS comments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED,
    article_id VARCHAR(255),
    text TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);