-- ============================================================
|
-- 设备保养模块数据库修复脚本
|
-- 修复保养部位和保养项目字段缺失问题
|
-- 创建时间: 2026-05-18
|
-- ============================================================
|
|
-- ============================================================
|
-- 问题说明：
|
-- 1. maintenance_task 表缺少 maintenance_location (保养部位) 字段
|
-- 2. device_maintenance 表缺少 maintenance_items (保养项目) 字段
|
-- ============================================================
|
|
|
-- ----------------------------
|
-- 修复 1: 在 maintenance_task 表添加 maintenance_location (保养部位) 字段
|
-- ----------------------------
|
-- 先检查字段是否存在，不存在则添加
|
SET @col_exists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
|
WHERE TABLE_SCHEMA = DATABASE()
|
AND TABLE_NAME = 'maintenance_task'
|
AND COLUMN_NAME = 'maintenance_location');
|
SET @sql = IF(@col_exists = 0,
|
'ALTER TABLE `maintenance_task` ADD COLUMN `maintenance_location` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT ''保养部位''',
|
'SELECT ''maintenance_location column already exists'' AS result');
|
PREPARE stmt FROM @sql;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
|
-- ----------------------------
|
-- 修复 2: 在 device_maintenance 表添加 maintenance_items (保养项目) 字段
|
-- ----------------------------
|
-- 先检查字段是否存在，不存在则添加
|
SET @col_exists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
|
WHERE TABLE_SCHEMA = DATABASE()
|
AND TABLE_NAME = 'device_maintenance'
|
AND COLUMN_NAME = 'maintenance_items');
|
SET @sql = IF(@col_exists = 0,
|
'ALTER TABLE `device_maintenance` ADD COLUMN `maintenance_items` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL COMMENT ''保养项目''',
|
'SELECT ''maintenance_items column already exists'' AS result');
|
PREPARE stmt FROM @sql;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
|
-- ============================================================
|
-- 验证执行：查询表结构确认字段已添加
|
-- ============================================================
|
-- SELECT COLUMN_NAME, DATA_TYPE, COLUMN_COMMENT
|
-- FROM INFORMATION_SCHEMA.COLUMNS
|
-- WHERE TABLE_NAME IN ('maintenance_task', 'device_maintenance')
|
-- AND COLUMN_NAME IN ('maintenance_location', 'maintenance_items');
|
