-- ============================================================================ -- 康意智能 mom-kyzn 生产库修复脚本(2026-10-30) -- 生成方式:解析 359 个 DO 实体,与生产库 actual schema 逐一比对取差集 -- 内容:41 个缺失列 + 14 张缺失表 + mes_wm_return_issue.type 类型修正 -- 特性:幂等,可重复执行 -- ============================================================================ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- --------------------------------------------------------------------------- -- 一、补齐缺失列(41 个) -- --------------------------------------------------------------------------- -- ---------- after_sale_visit ---------- SET @exists := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'after_sale_visit' AND column_name = 'recurred'); SET @sql := IF(@exists = 0, "ALTER TABLE `after_sale_visit` ADD COLUMN `recurred` bit(1) DEFAULT NULL", "SELECT 1"); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @exists := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'after_sale_visit' AND column_name = 'satisfied'); SET @sql := IF(@exists = 0, "ALTER TABLE `after_sale_visit` ADD COLUMN `satisfied` bit(1) DEFAULT NULL", "SELECT 1"); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; -- ---------- hrm_attendance_record ---------- SET @exists := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'hrm_attendance_record' AND column_name = 'clock_in_accuracy'); SET @sql := IF(@exists = 0, "ALTER TABLE `hrm_attendance_record` ADD COLUMN `clock_in_accuracy` decimal(24,6) DEFAULT NULL", "SELECT 1"); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @exists := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'hrm_attendance_record' AND column_name = 'clock_in_address'); SET @sql := IF(@exists = 0, "ALTER TABLE `hrm_attendance_record` ADD COLUMN `clock_in_address` varchar(255) DEFAULT NULL", "SELECT 1"); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @exists := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'hrm_attendance_record' AND column_name = 'clock_in_distance'); SET @sql := IF(@exists = 0, "ALTER TABLE `hrm_attendance_record` ADD COLUMN `clock_in_distance` decimal(24,6) DEFAULT NULL", "SELECT 1"); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @exists := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'hrm_attendance_record' AND column_name = 'clock_in_latitude'); SET @sql := IF(@exists = 0, "ALTER TABLE `hrm_attendance_record` ADD COLUMN `clock_in_latitude` decimal(24,6) DEFAULT NULL", "SELECT 1"); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @exists := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'hrm_attendance_record' AND column_name = 'clock_in_longitude'); SET @sql := IF(@exists = 0, "ALTER TABLE `hrm_attendance_record` ADD COLUMN `clock_in_longitude` decimal(24,6) DEFAULT NULL", "SELECT 1"); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @exists := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'hrm_attendance_record' AND column_name = 'clock_out_accuracy'); SET @sql := IF(@exists = 0, "ALTER TABLE `hrm_attendance_record` ADD COLUMN `clock_out_accuracy` decimal(24,6) DEFAULT NULL", "SELECT 1"); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @exists := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'hrm_attendance_record' AND column_name = 'clock_out_address'); SET @sql := IF(@exists = 0, "ALTER TABLE `hrm_attendance_record` ADD COLUMN `clock_out_address` varchar(255) DEFAULT NULL", "SELECT 1"); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @exists := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'hrm_attendance_record' AND column_name = 'clock_out_distance'); SET @sql := IF(@exists = 0, "ALTER TABLE `hrm_attendance_record` ADD COLUMN `clock_out_distance` decimal(24,6) DEFAULT NULL", "SELECT 1"); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @exists := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'hrm_attendance_record' AND column_name = 'clock_out_latitude'); SET @sql := IF(@exists = 0, "ALTER TABLE `hrm_attendance_record` ADD COLUMN `clock_out_latitude` decimal(24,6) DEFAULT NULL", "SELECT 1"); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @exists := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'hrm_attendance_record' AND column_name = 'clock_out_longitude'); SET @sql := IF(@exists = 0, "ALTER TABLE `hrm_attendance_record` ADD COLUMN `clock_out_longitude` decimal(24,6) DEFAULT NULL", "SELECT 1"); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; -- ---------- hrm_attendance_rule ---------- SET @exists := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'hrm_attendance_rule' AND column_name = 'coordinate_system'); SET @sql := IF(@exists = 0, "ALTER TABLE `hrm_attendance_rule` ADD COLUMN `coordinate_system` varchar(255) DEFAULT NULL", "SELECT 1"); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @exists := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'hrm_attendance_rule' AND column_name = 'latitude'); SET @sql := IF(@exists = 0, "ALTER TABLE `hrm_attendance_rule` ADD COLUMN `latitude` decimal(24,6) DEFAULT NULL", "SELECT 1"); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @exists := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'hrm_attendance_rule' AND column_name = 'location_address'); SET @sql := IF(@exists = 0, "ALTER TABLE `hrm_attendance_rule` ADD COLUMN `location_address` varchar(255) DEFAULT NULL", "SELECT 1"); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @exists := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'hrm_attendance_rule' AND column_name = 'location_name'); SET @sql := IF(@exists = 0, "ALTER TABLE `hrm_attendance_rule` ADD COLUMN `location_name` varchar(255) DEFAULT NULL", "SELECT 1"); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @exists := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'hrm_attendance_rule' AND column_name = 'location_required'); SET @sql := IF(@exists = 0, "ALTER TABLE `hrm_attendance_rule` ADD COLUMN `location_required` bit(1) DEFAULT NULL", "SELECT 1"); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @exists := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'hrm_attendance_rule' AND column_name = 'longitude'); SET @sql := IF(@exists = 0, "ALTER TABLE `hrm_attendance_rule` ADD COLUMN `longitude` decimal(24,6) DEFAULT NULL", "SELECT 1"); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @exists := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'hrm_attendance_rule' AND column_name = 'radius_meters'); SET @sql := IF(@exists = 0, "ALTER TABLE `hrm_attendance_rule` ADD COLUMN `radius_meters` int DEFAULT NULL", "SELECT 1"); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; -- ---------- mes_pro_work_order ---------- SET @exists := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'mes_pro_work_order' AND column_name = 'feedback_approve'); SET @sql := IF(@exists = 0, "ALTER TABLE `mes_pro_work_order` ADD COLUMN `feedback_approve` bit(1) DEFAULT NULL", "SELECT 1"); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; -- ---------- mes_wm_outsource_issue_line ---------- SET @exists := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'mes_wm_outsource_issue_line' AND column_name = 'batch_id'); SET @sql := IF(@exists = 0, "ALTER TABLE `mes_wm_outsource_issue_line` ADD COLUMN `batch_id` bigint DEFAULT NULL", "SELECT 1"); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @exists := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'mes_wm_outsource_issue_line' AND column_name = 'material_stock_id'); SET @sql := IF(@exists = 0, "ALTER TABLE `mes_wm_outsource_issue_line` ADD COLUMN `material_stock_id` bigint DEFAULT NULL", "SELECT 1"); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; -- ---------- mes_wm_outsource_receipt_line ---------- SET @exists := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'mes_wm_outsource_receipt_line' AND column_name = 'batch_code'); SET @sql := IF(@exists = 0, "ALTER TABLE `mes_wm_outsource_receipt_line` ADD COLUMN `batch_code` varchar(255) DEFAULT NULL", "SELECT 1"); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @exists := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'mes_wm_outsource_receipt_line' AND column_name = 'batch_id'); SET @sql := IF(@exists = 0, "ALTER TABLE `mes_wm_outsource_receipt_line` ADD COLUMN `batch_id` bigint DEFAULT NULL", "SELECT 1"); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @exists := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'mes_wm_outsource_receipt_line' AND column_name = 'expire_date'); SET @sql := IF(@exists = 0, "ALTER TABLE `mes_wm_outsource_receipt_line` ADD COLUMN `expire_date` datetime DEFAULT NULL", "SELECT 1"); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @exists := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'mes_wm_outsource_receipt_line' AND column_name = 'lot_number'); SET @sql := IF(@exists = 0, "ALTER TABLE `mes_wm_outsource_receipt_line` ADD COLUMN `lot_number` varchar(255) DEFAULT NULL", "SELECT 1"); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @exists := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'mes_wm_outsource_receipt_line' AND column_name = 'production_date'); SET @sql := IF(@exists = 0, "ALTER TABLE `mes_wm_outsource_receipt_line` ADD COLUMN `production_date` datetime DEFAULT NULL", "SELECT 1"); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; -- ---------- mes_wm_package_line ---------- SET @exists := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'mes_wm_package_line' AND column_name = 'expire_date'); SET @sql := IF(@exists = 0, "ALTER TABLE `mes_wm_package_line` ADD COLUMN `expire_date` datetime DEFAULT NULL", "SELECT 1"); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @exists := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'mes_wm_package_line' AND column_name = 'work_order_id'); SET @sql := IF(@exists = 0, "ALTER TABLE `mes_wm_package_line` ADD COLUMN `work_order_id` bigint DEFAULT NULL", "SELECT 1"); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; -- ---------- mes_wm_stock_taking_task ---------- SET @exists := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'mes_wm_stock_taking_task' AND column_name = 'blind_flag'); SET @sql := IF(@exists = 0, "ALTER TABLE `mes_wm_stock_taking_task` ADD COLUMN `blind_flag` bit(1) DEFAULT NULL", "SELECT 1"); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @exists := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'mes_wm_stock_taking_task' AND column_name = 'end_time'); SET @sql := IF(@exists = 0, "ALTER TABLE `mes_wm_stock_taking_task` ADD COLUMN `end_time` datetime DEFAULT NULL", "SELECT 1"); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @exists := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'mes_wm_stock_taking_task' AND column_name = 'frozen'); SET @sql := IF(@exists = 0, "ALTER TABLE `mes_wm_stock_taking_task` ADD COLUMN `frozen` bit(1) DEFAULT NULL", "SELECT 1"); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @exists := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'mes_wm_stock_taking_task' AND column_name = 'start_time'); SET @sql := IF(@exists = 0, "ALTER TABLE `mes_wm_stock_taking_task` ADD COLUMN `start_time` datetime DEFAULT NULL", "SELECT 1"); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @exists := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'mes_wm_stock_taking_task' AND column_name = 'taking_date'); SET @sql := IF(@exists = 0, "ALTER TABLE `mes_wm_stock_taking_task` ADD COLUMN `taking_date` datetime DEFAULT NULL", "SELECT 1"); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @exists := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'mes_wm_stock_taking_task' AND column_name = 'type'); SET @sql := IF(@exists = 0, "ALTER TABLE `mes_wm_stock_taking_task` ADD COLUMN `type` int DEFAULT NULL", "SELECT 1"); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @exists := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'mes_wm_stock_taking_task' AND column_name = 'user_id'); SET @sql := IF(@exists = 0, "ALTER TABLE `mes_wm_stock_taking_task` ADD COLUMN `user_id` bigint DEFAULT NULL", "SELECT 1"); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; -- ---------- mes_wm_stock_taking_task_line ---------- SET @exists := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'mes_wm_stock_taking_task_line' AND column_name = 'batch_code'); SET @sql := IF(@exists = 0, "ALTER TABLE `mes_wm_stock_taking_task_line` ADD COLUMN `batch_code` varchar(255) DEFAULT NULL", "SELECT 1"); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @exists := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'mes_wm_stock_taking_task_line' AND column_name = 'status'); SET @sql := IF(@exists = 0, "ALTER TABLE `mes_wm_stock_taking_task_line` ADD COLUMN `status` int DEFAULT NULL", "SELECT 1"); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @exists := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'mes_wm_stock_taking_task_line' AND column_name = 'taking_quantity'); SET @sql := IF(@exists = 0, "ALTER TABLE `mes_wm_stock_taking_task_line` ADD COLUMN `taking_quantity` decimal(24,6) DEFAULT NULL", "SELECT 1"); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; -- ---------- mes_wm_stock_taking_task_result ---------- SET @exists := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'mes_wm_stock_taking_task_result' AND column_name = 'batch_code'); SET @sql := IF(@exists = 0, "ALTER TABLE `mes_wm_stock_taking_task_result` ADD COLUMN `batch_code` varchar(255) DEFAULT NULL", "SELECT 1"); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @exists := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'mes_wm_stock_taking_task_result' AND column_name = 'taking_quantity'); SET @sql := IF(@exists = 0, "ALTER TABLE `mes_wm_stock_taking_task_result` ADD COLUMN `taking_quantity` decimal(24,6) DEFAULT NULL", "SELECT 1"); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; -- --------------------------------------------------------------------------- -- 二、修正 mes_wm_return_issue.type:varchar(255) -> int -- 历史数据存的是中文,先转换为枚举值 1/2/3 -- --------------------------------------------------------------------------- SET @is_varchar := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'mes_wm_return_issue' AND column_name = 'type' AND data_type IN ('varchar','char','text')); SET @sql := IF(@is_varchar > 0, "UPDATE `mes_wm_return_issue` SET `type` = CASE `type` WHEN '余料退料' THEN '1' WHEN '不良退料' THEN '2' WHEN '其他退料' THEN '3' WHEN '' THEN NULL ELSE `type` END WHERE `type` IS NOT NULL AND `type` NOT REGEXP '^[0-9]+$'", "SELECT 1"); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET @is_varchar := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'mes_wm_return_issue' AND column_name = 'type' AND data_type IN ('varchar','char','text')); SET @sql := IF(@is_varchar > 0, "ALTER TABLE `mes_wm_return_issue` MODIFY COLUMN `type` int DEFAULT NULL COMMENT '退料类型:1-余料退料 2-不良退料 3-其他退料'", "SELECT 1"); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; -- --------------------------------------------------------------------------- -- 三、创建缺失表(14 张,按 DO 实体生成) -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS `member_user` ( `id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号', `mobile` varchar(11) NOT NULL COMMENT '手机号', `email` varchar(50) DEFAULT NULL COMMENT '邮箱', `password` varchar(100) NOT NULL DEFAULT '' COMMENT '密码', `status` tinyint NOT NULL COMMENT '状态', `register_ip` varchar(32) NOT NULL COMMENT '注册 IP', `register_terminal` int DEFAULT NULL COMMENT '注册终端', `login_ip` varchar(50) DEFAULT '' COMMENT '最后登录IP', `login_date` datetime DEFAULT NULL COMMENT '最后登录时间', `nickname` varchar(30) NOT NULL DEFAULT '' COMMENT '用户昵称', `avatar` varchar(512) NOT NULL DEFAULT '' COMMENT '头像', `name` varchar(30) DEFAULT NULL COMMENT '真实名字', `sex` tinyint DEFAULT NULL COMMENT '性别', `birthday` datetime DEFAULT NULL COMMENT '出生日期', `area_id` int DEFAULT NULL COMMENT '所在地', `mark` varchar(255) DEFAULT NULL COMMENT '用户备注', `point` int DEFAULT 0 COMMENT '积分', `tag_ids` varchar(255) DEFAULT NULL COMMENT '用户标签编号列表,逗号分隔', `level_id` bigint DEFAULT NULL COMMENT '等级编号', `experience` int DEFAULT NULL COMMENT '经验', `group_id` bigint DEFAULT NULL COMMENT '用户分组编号', `creator` varchar(64) DEFAULT '' COMMENT '创建者', `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `updater` varchar(64) DEFAULT '' COMMENT '更新者', `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除', PRIMARY KEY (`id`), KEY `idx_mobile` (`mobile`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='会员用户'; CREATE TABLE IF NOT EXISTS `member_tag` ( `id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号', `name` varchar(30) NOT NULL COMMENT '标签名称', `creator` varchar(64) DEFAULT '' COMMENT '创建者', `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `updater` varchar(64) DEFAULT '' COMMENT '更新者', `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='会员标签'; CREATE TABLE IF NOT EXISTS `member_level` ( `id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号', `name` varchar(30) NOT NULL COMMENT '等级名称', `level` int NOT NULL COMMENT '等级', `experience` int NOT NULL COMMENT '升级经验', `discount_percent` int NOT NULL COMMENT '折扣百分比', `icon` varchar(255) NOT NULL COMMENT '图标', `background_url` varchar(255) NOT NULL COMMENT '背景图', `status` tinyint NOT NULL DEFAULT 0 COMMENT '状态', `creator` varchar(64) DEFAULT '' COMMENT '创建者', `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `updater` varchar(64) DEFAULT '' COMMENT '更新者', `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='会员等级'; CREATE TABLE IF NOT EXISTS `member_group` ( `id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号', `name` varchar(30) NOT NULL COMMENT '分组名称', `remark` varchar(255) DEFAULT NULL COMMENT '备注', `status` tinyint NOT NULL DEFAULT 0 COMMENT '状态', `creator` varchar(64) DEFAULT '' COMMENT '创建者', `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `updater` varchar(64) DEFAULT '' COMMENT '更新者', `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='会员分组'; CREATE TABLE IF NOT EXISTS `member_config` ( `id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号', `point_trade_deduct_enable` bit(1) NOT NULL DEFAULT b'0' COMMENT '积分抵扣开关', `point_trade_deduct_unit_price` int DEFAULT NULL COMMENT '积分抵扣单价', `point_trade_deduct_max_price` int DEFAULT NULL COMMENT '积分抵扣最大金额', `point_trade_give_point` int DEFAULT NULL COMMENT '交易赠送积分', `creator` varchar(64) DEFAULT '' COMMENT '创建者', `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `updater` varchar(64) DEFAULT '' COMMENT '更新者', `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='会员配置'; CREATE TABLE IF NOT EXISTS `member_experience_record` ( `id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号', `user_id` bigint NOT NULL COMMENT '用户编号', `biz_type` int NOT NULL COMMENT '业务类型', `biz_id` varchar(64) NOT NULL COMMENT '业务编号', `title` varchar(255) NOT NULL COMMENT '标题', `description` varchar(255) NOT NULL COMMENT '描述', `experience` int NOT NULL COMMENT '经验', `total_experience` int NOT NULL COMMENT '变更后的经验', `creator` varchar(64) DEFAULT '' COMMENT '创建者', `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `updater` varchar(64) DEFAULT '' COMMENT '更新者', `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除', PRIMARY KEY (`id`), KEY `idx_user_id` (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='会员经验记录'; CREATE TABLE IF NOT EXISTS `member_level_record` ( `id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号', `user_id` bigint NOT NULL COMMENT '用户编号', `level_id` bigint NOT NULL COMMENT '等级编号', `level` int NOT NULL COMMENT '等级', `discount_percent` int NOT NULL COMMENT '折扣百分比', `experience` int NOT NULL COMMENT '经验', `user_experience` int NOT NULL COMMENT '用户经验', `remark` varchar(255) DEFAULT NULL COMMENT '备注', `description` varchar(255) DEFAULT NULL COMMENT '描述', `creator` varchar(64) DEFAULT '' COMMENT '创建者', `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `updater` varchar(64) DEFAULT '' COMMENT '更新者', `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除', PRIMARY KEY (`id`), KEY `idx_user_id` (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='会员等级记录'; CREATE TABLE IF NOT EXISTS `member_point_record` ( `id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号', `user_id` bigint NOT NULL COMMENT '用户编号', `biz_id` varchar(64) NOT NULL COMMENT '业务编号', `biz_type` int NOT NULL COMMENT '业务类型', `title` varchar(255) NOT NULL COMMENT '标题', `description` varchar(255) NOT NULL COMMENT '描述', `point` int NOT NULL COMMENT '积分', `total_point` int NOT NULL COMMENT '变更后的积分', `creator` varchar(64) DEFAULT '' COMMENT '创建者', `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `updater` varchar(64) DEFAULT '' COMMENT '更新者', `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除', PRIMARY KEY (`id`), KEY `idx_user_id` (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='会员积分记录'; CREATE TABLE IF NOT EXISTS `member_sign_in_config` ( `id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号', `day` int NOT NULL COMMENT '第几天', `point` int NOT NULL COMMENT '赠送积分', `experience` int NOT NULL COMMENT '赠送经验', `status` tinyint NOT NULL DEFAULT 0 COMMENT '状态', `creator` varchar(64) DEFAULT '' COMMENT '创建者', `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `updater` varchar(64) DEFAULT '' COMMENT '更新者', `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='签到配置'; CREATE TABLE IF NOT EXISTS `member_sign_in_record` ( `id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号', `user_id` bigint NOT NULL COMMENT '用户编号', `day` int NOT NULL COMMENT '第几天', `point` int NOT NULL COMMENT '赠送积分', `experience` int NOT NULL COMMENT '赠送经验', `creator` varchar(64) DEFAULT '' COMMENT '创建者', `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `updater` varchar(64) DEFAULT '' COMMENT '更新者', `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除', PRIMARY KEY (`id`), KEY `idx_user_id` (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='签到记录'; CREATE TABLE IF NOT EXISTS `hrm_social_security_config` ( `id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号', `name` varchar(100) NOT NULL COMMENT '方案名称', `effective_date` date DEFAULT NULL COMMENT '生效日期', `pension_company_ratio` decimal(10,4) DEFAULT NULL COMMENT '养老-公司比例', `pension_personal_ratio` decimal(10,4) DEFAULT NULL COMMENT '养老-个人比例', `medical_company_ratio` decimal(10,4) DEFAULT NULL COMMENT '医疗-公司比例', `medical_personal_ratio` decimal(10,4) DEFAULT NULL COMMENT '医疗-个人比例', `unemployment_company_ratio` decimal(10,4) DEFAULT NULL COMMENT '失业-公司比例', `unemployment_personal_ratio` decimal(10,4) DEFAULT NULL COMMENT '失业-个人比例', `work_injury_ratio` decimal(10,4) DEFAULT NULL COMMENT '工伤-公司比例', `maternity_ratio` decimal(10,4) DEFAULT NULL COMMENT '生育-公司比例', `housing_fund_company_ratio` decimal(10,4) DEFAULT NULL COMMENT '公积金-公司比例', `housing_fund_personal_ratio` decimal(10,4) DEFAULT NULL COMMENT '公积金-个人比例', `social_security_base_min` decimal(12,2) DEFAULT NULL COMMENT '社保基数下限', `social_security_base_max` decimal(12,2) DEFAULT NULL COMMENT '社保基数上限', `housing_fund_base_min` decimal(12,2) DEFAULT NULL COMMENT '公积金基数下限', `housing_fund_base_max` decimal(12,2) DEFAULT NULL COMMENT '公积金基数上限', `status` int DEFAULT NULL COMMENT '状态', `remark` varchar(255) DEFAULT NULL COMMENT '备注', `creator` varchar(64) DEFAULT '' COMMENT '创建者', `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `updater` varchar(64) DEFAULT '' COMMENT '更新者', `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='社保配置'; CREATE TABLE IF NOT EXISTS `hrm_user_handover` ( `id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号', `from_user_id` bigint NULL COMMENT '/', `to_user_id` bigint NULL COMMENT '/', `resignation_id` bigint NULL COMMENT '/', `handover_status` int NULL COMMENT '/', `handover_time` datetime NULL COMMENT '/', `creator` varchar(64) DEFAULT '' COMMENT '创建者', `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `updater` varchar(64) DEFAULT '' COMMENT '更新者', `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='hrm_user_handover'; CREATE TABLE IF NOT EXISTS `member_address` ( `id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号', `user_id` bigint NULL COMMENT '/', `name` varchar(255) NULL COMMENT '/', `mobile` varchar(255) NULL COMMENT '/', `area_id` bigint NULL COMMENT '/', `detail_address` varchar(255) NULL COMMENT '/', `default_status` bit(1) NOT NULL DEFAULT b'0' COMMENT '/', `creator` varchar(64) DEFAULT '' COMMENT '创建者', `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `updater` varchar(64) DEFAULT '' COMMENT '更新者', `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='member_address'; CREATE TABLE IF NOT EXISTS `wms_warehouse_approval_config` ( `id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号', `warehouse_id` bigint NULL COMMENT '/', `biz_type` varchar(255) NULL COMMENT '/', `approval_enabled` bit(1) NOT NULL DEFAULT b'0' COMMENT '/', `process_definition_key` varchar(255) NULL COMMENT '/', `category_code` varchar(255) NULL COMMENT '/', `creator` varchar(64) DEFAULT '' COMMENT '创建者', `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `updater` varchar(64) DEFAULT '' COMMENT '更新者', `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='wms_warehouse_approval_config'; SET FOREIGN_KEY_CHECKS = 1;