52 lines
2.0 KiB
SQL
52 lines
2.0 KiB
SQL
CREATE TABLE users
|
||
(
|
||
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
account VARCHAR(255) UNIQUE NOT NULL,
|
||
name VARCHAR(255) NOT NULL,
|
||
birth DATE,
|
||
email VARCHAR(255),
|
||
password VARCHAR(255) NOT NULL,
|
||
phone VARCHAR(20),
|
||
status ENUM (
|
||
'active',
|
||
'inactive',
|
||
'suspended',
|
||
'deleted',
|
||
'locked',
|
||
'pending verification'
|
||
) DEFAULT 'active',
|
||
created_time DATETIME DEFAULT CURRENT_TIMESTAMP,
|
||
updated_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
||
);
|
||
|
||
CREATE TABLE IF NOT EXISTS posts
|
||
(
|
||
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '文章ID',
|
||
title VARCHAR(255) NOT NULL COMMENT '标题', -- 将长度扩展为255，适应更长的标题
|
||
content TEXT COMMENT '内容',
|
||
link VARCHAR(200) COMMENT 'URL地址', -- 添加唯一索引，避免重复链接
|
||
popularity VARCHAR(100) COMMENT '热度', -- 假设热度是一个数值，使用 INT 类型
|
||
created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
INDEX idx_title (title), -- 为 title 添加索引，提高查询效率
|
||
INDEX idx_link (link) -- 为 link 添加索引，避免 URL 查找慢
|
||
) ENGINE = InnoDB
|
||
DEFAULT CHARSET = utf8mb4
|
||
COLLATE = utf8mb4_unicode_ci COMMENT ='文章表';
|
||
|
||
|
||
|
||
# pub id: Option<i32>,
|
||
# pub title: Option<String>,
|
||
# pub content: Option<String>,
|
||
# pub link: Option<String>,
|
||
# pub popularity: Option<String>,
|
||
# pub created_time: Option<String>,
|
||
# pub updated_time: Option<String>,
|
||
|
||
|
||
INSERT INTO test_server.users (id, account, name, birth, password)
|
||
VALUES (0, '123', 'hyh', '2000-0-03', '1');
|
||
|
||
INSERT INTO test_server.users (account, name, password)
|
||
VALUES ('123', 'hyh', '1'); |