lq/wb-java-nc-shop
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
master
wb-java-nc-shop/z_wb_nc_shop.sql

507 lines
31 KiB
MySQL
Raw Permalink Normal View History

初始化v1
2026-01-11 18:14:42 +08:00
/*
Navicat Premium Dump SQL
Source Server : 本地
Source Server Type : MySQL
Source Server Version : 80406 (8.4.6)
Source Host : localhost:3306
Source Schema : z_wb_nc_shop
Target Server Type : MySQL
Target Server Version : 80406 (8.4.6)
File Encoding : 65001
Date: 10/01/2026 17:01:47
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for announcement
-- ----------------------------
DROP TABLE IF EXISTS `announcement`;
CREATE TABLE `announcement` (
`id` bigint NOT NULL AUTO_INCREMENT,
`merchant_id` bigint NOT NULL COMMENT '商家ID',
`title` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '标题',
`content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT '内容',
`type` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT 'NOTICE' COMMENT '类型：ACTIVITY-活动，NOTICE-通知',
`status` tinyint NULL DEFAULT 1 COMMENT '状态：0-下架，1-发布',
`create_time` datetime NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`) USING BTREE,
INDEX `merchant_id`(`merchant_id` ASC) USING BTREE,
CONSTRAINT `announcement_ibfk_1` FOREIGN KEY (`merchant_id`) REFERENCES `merchant` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '公告表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of announcement
-- ----------------------------
INSERT INTO `announcement` VALUES (1, 1, '新品上市，快来品尝', '新品上市，快来品尝\n新品上市，快来品尝', 'ACTIVITY', 1, '2026-01-09 16:59:12');
INSERT INTO `announcement` VALUES (2, 1, '店庆将于2月30日开始', '当天全部免单！！！', 'ACTIVITY', 1, '2026-01-10 16:12:20');
-- ----------------------------
-- Table structure for cart
-- ----------------------------
DROP TABLE IF EXISTS `cart`;
CREATE TABLE `cart` (
`id` bigint NOT NULL AUTO_INCREMENT,
`user_id` bigint NOT NULL COMMENT '用户ID',
`product_id` bigint NOT NULL COMMENT '商品ID',
`spec_id` bigint NULL DEFAULT NULL COMMENT '规格ID',
`quantity` int NULL DEFAULT 1 COMMENT '数量',
`custom_sweetness` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '甜度定制',
`custom_ice` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '冰度定制',
`toppings` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT '配料JSON',
`create_time` datetime NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`) USING BTREE,
INDEX `product_id`(`product_id` ASC) USING BTREE,
INDEX `idx_cart_user`(`user_id` ASC) USING BTREE,
CONSTRAINT `cart_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT,
CONSTRAINT `cart_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `product` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 12 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '购物车表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of cart
-- ----------------------------
-- ----------------------------
-- Table structure for inventory
-- ----------------------------
DROP TABLE IF EXISTS `inventory`;
CREATE TABLE `inventory` (
`id` bigint NOT NULL AUTO_INCREMENT,
`merchant_id` bigint NOT NULL COMMENT '商家ID',
`material_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '原料名称',
`quantity` decimal(10, 2) NOT NULL COMMENT '数量',
`unit` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '单位',
`min_threshold` decimal(10, 2) NULL DEFAULT 0.00 COMMENT '最低库存阈值',
`last_in_time` datetime NULL DEFAULT NULL COMMENT '最后入库时间',
`last_out_time` datetime NULL DEFAULT NULL COMMENT '最后出库时间',
PRIMARY KEY (`id`) USING BTREE,
INDEX `merchant_id`(`merchant_id` ASC) USING BTREE,
CONSTRAINT `inventory_ibfk_1` FOREIGN KEY (`merchant_id`) REFERENCES `merchant` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '库存表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of inventory
-- ----------------------------
INSERT INTO `inventory` VALUES (1, 1, '橙子', 9998.00, 'kg', 0.00, NULL, '2026-01-09 15:16:54');
-- ----------------------------
-- Table structure for inventory_log
-- ----------------------------
DROP TABLE IF EXISTS `inventory_log`;
CREATE TABLE `inventory_log` (
`id` bigint NOT NULL AUTO_INCREMENT,
`inventory_id` bigint NOT NULL COMMENT '库存ID',
`operation_type` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '操作类型：IN-入库，OUT-出库',
`quantity` decimal(10, 2) NOT NULL COMMENT '操作数量',
`operator` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '操作人',
`create_time` datetime NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`) USING BTREE,
INDEX `inventory_id`(`inventory_id` ASC) USING BTREE,
CONSTRAINT `inventory_log_ibfk_1` FOREIGN KEY (`inventory_id`) REFERENCES `inventory` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '库存操作日志表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of inventory_log
-- ----------------------------
INSERT INTO `inventory_log` VALUES (1, 1, 'OUT', 1.00, '', '2026-01-09 15:16:54');
-- ----------------------------
-- Table structure for merchant
-- ----------------------------
DROP TABLE IF EXISTS `merchant`;
CREATE TABLE `merchant` (
`id` bigint NOT NULL AUTO_INCREMENT,
`username` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '用户名',
`password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '密码（加密）',
`store_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '门店名称',
`address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '门店地址',
`phone` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '联系电话',
`email` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '邮箱',
`manager_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '负责人姓名',
`avatar` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '头像路径',
`create_time` datetime NULL DEFAULT CURRENT_TIMESTAMP,
`update_time` datetime NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`) USING BTREE,
UNIQUE INDEX `username`(`username` ASC) USING BTREE,
INDEX `idx_merchant_username`(`username` ASC) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '商家表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of merchant
-- ----------------------------
INSERT INTO `merchant` VALUES (1, 'l', '4297f44b13955235245b2497399d7a93', 'lq', NULL, '15100000000', 'aaa@qq.com', NULL, NULL, '2026-01-09 12:52:08', '2026-01-09 12:52:08');
-- ----------------------------
-- Table structure for message
-- ----------------------------
DROP TABLE IF EXISTS `message`;
CREATE TABLE `message` (
`id` bigint NOT NULL AUTO_INCREMENT,
`user_id` bigint NOT NULL COMMENT '用户ID',
`merchant_id` bigint NOT NULL COMMENT '商家ID',
`order_id` bigint NULL DEFAULT NULL COMMENT '订单ID',
`content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '留言内容',
`reply` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT '回复内容',
`status` tinyint NULL DEFAULT 0 COMMENT '状态：0-未回复，1-已回复',
`create_time` datetime NULL DEFAULT CURRENT_TIMESTAMP,
`reply_time` datetime NULL DEFAULT NULL COMMENT '回复时间',
PRIMARY KEY (`id`) USING BTREE,
INDEX `user_id`(`user_id` ASC) USING BTREE,
INDEX `merchant_id`(`merchant_id` ASC) USING BTREE,
CONSTRAINT `message_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `message_ibfk_2` FOREIGN KEY (`merchant_id`) REFERENCES `merchant` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '留言反馈表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of message
-- ----------------------------
-- ----------------------------
-- Table structure for order
-- ----------------------------
DROP TABLE IF EXISTS `order`;
CREATE TABLE `order` (
`id` bigint NOT NULL AUTO_INCREMENT,
`order_no` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '订单号',
`user_id` bigint NOT NULL COMMENT '用户ID',
`merchant_id` bigint NOT NULL COMMENT '商家ID',
`total_amount` decimal(10, 2) NOT NULL COMMENT '订单总金额',
`payment_method` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '支付方式：wechat/alipay',
`payment_status` tinyint NULL DEFAULT 0 COMMENT '支付状态：0-未支付，1-已支付',
`order_status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT 'PENDING_PAY' COMMENT '订单状态：PENDING_PAY-待支付，MAKING-制作中，READY-待取餐，COMPLETED-已完成，CANCELLED-已取消',
`address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '收货地址',
`contact_phone` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '联系电话',
`create_time` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`pay_time` datetime NULL DEFAULT NULL COMMENT '支付时间',
`complete_time` datetime NULL DEFAULT NULL COMMENT '完成时间',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE INDEX `order_no`(`order_no` ASC) USING BTREE,
INDEX `idx_order_user`(`user_id` ASC) USING BTREE,
INDEX `idx_order_merchant`(`merchant_id` ASC) USING BTREE,
INDEX `idx_order_status`(`order_status` ASC) USING BTREE,
INDEX `idx_order_no`(`order_no` ASC) USING BTREE,
CONSTRAINT `order_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `order_ibfk_2` FOREIGN KEY (`merchant_id`) REFERENCES `merchant` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '订单表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of order
-- ----------------------------
INSERT INTO `order` VALUES (1, 'ORD202601091502550001', 1, 1, 4.00, 'wechat', 1, 'COMPLETED', '测试地址', '15110000000', '2026-01-09 15:02:55', '2026-01-09 15:03:00', '2026-01-09 15:03:21');
INSERT INTO `order` VALUES (2, 'ORD202601091536100001', 1, 1, 4.00, 'wechat', 1, 'COMPLETED', 'c街道101号', '15100000000', '2026-01-09 15:36:10', '2026-01-09 15:36:16', '2026-01-09 15:36:43');
INSERT INTO `order` VALUES (3, 'ORD202601101522530001', 1, 1, 22.40, 'wechat', 1, 'MAKING', '1111111', '13100000000', '2026-01-10 15:22:53', '2026-01-10 15:22:57', NULL);
INSERT INTO `order` VALUES (4, 'ORD202601101557240001', 1, 1, 6.00, 'alipay', 1, 'MAKING', 'c街道101号', '15100000000', '2026-01-10 15:57:24', '2026-01-10 16:04:09', NULL);
INSERT INTO `order` VALUES (5, 'ORD202601101600170001', 1, 1, 6.00, NULL, 0, 'PENDING_PAY', 'c街道101号', '15100000000', '2026-01-10 16:00:17', NULL, NULL);
INSERT INTO `order` VALUES (6, 'ORD202601101605020001', 2, 1, 30.36, 'wechat', 1, 'READY', '1\n1', '13111111111', '2026-01-10 16:05:02', '2026-01-10 16:05:04', NULL);
INSERT INTO `order` VALUES (7, 'ORD202601101617430001', 1, 1, 27.18, 'wechat', 1, 'MAKING', 'c街道101号', '15100000000', '2026-01-10 16:17:43', '2026-01-10 16:17:46', NULL);
-- ----------------------------
-- Table structure for order_item
-- ----------------------------
DROP TABLE IF EXISTS `order_item`;
CREATE TABLE `order_item` (
`id` bigint NOT NULL AUTO_INCREMENT,
`order_id` bigint NOT NULL COMMENT '订单ID',
`product_id` bigint NOT NULL COMMENT '商品ID',
`product_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '商品名称',
`spec_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '规格名称',
`quantity` int NOT NULL COMMENT '数量',
`price` decimal(10, 2) NOT NULL COMMENT '单价',
`custom_sweetness` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '甜度',
`custom_ice` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '冰度',
`toppings` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT '配料JSON',
`subtotal` decimal(10, 2) NOT NULL COMMENT '小计',
PRIMARY KEY (`id`) USING BTREE,
INDEX `order_id`(`order_id` ASC) USING BTREE,
INDEX `product_id`(`product_id` ASC) USING BTREE,
CONSTRAINT `order_item_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `order` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT,
CONSTRAINT `order_item_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `product` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 10 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '订单明细表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of order_item
-- ----------------------------
INSERT INTO `order_item` VALUES (1, 1, 1, '棒打鲜橙', '大杯', 1, 4.00, '', '', '[]', 4.00);
INSERT INTO `order_item` VALUES (2, 2, 1, '棒打鲜橙', '大杯', 1, 4.00, '', '', '[]', 4.00);
INSERT INTO `order_item` VALUES (3, 3, 1, '棒打鲜橙', '大杯', 2, 7.20, '七分糖', '少冰', '[\"4\"]', 14.40);
INSERT INTO `order_item` VALUES (4, 3, 1, '棒打鲜橙', NULL, 2, 4.00, '无糖', '去冰', '[]', 8.00);
INSERT INTO `order_item` VALUES (5, 4, 1, '棒打鲜橙', '大杯', 1, 6.00, '无糖', '去冰', '[]', 6.00);
INSERT INTO `order_item` VALUES (6, 5, 1, '棒打鲜橙', '大杯', 1, 6.00, '无糖', '去冰', '[]', 6.00);
INSERT INTO `order_item` VALUES (7, 6, 2, '满杯百香果', '大杯', 2, 15.18, '少糖', '少冰', '[\"5\",\"6\",\"7\"]', 30.36);
INSERT INTO `order_item` VALUES (8, 7, 2, '满杯百香果', '大杯', 1, 15.18, '少糖', '少冰', '[\"5\",\"6\",\"7\"]', 15.18);
INSERT INTO `order_item` VALUES (9, 7, 1, '棒打鲜橙', '大杯', 2, 6.00, '无糖', '去冰', '[]', 12.00);
-- ----------------------------
-- Table structure for order_track
-- ----------------------------
DROP TABLE IF EXISTS `order_track`;
CREATE TABLE `order_track` (
`id` bigint NOT NULL AUTO_INCREMENT,
`order_id` bigint NOT NULL COMMENT '订单ID',
`status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '状态',
`description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '描述',
`create_time` datetime NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`) USING BTREE,
INDEX `order_id`(`order_id` ASC) USING BTREE,
CONSTRAINT `order_track_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `order` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 19 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '订单跟踪表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of order_track
-- ----------------------------
INSERT INTO `order_track` VALUES (1, 1, 'PENDING_PAY', '订单已创建，等待支付', '2026-01-09 15:02:55');
INSERT INTO `order_track` VALUES (2, 1, 'MAKING', '支付成功，商家开始制作', '2026-01-09 15:03:00');
INSERT INTO `order_track` VALUES (3, 1, 'READY', '制作完成，等待取餐', '2026-01-09 15:03:15');
INSERT INTO `order_track` VALUES (4, 1, 'COMPLETED', '订单已完成', '2026-01-09 15:03:21');
INSERT INTO `order_track` VALUES (5, 2, 'PENDING_PAY', '订单已创建，等待支付', '2026-01-09 15:36:10');
INSERT INTO `order_track` VALUES (6, 2, 'MAKING', '支付成功，商家开始制作', '2026-01-09 15:36:16');
INSERT INTO `order_track` VALUES (7, 2, 'READY', '制作完成，等待取餐', '2026-01-09 15:36:36');
INSERT INTO `order_track` VALUES (8, 2, 'COMPLETED', '订单已完成', '2026-01-09 15:36:43');
INSERT INTO `order_track` VALUES (9, 3, 'PENDING_PAY', '订单已创建，等待支付', '2026-01-10 15:22:53');
INSERT INTO `order_track` VALUES (10, 3, 'MAKING', '支付成功，商家开始制作', '2026-01-10 15:22:57');
INSERT INTO `order_track` VALUES (11, 4, 'PENDING_PAY', '订单已创建，等待支付', '2026-01-10 15:57:24');
INSERT INTO `order_track` VALUES (12, 5, 'PENDING_PAY', '订单已创建，等待支付', '2026-01-10 16:00:17');
INSERT INTO `order_track` VALUES (13, 4, 'MAKING', '支付成功，商家开始制作', '2026-01-10 16:04:09');
INSERT INTO `order_track` VALUES (14, 6, 'PENDING_PAY', '订单已创建，等待支付', '2026-01-10 16:05:02');
INSERT INTO `order_track` VALUES (15, 6, 'MAKING', '支付成功，商家开始制作', '2026-01-10 16:05:04');
INSERT INTO `order_track` VALUES (16, 7, 'PENDING_PAY', '订单已创建，等待支付', '2026-01-10 16:17:43');
INSERT INTO `order_track` VALUES (17, 7, 'MAKING', '支付成功，商家开始制作', '2026-01-10 16:17:46');
INSERT INTO `order_track` VALUES (18, 6, 'READY', '制作完成，等待取餐', '2026-01-10 16:39:45');
-- ----------------------------
-- Table structure for product
-- ----------------------------
DROP TABLE IF EXISTS `product`;
CREATE TABLE `product` (
`id` bigint NOT NULL AUTO_INCREMENT,
`merchant_id` bigint NOT NULL COMMENT '商家ID',
`name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '商品名称',
`category` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '分类',
`description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT '商品描述',
`base_price` decimal(10, 2) NOT NULL COMMENT '基础价格',
`image` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '商品图片',
`status` tinyint NULL DEFAULT 1 COMMENT '状态：0-下架，1-上架',
`create_time` datetime NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`) USING BTREE,
INDEX `idx_product_merchant`(`merchant_id` ASC) USING BTREE,
INDEX `idx_product_status`(`status` ASC) USING BTREE,
CONSTRAINT `product_ibfk_1` FOREIGN KEY (`merchant_id`) REFERENCES `merchant` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '商品表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of product
-- ----------------------------
INSERT INTO `product` VALUES (1, 1, '棒打鲜橙', '奶茶', '1', 4.00, '/upload/product/ad501998-b3c1-4568-ae4f-31e2d9ce0e3d.jpg', 1, '2026-01-09 15:01:13');
INSERT INTO `product` VALUES (2, 1, '满杯百香果', '果茶', '这个是用了一百个百香果', 7.00, '/upload/product/f6f49393-42c0-4c27-92fe-5d54aa04888f.jpg', 1, '2026-01-10 14:13:38');
-- ----------------------------
-- Table structure for product_custom
-- ----------------------------
DROP TABLE IF EXISTS `product_custom`;
CREATE TABLE `product_custom` (
`id` bigint NOT NULL AUTO_INCREMENT,
`product_id` bigint NOT NULL COMMENT '商品ID',
`option_type` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '选项类型：sweetness-甜度，ice-冰度',
`option_value` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '选项值',
`price_adjust` decimal(10, 2) NULL DEFAULT 0.00 COMMENT '价格调整',
PRIMARY KEY (`id`) USING BTREE,
INDEX `product_id`(`product_id` ASC) USING BTREE,
CONSTRAINT `product_custom_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `product` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 33 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '商品定制选项表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of product_custom
-- ----------------------------
INSERT INTO `product_custom` VALUES (17, 1, 'sweetness', '无糖', 0.00);
INSERT INTO `product_custom` VALUES (18, 1, 'sweetness', '三分糖', 0.00);
INSERT INTO `product_custom` VALUES (19, 1, 'sweetness', '七分糖', 0.00);
INSERT INTO `product_custom` VALUES (20, 1, 'sweetness', '多糖', 0.00);
INSERT INTO `product_custom` VALUES (21, 1, 'ice', '去冰', 0.00);
INSERT INTO `product_custom` VALUES (22, 1, 'ice', '少冰', 0.00);
INSERT INTO `product_custom` VALUES (23, 1, 'ice', '多冰', 0.00);
INSERT INTO `product_custom` VALUES (24, 1, 'ice', '热', 0.00);
INSERT INTO `product_custom` VALUES (25, 2, 'sweetness', '无糖', 0.00);
INSERT INTO `product_custom` VALUES (26, 2, 'sweetness', '少糖', 0.00);
INSERT INTO `product_custom` VALUES (27, 2, 'sweetness', '正常', 0.00);
INSERT INTO `product_custom` VALUES (28, 2, 'sweetness', '多糖', 0.00);
INSERT INTO `product_custom` VALUES (29, 2, 'ice', '去冰', 0.00);
INSERT INTO `product_custom` VALUES (30, 2, 'ice', '少冰', 0.00);
INSERT INTO `product_custom` VALUES (31, 2, 'ice', '正常', 0.00);
INSERT INTO `product_custom` VALUES (32, 2, 'ice', '多冰', 0.00);
-- ----------------------------
-- Table structure for product_spec
-- ----------------------------
DROP TABLE IF EXISTS `product_spec`;
CREATE TABLE `product_spec` (
`id` bigint NOT NULL AUTO_INCREMENT,
`product_id` bigint NOT NULL COMMENT '商品ID',
`spec_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '规格名称（大杯/中杯/小杯）',
`price_adjust` decimal(10, 2) NULL DEFAULT 0.00 COMMENT '价格调整（相对于基础价格）',
PRIMARY KEY (`id`) USING BTREE,
INDEX `product_id`(`product_id` ASC) USING BTREE,
CONSTRAINT `product_spec_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `product` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 12 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '商品规格表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of product_spec
-- ----------------------------
INSERT INTO `product_spec` VALUES (7, 1, '大杯', 2.00);
INSERT INTO `product_spec` VALUES (8, 1, '中杯', 0.00);
INSERT INTO `product_spec` VALUES (9, 2, '大杯', 3.00);
INSERT INTO `product_spec` VALUES (10, 2, '中杯', 2.00);
INSERT INTO `product_spec` VALUES (11, 2, '小杯', 0.00);
-- ----------------------------
-- Table structure for product_topping
-- ----------------------------
DROP TABLE IF EXISTS `product_topping`;
CREATE TABLE `product_topping` (
`id` bigint NOT NULL AUTO_INCREMENT,
`product_id` bigint NOT NULL COMMENT '商品ID',
`topping_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '配料名称',
`price` decimal(10, 2) NULL DEFAULT 0.00 COMMENT '配料价格',
PRIMARY KEY (`id`) USING BTREE,
INDEX `product_id`(`product_id` ASC) USING BTREE,
CONSTRAINT `product_topping_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `product` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '配料表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of product_topping
-- ----------------------------
INSERT INTO `product_topping` VALUES (4, 1, '椰果', 1.20);
INSERT INTO `product_topping` VALUES (5, 2, '更多的百香果', 1.20);
INSERT INTO `product_topping` VALUES (6, 2, '椰果', 2.00);
INSERT INTO `product_topping` VALUES (7, 2, '布丁', 1.98);
-- ----------------------------
-- Table structure for review
-- ----------------------------
DROP TABLE IF EXISTS `review`;
CREATE TABLE `review` (
`id` bigint NOT NULL AUTO_INCREMENT,
`user_id` bigint NOT NULL COMMENT '用户ID',
`order_id` bigint NOT NULL COMMENT '订单ID',
`product_id` bigint NOT NULL COMMENT '商品ID',
`rating` tinyint NOT NULL COMMENT '评分：1-5',
`content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT '评价内容',
`reply` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT '商家回复',
`create_time` datetime NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`) USING BTREE,
INDEX `user_id`(`user_id` ASC) USING BTREE,
INDEX `order_id`(`order_id` ASC) USING BTREE,
INDEX `product_id`(`product_id` ASC) USING BTREE,
CONSTRAINT `review_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `review_ibfk_2` FOREIGN KEY (`order_id`) REFERENCES `order` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `review_ibfk_3` FOREIGN KEY (`product_id`) REFERENCES `product` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '评价表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of review
-- ----------------------------
INSERT INTO `review` VALUES (1, 1, 2, 1, 5, '1', '感谢支持', '2026-01-09 16:59:29');
INSERT INTO `review` VALUES (2, 1, 1, 1, 3, '2', NULL, '2026-01-10 14:06:42');
-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` bigint NOT NULL AUTO_INCREMENT,
`username` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '用户名',
`password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '密码（加密）',
`name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '姓名',
`gender` tinyint NULL DEFAULT NULL COMMENT '性别：0-女，1-男',
`phone` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '手机号',
`email` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '邮箱',
`avatar` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '头像路径',
`address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '默认地址',
`create_time` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` datetime NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE INDEX `username`(`username` ASC) USING BTREE,
INDEX `idx_user_username`(`username` ASC) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '用户表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, 'l', '4297f44b13955235245b2497399d7a93', 'lq', NULL, '15100000000', 'aaa@qq.com', NULL, NULL, '2026-01-09 12:52:08', '2026-01-09 12:52:08');
INSERT INTO `user` VALUES (2, 'l1', '4297f44b13955235245b2497399d7a93', '测试1', NULL, '15100000000', '', NULL, NULL, '2026-01-10 14:14:29', '2026-01-10 14:14:29');
-- ----------------------------
-- Table structure for user_address
-- ----------------------------
DROP TABLE IF EXISTS `user_address`;
CREATE TABLE `user_address` (
`id` bigint NOT NULL AUTO_INCREMENT,
`user_id` bigint NOT NULL COMMENT '用户ID',
`address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '详细地址',
`contact_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '联系人姓名',
`contact_phone` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '联系人电话',
`is_default` tinyint NULL DEFAULT 0 COMMENT '是否默认：0-否，1-是',
`create_time` datetime NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`) USING BTREE,
INDEX `user_id`(`user_id` ASC) USING BTREE,
CONSTRAINT `user_address_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '用户地址表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of user_address
-- ----------------------------
INSERT INTO `user_address` VALUES (1, 1, 'c街道101号', '李二狗', '15100000000', 1, '2026-01-09 15:03:58');
INSERT INTO `user_address` VALUES (2, 1, '1111111', '李二狗2', '13100000000', 0, '2026-01-10 14:06:16');
INSERT INTO `user_address` VALUES (3, 2, '1\n1', '测试', '13111111111', 0, '2026-01-10 16:04:58');
INSERT INTO `user_address` VALUES (4, 2, '1', '测试', '15100000000', 0, '2026-01-10 16:08:06');
-- ----------------------------
-- Table structure for user_behavior
-- ----------------------------
DROP TABLE IF EXISTS `user_behavior`;
CREATE TABLE `user_behavior` (
`id` bigint NOT NULL AUTO_INCREMENT,
`user_id` bigint NOT NULL COMMENT '用户ID',
`product_id` bigint NOT NULL COMMENT '商品ID',
`behavior_type` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '行为类型：VIEW-浏览，PURCHASE-购买，RATING-评分',
`score` decimal(5, 2) NULL DEFAULT 0.00 COMMENT '评分（0-5）',
`create_time` datetime NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`) USING BTREE,
INDEX `idx_user_behavior_user`(`user_id` ASC) USING BTREE,
INDEX `idx_user_behavior_product`(`product_id` ASC) USING BTREE,
CONSTRAINT `user_behavior_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT,
CONSTRAINT `user_behavior_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `product` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '用户行为表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of user_behavior
-- ----------------------------
INSERT INTO `user_behavior` VALUES (1, 1, 1, 'PURCHASE', 3.00, '2026-01-09 15:03:00');
INSERT INTO `user_behavior` VALUES (2, 2, 2, 'PURCHASE', 3.00, '2026-01-10 16:05:04');
INSERT INTO `user_behavior` VALUES (3, 1, 2, 'PURCHASE', 3.00, '2026-01-10 16:17:46');
-- ----------------------------
-- Table structure for user_similarity
-- ----------------------------
DROP TABLE IF EXISTS `user_similarity`;
CREATE TABLE `user_similarity` (
`user1_id` bigint NOT NULL COMMENT '用户1ID',
`user2_id` bigint NOT NULL COMMENT '用户2ID',
`similarity_score` decimal(10, 6) NOT NULL COMMENT '相似度分数',
`update_time` datetime NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`user1_id`, `user2_id`) USING BTREE,
INDEX `user2_id`(`user2_id` ASC) USING BTREE,
CONSTRAINT `user_similarity_ibfk_1` FOREIGN KEY (`user1_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT,
CONSTRAINT `user_similarity_ibfk_2` FOREIGN KEY (`user2_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '用户相似度缓存表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of user_similarity
-- ----------------------------
INSERT INTO `user_similarity` VALUES (1, 2, 1.000000, '2026-01-10 16:17:46');
SET FOREIGN_KEY_CHECKS = 1;
Reference in New Issue Copy Permalink