-- 创建数据库 CREATE DATABASE IF NOT EXISTS rural_household DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE rural_household; -- 创建管理员表 CREATE TABLE IF NOT EXISTS admin_user ( id BIGINT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名', password VARCHAR(100) NOT NULL COMMENT '密码', real_name VARCHAR(50) COMMENT '真实姓名', phone VARCHAR(11) COMMENT '手机号', create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', INDEX idx_username (username) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='管理员表'; -- 插入测试管理员数据 INSERT INTO admin_user (username, password, real_name, phone) VALUES ('admin', 'admin123', '系统管理员', '13800138000'), ('user1', 'user123', '张三', '13800138001'), ('user2', 'user123', '李四', '13800138002'); -- 创建户主信息表 CREATE TABLE IF NOT EXISTS household ( id BIGINT AUTO_INCREMENT PRIMARY KEY, -- 基本信息 householder_name VARCHAR(50) COMMENT '户主姓名', householder_id_number VARCHAR(18) COMMENT '户主身份证号', household_num VARCHAR(50) COMMENT '户号', resident_num VARCHAR(10) COMMENT '人口数', householder_phone VARCHAR(11) COMMENT '户主手机号', labor_num VARCHAR(10) COMMENT '劳动力人数', work_num VARCHAR(10) COMMENT '务工人员数', no_labor_num VARCHAR(10) COMMENT '无劳动力人数', town VARCHAR(50) COMMENT '乡镇', village VARCHAR(50) COMMENT '村', door_number VARCHAR(50) COMMENT '门牌号', -- 户属性 household_type TEXT COMMENT '家庭属性（JSON数组）', hukou_type VARCHAR(50) COMMENT '居住属性', -- 8个单选字段 is_dangerous_house VARCHAR(10) COMMENT '是否危房', has_other_safe_house VARCHAR(10) COMMENT '是否有其他安全住房', has_applied_for_temporary_assistance VARCHAR(10) COMMENT '是否申请临时救助', has_family_doctor_contract VARCHAR(10) COMMENT '家庭医生是否签约', needs_home_teaching VARCHAR(10) COMMENT '是否需要送教上门', has_farmland VARCHAR(10) COMMENT '是否有耕地', needs_startup_loan VARCHAR(10) COMMENT '是否需要创业贷款', knows_how_to_report_monitoring VARCHAR(10) COMMENT '是否知道如何申报检测对象', population_type TEXT COMMENT '人口类型（JSON数组）', -- 住房情况 house_structure VARCHAR(50) COMMENT '住房结构', house_area VARCHAR(20) COMMENT '住房面积', -- 三保障情况 medical_insurance_num VARCHAR(10) COMMENT '医疗保险人数', pension_insurance_num VARCHAR(10) COMMENT '养老保险人数', insurance_level VARCHAR(20) COMMENT '保险等级', dibao_num VARCHAR(10) COMMENT '低保人数', dibao_amount VARCHAR(20) COMMENT '低保金额', tekun_num VARCHAR(10) COMMENT '特困人数', tekun_amount VARCHAR(20) COMMENT '特困金额', disabled_num VARCHAR(10) COMMENT '残疾人数', disabled_type VARCHAR(50) COMMENT '残疾类型', disabled_amount VARCHAR(20) COMMENT '残疾金额', pension_num VARCHAR(10) COMMENT '养老金人数', pension_amount VARCHAR(20) COMMENT '养老金金额', -- 教育保障 edu_student_num VARCHAR(10) COMMENT '义务教育学生人数', college_student_num VARCHAR(10) COMMENT '大中专学生人数', -- 生产生活情况 industry_status TEXT COMMENT '产业发展情况', land_area VARCHAR(20) COMMENT '确权面积', breeding_status TEXT COMMENT '养殖情况', business_status TEXT COMMENT '个体经营情况', loan_purpose VARCHAR(100) COMMENT '贷款用途', -- 监测对象、综合研判、重点关注对象 comprehensive_judgment TEXT COMMENT '综合研判', key_attention TEXT COMMENT '是否"八必访"重点关注对象', -- 培训、就业、诉求、建议 training_need TEXT COMMENT '培训需求', job_need TEXT COMMENT '就业需求', other_need TEXT COMMENT '其他诉求', suggestion TEXT COMMENT '意见建议', -- 满意度 village_satisfaction INT COMMENT '对村两委满意度', work_team_satisfaction INT COMMENT '对驻村工作队满意度', -- 签名 signature_img LONGTEXT COMMENT '签名图片（base64）', -- 家庭成员 members TEXT COMMENT '家庭成员（JSON数组）', -- 提交信息 submitter_id BIGINT COMMENT '提交人ID', submitter_name VARCHAR(50) COMMENT '提交人姓名', submit_status TINYINT DEFAULT 0 COMMENT '提交状态：0-暂存，1-已提交', create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', INDEX idx_householder_name (householder_name), INDEX idx_householder_id_number (householder_id_number), INDEX idx_household_num (household_num), INDEX idx_town (town), INDEX idx_village (village), INDEX idx_submitter_id (submitter_id), INDEX idx_submit_status (submit_status), INDEX idx_create_time (create_time) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='户主信息表'; -- 插入测试数据 INSERT INTO household ( householder_name, householder_id_number, household_num, resident_num, householder_phone, labor_num, work_num, no_labor_num, town, village, door_number, household_type, hukou_type, is_dangerous_house, has_other_safe_house, has_applied_for_temporary_assistance, has_family_doctor_contract, needs_home_teaching, has_farmland, needs_startup_loan, knows_how_to_report_monitoring, population_type, house_structure, house_area, medical_insurance_num, pension_insurance_num, insurance_level, dibao_num, dibao_amount, tekun_num, tekun_amount, disabled_num, disabled_type, disabled_amount, pension_num, pension_amount, edu_student_num, college_student_num, industry_status, land_area, breeding_status, business_status, loan_purpose, comprehensive_judgment, key_attention, training_need, job_need, other_need, suggestion, village_satisfaction, work_team_satisfaction, signature_img, members, submitter_id, submitter_name, submit_status ) VALUES ( '张三', '110101199001011234', 'HH001', '4', '13800138000', '2', '1', '0', '某某镇', '某某村', '123号', '["脱贫户", "一般户"]', '常住户', '否', '是', '否', '是', '否', '是', '否', '是', '["家中有2个或以上义务教育阶段学生"]', '砖混结构', '120平方米', '4', '4', '基本医疗保险', '0', '0', '0', '0', '0', '', '0', '2', '2000', '2', '0', '种植水稻', '5亩', '养猪2头', '小卖部', '', '家庭收入稳定，无返贫风险', '非重点关注对象', '希望参加农业技术培训', '希望就近就业', '希望改善道路条件', '建议加强基础设施建设', 4, 5, '', '[{"name":"张三","idNumber":"110101199001011234","phone":"13800138000","health":"健康","job":"务农"},{"name":"李四","idNumber":"110101199501011234","phone":"13800138001","health":"健康","job":"务工"}]', 1, 'admin', 1 );