數據庫 · 棚大大(雇主|民工端)

~~~[sql] /* Navicat Premium Data Transfer Source Server : 虛擬機 Source Server Type : MySQL Source Server Version : 50719 Source Host : centos-dfc:3306 Source Schema : large_shed Target Server Type : MySQL Target Server Version : 50719 File Encoding : 65001 Date: 14/06/2018 17:50:35 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for admin_named_user -- ---------------------------- DROP TABLE IF EXISTS `admin_named_user`; CREATE TABLE `admin_named_user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '用戶名', `password` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '密碼', `user_id` int(11) NOT NULL COMMENT '所屬用戶id', PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `user_id`(`user_id`) USING BTREE, UNIQUE INDEX `username`(`username`) USING BTREE, CONSTRAINT `admin_named_user_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `admin_user_info` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT ) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '命名用戶登錄表' ROW_FORMAT = Dynamic; -- ---------------------------- -- Table structure for admin_phone_user -- ---------------------------- DROP TABLE IF EXISTS `admin_phone_user`; CREATE TABLE `admin_phone_user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `phone` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '手機號', `password` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '密碼', `user_id` int(11) NOT NULL COMMENT '所屬用戶id', PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `user_id`(`user_id`) USING BTREE, UNIQUE INDEX `phone`(`phone`) USING BTREE, CONSTRAINT `admin_phone_user_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `admin_user_info` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '手機號登錄表' ROW_FORMAT = Dynamic; -- ---------------------------- -- Table structure for admin_user_config -- ---------------------------- DROP TABLE IF EXISTS `admin_user_config`; CREATE TABLE `admin_user_config` ( `id` int(11) NOT NULL AUTO_INCREMENT, `configs` json NULL COMMENT '配置項', `user_id` int(11) NOT NULL COMMENT '所屬用戶', PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `user_id`(`user_id`) USING BTREE, CONSTRAINT `admin_user_config_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `admin_user_info` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT ) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '用戶配置信息' ROW_FORMAT = Dynamic; -- ---------------------------- -- Table structure for admin_user_info -- ---------------------------- DROP TABLE IF EXISTS `admin_user_info`; CREATE TABLE `admin_user_info` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '用戶昵稱', `recent_login_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '最后登錄', PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '用戶信息表' ROW_FORMAT = Dynamic; -- ---------------------------- -- Table structure for banner -- ---------------------------- DROP TABLE IF EXISTS `banner`; CREATE TABLE `banner` ( `id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '標題', `img` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '圖片地址', `url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '連接地址', `is_show` tinyint(1) NOT NULL DEFAULT 0 COMMENT '是否在前端顯示', `position` enum('top','center') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'top' COMMENT '顯示位置', `create_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP, `update_time` timestamp(0) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '輪播圖' ROW_FORMAT = Dynamic; -- ---------------------------- -- Table structure for employer_address_book -- ---------------------------- DROP TABLE IF EXISTS `employer_address_book`; CREATE TABLE `employer_address_book` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL COMMENT '所屬用戶id', `is_default` tinyint(1) NOT NULL DEFAULT 0 COMMENT '是否默認地址', `name` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '聯系人', `phone` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '聯系電話', `province` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '省', `city` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '市', `district` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '區/縣', `town` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '鄉鎮', `area` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '區域', `village` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '村莊', `area_id` int(11) NOT NULL COMMENT '最后一級地址的id，僅用于匹配和篩選地址', PRIMARY KEY (`id`) USING BTREE, INDEX `area_id`(`area_id`) USING BTREE, INDEX `user_id`(`user_id`) USING BTREE, CONSTRAINT `employer_address_book_ibfk_1` FOREIGN KEY (`area_id`) REFERENCES `regions` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT, CONSTRAINT `employer_address_book_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `employer_user_info` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT ) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '雇主地址簿，名稱都使用冗余字段，因為幾乎不會更新' ROW_FORMAT = Dynamic; -- ---------------------------- -- Table structure for employer_balance_records -- ---------------------------- DROP TABLE IF EXISTS `employer_balance_records`; CREATE TABLE `employer_balance_records` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL COMMENT '所屬用戶', `title` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '名稱', `description` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '詳細描述', `value` int(11) NOT NULL COMMENT '變化值，可正可負,單位分', `type` enum('order','reward','red_packet','invite','others') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '來源類型', `extra` json NULL COMMENT '可用于保存訂單號或其它信息', `create_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP, `update_time` timestamp(0) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE, INDEX `user_id`(`user_id`) USING BTREE, CONSTRAINT `employer_balance_records_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `employer_user_info` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '余額變化記錄表' ROW_FORMAT = Dynamic; -- ---------------------------- -- Table structure for employer_device_permissions -- ---------------------------- DROP TABLE IF EXISTS `employer_device_permissions`; CREATE TABLE `employer_device_permissions` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL COMMENT '所屬用戶id', `device_id` int(11) NOT NULL COMMENT '設備id', `name` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '設備別名', `permissions` int(11) NOT NULL COMMENT '用戶對設備的權限', PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `user_id`(`user_id`, `device_id`) USING BTREE, INDEX `device_id`(`device_id`) USING BTREE, CONSTRAINT `employer_device_permissions_ibfk_1` FOREIGN KEY (`device_id`) REFERENCES `employer_devices` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '用戶-設備權限表' ROW_FORMAT = Dynamic; -- ---------------------------- -- Table structure for employer_device_topup -- ---------------------------- DROP TABLE IF EXISTS `employer_device_topup`; CREATE TABLE `employer_device_topup` ( `id` int(11) NOT NULL AUTO_INCREMENT, `fee` int(11) NOT NULL COMMENT '總金額,單位分', `day_of_service` int(11) NOT NULL COMMENT '充值服務時長，單位天', `fee_real` int(11) NOT NULL COMMENT '實際支付費用，單位分', `discount` int(11) NOT NULL COMMENT '減免的費用，單位分', `pay_by_balance` int(11) NOT NULL COMMENT '余額抵扣金額，單位分', `pay_by_points` int(11) NOT NULL COMMENT '積分抵扣金額，單位分', `pay_cost_points` int(11) NOT NULL COMMENT '消耗積分數量，主要用于需要退還的情況', `pay_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '支付流水號', `pay_method` enum('wechat') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '支付渠道，如微信、支付寶', `device_ids` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '充值的設備id列表,以逗號分隔', `title` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '標題', `create_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP, `update_time` timestamp(0) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '設備收費服務購買記錄' ROW_FORMAT = Dynamic; -- ---------------------------- -- Table structure for employer_devices -- ---------------------------- DROP TABLE IF EXISTS `employer_devices`; CREATE TABLE `employer_devices` ( `id` int(11) NOT NULL AUTO_INCREMENT, `sn` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '設備序列號', `owner_id` int(11) NOT NULL COMMENT '所屬用戶id', `name` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '設備原始名稱', `password` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '設備密碼', `type` int(11) NOT NULL COMMENT '設備類型', `subtype` int(11) NOT NULL COMMENT '設備子類型', `extra` json NULL COMMENT '其它設備相關數據', `start_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '服務開始時間', `end_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '服務結束時間', `delete_time` timestamp(0) NULL DEFAULT NULL COMMENT '軟刪除標記', PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `sn`(`sn`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '設備' ROW_FORMAT = Dynamic; -- ---------------------------- -- Table structure for employer_favorites -- ---------------------------- DROP TABLE IF EXISTS `employer_favorites`; CREATE TABLE `employer_favorites` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL COMMENT '所屬用戶id', `labour_id` int(11) NOT NULL COMMENT '收藏的工人', `create_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP, `update_time` timestamp(0) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `user_id`(`user_id`, `labour_id`) USING BTREE, INDEX `labour_id`(`labour_id`) USING BTREE, CONSTRAINT `employer_favorites_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `employer_user_info` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT, CONSTRAINT `employer_favorites_ibfk_2` FOREIGN KEY (`labour_id`) REFERENCES `labour_user_info` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT ) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '雇主的勞務工人收藏夾' ROW_FORMAT = Dynamic; -- ---------------------------- -- Table structure for employer_order_pay_records -- ---------------------------- DROP TABLE IF EXISTS `employer_order_pay_records`; CREATE TABLE `employer_order_pay_records` ( `id` int(11) NOT NULL AUTO_INCREMENT, `order_id` int(11) NOT NULL COMMENT '支付的訂單號', `title` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '標題', `description` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '詳細描述', `fee` int(11) NOT NULL COMMENT '總金額,單位分', `fee_real` int(11) NOT NULL COMMENT '實際支付費用，單位分', `discount` int(11) NOT NULL COMMENT '減免的費用，單位分', `pay_by_balance` int(11) NOT NULL COMMENT '余額抵扣金額，單位分', `pay_by_points` int(11) NOT NULL COMMENT '積分抵扣金額，單位分', `pay_cost_points` int(11) NOT NULL COMMENT '消耗積分數量，主要用于需要退還的情況', `pay_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '支付流水號', `pay_method` enum('wechat') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '支付渠道，如微信、支付寶', `status` enum('pending','succeed','failed') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '支付狀態', `extra` json NULL COMMENT '其它信息', `create_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP, `update_time` timestamp(0) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE, INDEX `order_id`(`order_id`) USING BTREE, CONSTRAINT `employer_order_pay_records_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT ) ENGINE = InnoDB AUTO_INCREMENT = 73 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '訂單支付記錄表' ROW_FORMAT = Dynamic; -- ---------------------------- -- Table structure for employer_phone_user -- ---------------------------- DROP TABLE IF EXISTS `employer_phone_user`; CREATE TABLE `employer_phone_user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `phone` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '手機號', `password` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '密碼', `user_id` int(11) NOT NULL COMMENT '所屬用戶id', PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `user_id`(`user_id`) USING BTREE, UNIQUE INDEX `phone`(`phone`) USING BTREE, CONSTRAINT `employer_phone_user_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `employer_user_info` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT ) ENGINE = InnoDB AUTO_INCREMENT = 13 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '手機號登錄表' ROW_FORMAT = Dynamic; -- ---------------------------- -- Table structure for employer_point_records -- ---------------------------- DROP TABLE IF EXISTS `employer_point_records`; CREATE TABLE `employer_point_records` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL COMMENT '所屬用戶', `title` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '積分項目名稱', `description` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '詳細描述', `value` int(11) NOT NULL COMMENT '積分變化值，可正可負', `type` enum('order','reward','refund','others') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '積分來源類型', `extra` json NULL COMMENT '可用于保存訂單號或其它信息', `create_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP, `update_time` timestamp(0) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE, INDEX `user_id`(`user_id`) USING BTREE, CONSTRAINT `employer_point_records_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `employer_user_info` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '積分記錄表' ROW_FORMAT = Dynamic; -- ---------------------------- -- Table structure for employer_rate_info -- ---------------------------- DROP TABLE IF EXISTS `employer_rate_info`; CREATE TABLE `employer_rate_info` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL COMMENT '被評價人', `rate1` int(11) NOT NULL DEFAULT 85 COMMENT '評分，百分制', `rate2` int(11) NOT NULL DEFAULT 85 COMMENT '評分，百分制', `rate3` int(11) NOT NULL DEFAULT 85 COMMENT '評分，百分制', `total` int(11) NOT NULL DEFAULT 0 COMMENT '評價總數', `create_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP, `update_time` timestamp(0) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE, INDEX `user_id`(`user_id`) USING BTREE, CONSTRAINT `employer_rate_info_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `employer_user_info` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT ) ENGINE = InnoDB AUTO_INCREMENT = 13 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '收到的評價匯總信息' ROW_FORMAT = Dynamic; -- ---------------------------- -- Table structure for employer_ratings -- ---------------------------- DROP TABLE IF EXISTS `employer_ratings`; CREATE TABLE `employer_ratings` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL COMMENT '被評價人', `order_id` int(11) NOT NULL COMMENT '訂單號', `rater_id` int(11) NOT NULL COMMENT '評價人', `anonymous` tinyint(1) NOT NULL DEFAULT 1 COMMENT '是否匿名', `rate1` int(11) NOT NULL COMMENT '評分，百分制', `rate2` int(11) NOT NULL COMMENT '評分，百分制', `rate3` int(11) NOT NULL COMMENT '評分，百分制', `reviews` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '評價內容', `images` json NOT NULL COMMENT '圖片數組', `up` int(11) NOT NULL DEFAULT 0 COMMENT '點贊次數', `down` int(11) NOT NULL DEFAULT 0 COMMENT '反對次數', `praise_people` json NULL COMMENT '點贊人', `create_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP, `update_time` timestamp(0) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `user_id`(`user_id`, `order_id`) USING BTREE, UNIQUE INDEX `order_id`(`order_id`) USING BTREE, INDEX `rater_id`(`rater_id`) USING BTREE, CONSTRAINT `employer_ratings_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `employer_user_info` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT, CONSTRAINT `employer_ratings_ibfk_2` FOREIGN KEY (`rater_id`) REFERENCES `labour_user_info` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT, CONSTRAINT `employer_ratings_ibfk_3` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT ) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '收到的評價' ROW_FORMAT = Dynamic; -- ---------------------------- -- Table structure for employer_user_config -- ---------------------------- DROP TABLE IF EXISTS `employer_user_config`; CREATE TABLE `employer_user_config` ( `id` int(11) NOT NULL AUTO_INCREMENT, `configs` json NULL COMMENT '配置項', `user_id` int(11) NOT NULL COMMENT '所屬用戶', PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `user_id`(`user_id`) USING BTREE, CONSTRAINT `employer_user_config_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `employer_user_info` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT ) ENGINE = InnoDB AUTO_INCREMENT = 13 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '用戶配置信息' ROW_FORMAT = Dynamic; -- ---------------------------- -- Table structure for employer_user_info -- ---------------------------- DROP TABLE IF EXISTS `employer_user_info`; CREATE TABLE `employer_user_info` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '用戶姓名', `recent_login_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '最后登錄', `avatar` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '頭像地址', `gender` enum('male','female','secret') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'secret' COMMENT '性別', `birthday` date NULL DEFAULT NULL COMMENT '出生日期', `nickname` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '用戶昵稱', PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 14 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '用戶信息表' ROW_FORMAT = Dynamic; -- ---------------------------- -- Table structure for job_category -- ---------------------------- DROP TABLE IF EXISTS `job_category`; CREATE TABLE `job_category` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '工種名稱', `icon` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '圖標url地址', `description` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '描述', `type` enum('crops','others') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '分類類型，主要分為需要選擇農作物與不需要選擇農作物。不需要選擇農作物的工種下僅有一項工作，名字為空。', PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `name`(`name`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '工作分類，工種。工種僅作為列舉和選取使用，id并不存入具體訂單中，直接存名字' ROW_FORMAT = Dynamic; -- ---------------------------- -- Table structure for job_category2 -- ---------------------------- DROP TABLE IF EXISTS `job_category2`; CREATE TABLE `job_category2` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '分類/蔬菜名稱', `icon` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '圖標地址', `parent_id` int(11) NULL DEFAULT NULL COMMENT '父級id', PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `name`(`name`) USING BTREE, INDEX `parent_id`(`parent_id`) USING BTREE, CONSTRAINT `job_category2_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `job_category2` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT ) ENGINE = InnoDB AUTO_INCREMENT = 239 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '工作分類2，如蔬菜分類' ROW_FORMAT = Dynamic; -- ---------------------------- -- Table structure for jobs -- ---------------------------- DROP TABLE IF EXISTS `jobs`; CREATE TABLE `jobs` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '工作名稱', `icon` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '圖標url地址', `description` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '描述', `specifications` json NOT NULL COMMENT '工作的規格選項列表', `category_id` int(11) NOT NULL COMMENT '工作分類id，每一項工作（農作物）都屬于一種工種', `category2_id` int(11) NOT NULL DEFAULT 1 COMMENT '工作的第二分類id，例如農作物的種類分類', PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `name`(`name`, `category_id`, `category2_id`) USING BTREE, INDEX `category_id`(`category_id`) USING BTREE, INDEX `category2_id`(`category2_id`) USING BTREE, CONSTRAINT `jobs_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `job_category` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT `jobs_ibfk_2` FOREIGN KEY (`category2_id`) REFERENCES `job_category2` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT ) ENGINE = InnoDB AUTO_INCREMENT = 45 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '具體工作項,農作物也當作一項具體工作' ROW_FORMAT = Dynamic; -- ---------------------------- -- Table structure for labour_balance_records -- ---------------------------- DROP TABLE IF EXISTS `labour_balance_records`; CREATE TABLE `labour_balance_records` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL COMMENT '所屬用戶', `title` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '名稱', `description` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '詳細描述', `value` int(11) NOT NULL COMMENT '變化值，可正可負,單位分', `type` enum('order','reward','red_packet','invite','others') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '來源類型', `extra` json NULL COMMENT '可用于保存訂單號或其它信息', `create_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP, `update_time` timestamp(0) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE, INDEX `user_id`(`user_id`) USING BTREE, CONSTRAINT `labour_balance_records_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `labour_user_info` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT ) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '余額變化記錄表' ROW_FORMAT = Dynamic; -- ---------------------------- -- Table structure for labour_job_categories -- ---------------------------- DROP TABLE IF EXISTS `labour_job_categories`; CREATE TABLE `labour_job_categories` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL COMMENT '所屬用戶id', `job_category_id` int(11) NOT NULL COMMENT '工種id', `name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '工種名稱。冗余字段，避免聯表查詢', `vegetables` json NULL COMMENT '工種對應的蔬菜信息', `type` enum('crops','others') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '工種類型，用于前端區分', PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `user_id`(`user_id`, `job_category_id`) USING BTREE, INDEX `job_category_id`(`job_category_id`) USING BTREE, CONSTRAINT `labour_job_categories_ibfk_1` FOREIGN KEY (`job_category_id`) REFERENCES `job_category` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT, CONSTRAINT `labour_job_categories_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `labour_user_info` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT ) ENGINE = InnoDB AUTO_INCREMENT = 28 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '可服務的工種類型' ROW_FORMAT = Dynamic; -- ---------------------------- -- Table structure for labour_jobs -- ---------------------------- DROP TABLE IF EXISTS `labour_jobs`; CREATE TABLE `labour_jobs` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL COMMENT '所屬用戶id', `job_id` int(11) NOT NULL COMMENT '工作id', `price` int(11) NULL DEFAULT NULL COMMENT '工作報價,單位分', `charging_method` enum('total','per_unit','per_day') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '報價類型', PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `user_id`(`user_id`, `job_id`) USING BTREE, INDEX `job_id`(`job_id`) USING BTREE, CONSTRAINT `labour_jobs_ibfk_1` FOREIGN KEY (`job_id`) REFERENCES `jobs` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT, CONSTRAINT `labour_jobs_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `labour_user_info` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '可服務的工作' ROW_FORMAT = Dynamic; -- ---------------------------- -- Table structure for labour_order_pay_records -- ---------------------------- DROP TABLE IF EXISTS `labour_order_pay_records`; CREATE TABLE `labour_order_pay_records` ( `id` int(11) NOT NULL AUTO_INCREMENT, `order_id` int(11) NOT NULL COMMENT '支付的訂單號', `title` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '標題', `description` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '詳細描述', `fee` int(11) NOT NULL COMMENT '總金額,單位分', `fee_real` int(11) NOT NULL COMMENT '實際支付費用，單位分', `discount` int(11) NOT NULL COMMENT '減免的費用，單位分', `pay_by_balance` int(11) NOT NULL COMMENT '余額抵扣金額，單位分', `pay_by_points` int(11) NOT NULL COMMENT '積分抵扣金額，單位分', `pay_cost_points` int(11) NOT NULL COMMENT '消耗積分數量，主要用于需要退還的情況', `pay_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '支付流水號', `pay_method` enum('wechat') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '支付渠道，如微信、支付寶', `status` enum('pending','succeed','failed') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '支付狀態', `extra` json NULL COMMENT '其它信息', `create_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP, `update_time` timestamp(0) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE, INDEX `order_id`(`order_id`) USING BTREE, CONSTRAINT `labour_order_pay_records_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '訂單支付記錄表' ROW_FORMAT = Dynamic; -- ---------------------------- -- Table structure for labour_phone_user -- ---------------------------- DROP TABLE IF EXISTS `labour_phone_user`; CREATE TABLE `labour_phone_user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `phone` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '手機號', `password` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '密碼', `user_id` int(11) NOT NULL COMMENT '所屬用戶id', PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `user_id`(`user_id`) USING BTREE, UNIQUE INDEX `phone`(`phone`) USING BTREE, CONSTRAINT `labour_phone_user_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `labour_user_info` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT ) ENGINE = InnoDB AUTO_INCREMENT = 16 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '手機號登錄表' ROW_FORMAT = Dynamic; -- ---------------------------- -- Table structure for labour_point_records -- ---------------------------- DROP TABLE IF EXISTS `labour_point_records`; CREATE TABLE `labour_point_records` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL COMMENT '所屬用戶', `title` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '積分項目名稱', `description` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '詳細描述', `value` int(11) NOT NULL COMMENT '積分變化值，可正可負', `type` enum('order','reward','refund','others') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '積分來源類型', `extra` json NULL COMMENT '可用于保存訂單號或其它信息', `create_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP, `update_time` timestamp(0) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE, INDEX `user_id`(`user_id`) USING BTREE, CONSTRAINT `labour_point_records_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `labour_user_info` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT ) ENGINE = InnoDB AUTO_INCREMENT = 61 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '積分記錄表' ROW_FORMAT = Dynamic; -- ---------------------------- -- Table structure for labour_rate_info -- ---------------------------- DROP TABLE IF EXISTS `labour_rate_info`; CREATE TABLE `labour_rate_info` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL COMMENT '被評價人', `rate1` int(11) NOT NULL DEFAULT 85 COMMENT '評分，百分制', `rate2` int(11) NOT NULL DEFAULT 85 COMMENT '評分，百分制', `rate3` int(11) NOT NULL DEFAULT 85 COMMENT '評分，百分制', `total` int(11) NOT NULL DEFAULT 0 COMMENT '評價總數', `create_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP, `update_time` timestamp(0) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE, INDEX `user_id`(`user_id`) USING BTREE, CONSTRAINT `labour_rate_info_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `labour_user_info` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT ) ENGINE = InnoDB AUTO_INCREMENT = 16 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '收到的評價匯總信息' ROW_FORMAT = Dynamic; -- ---------------------------- -- Table structure for labour_ratings -- ---------------------------- DROP TABLE IF EXISTS `labour_ratings`; CREATE TABLE `labour_ratings` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL COMMENT '被評價人', `order_id` int(11) NOT NULL COMMENT '訂單號', `rater_id` int(11) NOT NULL COMMENT '評價人', `anonymous` tinyint(1) NOT NULL DEFAULT 1 COMMENT '是否匿名', `rate1` int(11) NOT NULL COMMENT '評分，百分制', `rate2` int(11) NOT NULL COMMENT '評分，百分制', `rate3` int(11) NOT NULL COMMENT '評分，百分制', `reviews` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '評價內容', `images` json NOT NULL COMMENT '圖片數組', `up` int(11) NOT NULL DEFAULT 0 COMMENT '點贊次數', `down` int(11) NOT NULL DEFAULT 0 COMMENT '反對次數', `praise_people` json NULL COMMENT '點贊人', `create_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP, `update_time` timestamp(0) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `order_id`(`order_id`) USING BTREE, INDEX `user_id`(`user_id`) USING BTREE, INDEX `rater_id`(`rater_id`) USING BTREE, CONSTRAINT `labour_ratings_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `labour_user_info` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT, CONSTRAINT `labour_ratings_ibfk_2` FOREIGN KEY (`rater_id`) REFERENCES `employer_user_info` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT, CONSTRAINT `labour_ratings_ibfk_3` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT ) ENGINE = InnoDB AUTO_INCREMENT = 10 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '收到的評價' ROW_FORMAT = Dynamic; -- ---------------------------- -- Table structure for labour_real_name_info -- ---------------------------- DROP TABLE IF EXISTS `labour_real_name_info`; CREATE TABLE `labour_real_name_info` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL COMMENT '所屬用戶', `status` enum('pending','succeed','failed') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'pending' COMMENT '狀態', `name` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '姓名', `gender` enum('male','female','secret') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '性別', `id_card` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '身份證號', `id_card_pictures` json NOT NULL COMMENT '身份證照片', `birthday` date NOT NULL COMMENT '出生日期', `create_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP, `update_time` timestamp(0) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `user_id`(`user_id`) USING BTREE, CONSTRAINT `labour_real_name_info_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `labour_user_info` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT ) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '實名信息' ROW_FORMAT = Dynamic; -- ---------------------------- -- Table structure for labour_reservations -- ---------------------------- DROP TABLE IF EXISTS `labour_reservations`; CREATE TABLE `labour_reservations` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL COMMENT '所屬用戶', `order_id` int(11) NOT NULL COMMENT '關聯訂單id', `type` enum('bid','phone_reservation','online_reservation') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '訂單類型：招標、電話預約、在線預約', `status` enum('processing','succeed','cancelled','timeout','refused') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'processing' COMMENT '接單狀態', `cancel_reason` enum('others') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '取消原因', `create_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP, `update_time` timestamp(0) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `user_id`(`user_id`, `order_id`) USING BTREE, INDEX `order_id`(`order_id`) USING BTREE, CONSTRAINT `labour_reservations_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `labour_user_info` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT, CONSTRAINT `labour_reservations_ibfk_2` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT ) ENGINE = InnoDB AUTO_INCREMENT = 40 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '勞務預約單（包括招標申請）' ROW_FORMAT = Dynamic; -- ---------------------------- -- Table structure for labour_service_regions -- ---------------------------- DROP TABLE IF EXISTS `labour_service_regions`; CREATE TABLE `labour_service_regions` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL COMMENT '所屬用戶id', `province` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '省', `city` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '市', `district` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '區/縣', `town` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '鄉鎮', `area` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '區域', `area_id` int(11) NOT NULL COMMENT '最后一級地址的id，僅用于匹配和篩選地址', `is_all` tinyint(1) NOT NULL DEFAULT 0 COMMENT '區域是否可全部上門', `villages` json NOT NULL COMMENT '村莊名稱數組', PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `user_id`(`user_id`, `area_id`) USING BTREE, INDEX `area_id`(`area_id`) USING BTREE, CONSTRAINT `labour_service_regions_ibfk_1` FOREIGN KEY (`area_id`) REFERENCES `regions` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT, CONSTRAINT `labour_service_regions_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `labour_user_info` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT ) ENGINE = InnoDB AUTO_INCREMENT = 101 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '服務區域信息，名稱都使用冗余字段，因為幾乎不會更新' ROW_FORMAT = Dynamic; -- ---------------------------- -- Table structure for labour_user_config -- ---------------------------- DROP TABLE IF EXISTS `labour_user_config`; CREATE TABLE `labour_user_config` ( `id` int(11) NOT NULL AUTO_INCREMENT, `configs` json NULL COMMENT '其它配置項', `rest_in_dates` json NULL COMMENT '停止接單的日期，數組', `rest_from_date` date NULL DEFAULT NULL COMMENT '從該日期開始不再接單', `member_number` int(11) NOT NULL DEFAULT 0 COMMENT '工人數量', `pictures` json NULL COMMENT '工作照片，數組', `price` int(11) NOT NULL DEFAULT 0 COMMENT '服務報價', `description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '服務描述', `user_id` int(11) NOT NULL COMMENT '所屬用戶', PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `user_id`(`user_id`) USING BTREE, CONSTRAINT `labour_user_config_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `labour_user_info` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT ) ENGINE = InnoDB AUTO_INCREMENT = 16 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '用戶配置信息' ROW_FORMAT = Dynamic; -- ---------------------------- -- Table structure for labour_user_info -- ---------------------------- DROP TABLE IF EXISTS `labour_user_info`; CREATE TABLE `labour_user_info` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '用戶昵稱', `recent_login_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '最后登錄', `level_points` int(11) NOT NULL DEFAULT 0 COMMENT '等級積分。默認為見習會員，實名后可購買會員卡', `avatar` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '頭像地址', `birthday` date NULL DEFAULT NULL COMMENT '出生日期', `gender` enum('male','female','secret') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'secret' COMMENT '性別', `verify_status` enum('pending','succeed') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '實名認證狀態', `interviewed` int(11) NOT NULL DEFAULT 0 COMMENT '被訪問次數', PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 17 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '用戶信息表' ROW_FORMAT = Dynamic; -- ---------------------------- -- Table structure for labour_vip_cards -- ---------------------------- DROP TABLE IF EXISTS `labour_vip_cards`; CREATE TABLE `labour_vip_cards` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL COMMENT '所屬用戶id', `name` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '會員名稱', `level` int(11) NOT NULL DEFAULT 1 COMMENT '會員等級', `start_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '服務開始時間', `end_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '服務結束時間', `discount` int(11) NOT NULL DEFAULT 100 COMMENT '折扣比例，百分比值', PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `user_id`(`user_id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '會員卡。實名認證后可購買會員卡。購買會員卡后完善資料可升級會員等級。默認名為高級會員，升級后為vip會員' ROW_FORMAT = Dynamic; -- ---------------------------- -- Table structure for labour_vip_cards_pay_records -- ---------------------------- DROP TABLE IF EXISTS `labour_vip_cards_pay_records`; CREATE TABLE `labour_vip_cards_pay_records` ( `id` int(11) NOT NULL AUTO_INCREMENT, `vip_card_id` int(11) NOT NULL COMMENT '會員卡id', `title` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '標題', `day_of_service` int(11) NOT NULL COMMENT '充值服務時長，單位天', `fee` int(11) NOT NULL COMMENT '總金額,單位分', `fee_real` int(11) NOT NULL COMMENT '實際支付費用，單位分', `discount` int(11) NOT NULL COMMENT '減免的費用，單位分', `pay_by_balance` int(11) NOT NULL COMMENT '余額抵扣金額，單位分', `pay_by_points` int(11) NOT NULL COMMENT '積分抵扣金額，單位分', `pay_cost_points` int(11) NOT NULL COMMENT '消耗積分數量，主要用于需要退還的情況', `pay_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '支付流水號', `pay_method` enum('wechat') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '支付渠道，如微信、支付寶', `status` enum('pending','succeed','failed') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '支付狀態', `create_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP, `update_time` timestamp(0) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE, INDEX `vip_card_id`(`vip_card_id`) USING BTREE, CONSTRAINT `labour_vip_cards_pay_records_ibfk_1` FOREIGN KEY (`vip_card_id`) REFERENCES `labour_vip_cards` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT ) ENGINE = InnoDB AUTO_INCREMENT = 15 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '會員卡支付記錄' ROW_FORMAT = Dynamic; -- ---------------------------- -- Table structure for messages -- ---------------------------- DROP TABLE IF EXISTS `messages`; CREATE TABLE `messages` ( `id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '標題', `content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '內容', `extra` json NULL COMMENT '可用于保存其它信息', `is_deleted` tinyint(1) NOT NULL DEFAULT 0 COMMENT '標識發送者是否已刪除，只有發送者和接收者同時已刪除才能真正刪除', `send_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '發送時間', `sender_id` int(11) NOT NULL COMMENT '發件人', `receiver_group` enum('all','employer','labour') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '收件人群組，如果是單條消息，則為null', `type` enum('general','order','real_name_notify','vip_card') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'general' COMMENT '消息類型，可用于區分跳轉等', PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 133 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '消息內容' ROW_FORMAT = Dynamic; -- ---------------------------- -- Table structure for migrations -- ---------------------------- DROP TABLE IF EXISTS `migrations`; CREATE TABLE `migrations` ( `version` bigint(20) NOT NULL, `migration_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `start_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP, `end_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP, `breakpoint` tinyint(1) NOT NULL DEFAULT 0, PRIMARY KEY (`version`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Table structure for orders -- ---------------------------- DROP TABLE IF EXISTS `orders`; CREATE TABLE `orders` ( `id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '訂單標題', `description` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '訂單描述', `employer_name` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '雇主方聯系人名稱', `employer_phone` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '雇主方聯系人電話', `employer_id` int(11) NOT NULL COMMENT '雇主id', `labour_id` int(11) NULL DEFAULT NULL COMMENT '勞務id，招標訂單時可為空', `job_category_name` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '工種名稱', `job_name` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '工作名稱', `specifications` json NOT NULL COMMENT '工作的規格屬性列表', `address` json NOT NULL COMMENT '工作地址', `reservation_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '預約的工作時間', `labour_number` int(11) NOT NULL DEFAULT 1 COMMENT '工作需要勞務人數', `charging_method` enum('total','per_unit','per_day') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '計費方式，電話議價時可為空', `cost` int(11) NULL DEFAULT NULL COMMENT '勞務費用，單位分。電話議價時可為空', `with_shuttle` tinyint(1) NOT NULL DEFAULT 1 COMMENT '雇主是否接送', `memo` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '訂單備注信息', `type` enum('bid','phone_reservation','online_reservation') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '訂單類型：招標、電話預約、在線預約', `status` enum('pending','processing','succeed','finished','cancelled','timeout','refused') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '訂單主流程狀態', `sub_status` enum('normal','labour_cancelled','absence') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'normal' COMMENT '訂單流程外的其它狀態', `cancel_reason` enum('just_trying','no_need','dislike_labour','better_platform','time','price','cant_contact','others') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '取消原因', `employer_rated` tinyint(1) NOT NULL DEFAULT 0 COMMENT '雇主是否已評價', `labour_rated` tinyint(1) NOT NULL DEFAULT 0 COMMENT '工人是否已評價', `extra` json NULL COMMENT '訂單其它屬性', `publish_time` timestamp(0) NULL DEFAULT NULL COMMENT '訂單發布時間', `finish_time` timestamp(0) NULL DEFAULT NULL COMMENT '訂單完成時間', `labour_list` json NULL COMMENT '可以搶單的人', `create_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP, `update_time` timestamp(0) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE, INDEX `employer_id`(`employer_id`) USING BTREE, INDEX `labour_id`(`labour_id`) USING BTREE, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`employer_id`) REFERENCES `employer_user_info` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT `orders_ibfk_2` FOREIGN KEY (`labour_id`) REFERENCES `labour_user_info` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT ) ENGINE = InnoDB AUTO_INCREMENT = 81 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '招工訂單' ROW_FORMAT = Dynamic; -- ---------------------------- -- Table structure for rbac_permissions -- ---------------------------- DROP TABLE IF EXISTS `rbac_permissions`; CREATE TABLE `rbac_permissions` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `Lft` int(11) NOT NULL, `Rght` int(11) NOT NULL, `Title` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL, `Description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL, PRIMARY KEY (`ID`) USING BTREE, INDEX `Lft`(`Lft`) USING BTREE, INDEX `Rght`(`Rght`) USING BTREE, INDEX `Title`(`Title`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Table structure for rbac_rolepermissions -- ---------------------------- DROP TABLE IF EXISTS `rbac_rolepermissions`; CREATE TABLE `rbac_rolepermissions` ( `RoleID` int(11) NOT NULL, `PermissionID` int(11) NOT NULL, `AssignmentDate` int(11) NOT NULL, PRIMARY KEY (`RoleID`, `PermissionID`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Table structure for rbac_roles -- ---------------------------- DROP TABLE IF EXISTS `rbac_roles`; CREATE TABLE `rbac_roles` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `Lft` int(11) NOT NULL, `Rght` int(11) NOT NULL, `Title` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL, `Description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL, PRIMARY KEY (`ID`) USING BTREE, INDEX `Lft`(`Lft`) USING BTREE, INDEX `Rght`(`Rght`) USING BTREE, INDEX `Title`(`Title`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Table structure for rbac_userroles -- ---------------------------- DROP TABLE IF EXISTS `rbac_userroles`; CREATE TABLE `rbac_userroles` ( `UserID` int(11) NOT NULL, `RoleID` int(11) NOT NULL, `AssignmentDate` int(11) NOT NULL, PRIMARY KEY (`UserID`, `RoleID`) USING BTREE, CONSTRAINT `rbac_userroles_ibfk_1` FOREIGN KEY (`UserID`) REFERENCES `admin_user_info` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Table structure for regions -- ---------------------------- DROP TABLE IF EXISTS `regions`; CREATE TABLE `regions` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '名稱', `parent_id` int(11) NULL DEFAULT NULL COMMENT '父級id', PRIMARY KEY (`id`) USING BTREE, INDEX `parent_id`(`parent_id`) USING BTREE, CONSTRAINT `regions_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `regions` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT ) ENGINE = InnoDB AUTO_INCREMENT = 1021 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '省市區縣數據' ROW_FORMAT = Dynamic; -- ---------------------------- -- Table structure for system_jobs -- ---------------------------- DROP TABLE IF EXISTS `system_jobs`; CREATE TABLE `system_jobs` ( `id` int(11) NOT NULL AUTO_INCREMENT, `queue` char(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '隊列名稱，對應到命令行的 --queue', `payload` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '消息內容，為json結構', `attempts` int(3) UNSIGNED NOT NULL COMMENT '任務已經嘗試的次數', `reserved` int(3) UNSIGNED NOT NULL COMMENT '任務是否處于執行中的狀態 0 未執行 1 已執行', `reserved_at` int(10) UNSIGNED NULL DEFAULT NULL COMMENT '任務被標記為已執行中的狀態的時刻', `available_at` int(10) UNSIGNED NOT NULL COMMENT '該任務運行執行的最高時刻，用于任務的定時，任務的延遲執行', `created_at` int(10) UNSIGNED NOT NULL COMMENT '任務的創建時間', PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '消息隊列使用的表' ROW_FORMAT = Dynamic; -- ---------------------------- -- Table structure for user_messages -- ---------------------------- DROP TABLE IF EXISTS `user_messages`; CREATE TABLE `user_messages` ( `sender_id` int(11) NOT NULL COMMENT '發件人', `receiver_id` int(11) NOT NULL COMMENT '收件人', `message_id` int(11) NOT NULL COMMENT '消息內容id', `is_read` tinyint(1) NOT NULL DEFAULT 0 COMMENT '是否已讀', `is_deleted` tinyint(1) NOT NULL DEFAULT 0 COMMENT '標識接收者是否已刪除，只有發送者和接收者同時已刪除才能鎮長刪除', `user_type` enum('employer','labour') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '用戶類型，用于區分是雇主還是工人', PRIMARY KEY (`user_type`, `receiver_id`, `message_id`) USING BTREE, INDEX `message_id`(`message_id`) USING BTREE, CONSTRAINT `user_messages_ibfk_1` FOREIGN KEY (`message_id`) REFERENCES `messages` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '用戶擁有的消息' ROW_FORMAT = Dynamic; SET FOREIGN_KEY_CHECKS = 1; ~~~