36 lines
1.7 KiB
SQL
36 lines
1.7 KiB
SQL
-- 迁移文件：创建基础表结构
|
||
-- 创建时间：2024-01-01
|
||
-- 描述：创建用户表和产品表
|
||
|
||
-- 创建用户表
|
||
CREATE TABLE IF NOT EXISTS `users` (
|
||
`id` int(11) NOT NULL AUTO_INCREMENT,
|
||
`username` varchar(50) NOT NULL COMMENT '用户名',
|
||
`email` varchar(100) NOT NULL COMMENT '邮箱',
|
||
`full_name` varchar(100) DEFAULT NULL COMMENT '全名',
|
||
`is_active` tinyint(1) NOT NULL DEFAULT '1' COMMENT '是否激活',
|
||
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `username` (`username`),
|
||
UNIQUE KEY `email` (`email`),
|
||
KEY `idx_username` (`username`),
|
||
KEY `idx_email` (`email`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户表';
|
||
|
||
-- 创建产品表
|
||
CREATE TABLE IF NOT EXISTS `products` (
|
||
`id` int(11) NOT NULL AUTO_INCREMENT,
|
||
`name` varchar(200) NOT NULL COMMENT '产品名称',
|
||
`description` text COMMENT '产品描述',
|
||
`price` decimal(10,2) NOT NULL COMMENT '价格',
|
||
`stock` int(11) NOT NULL DEFAULT '0' COMMENT '库存数量',
|
||
`is_available` tinyint(1) NOT NULL DEFAULT '1' COMMENT '是否可用',
|
||
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
PRIMARY KEY (`id`),
|
||
KEY `idx_name` (`name`),
|
||
KEY `idx_price` (`price`),
|
||
KEY `idx_is_available` (`is_available`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='产品表';
|