数据库 · 棚大大(雇主|民工端)

~~~[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; ~~~