79 lines
2.5 KiB
SQL
79 lines
2.5 KiB
SQL
-- ================================================================
|
||
-- 场地表字段修复脚本（保留数据版本）
|
||
-- 用途：为现有 martial_venue 表添加缺失的字段，不删除已有数据
|
||
-- 日期：2025-12-06
|
||
-- ================================================================
|
||
|
||
-- 检查并添加 max_capacity 字段
|
||
SET @col_exists = 0;
|
||
SELECT COUNT(*) INTO @col_exists
|
||
FROM information_schema.COLUMNS
|
||
WHERE TABLE_SCHEMA = DATABASE()
|
||
AND TABLE_NAME = 'martial_venue'
|
||
AND COLUMN_NAME = 'max_capacity';
|
||
|
||
SET @sql = IF(@col_exists = 0,
|
||
'ALTER TABLE martial_venue ADD COLUMN max_capacity int(11) DEFAULT 100 COMMENT ''最大容纳人数'' AFTER venue_code',
|
||
'SELECT ''max_capacity 字段已存在'' AS info'
|
||
);
|
||
|
||
PREPARE stmt FROM @sql;
|
||
EXECUTE stmt;
|
||
DEALLOCATE PREPARE stmt;
|
||
|
||
-- 检查并添加 facilities 字段（如果也缺失）
|
||
SET @col_exists = 0;
|
||
SELECT COUNT(*) INTO @col_exists
|
||
FROM information_schema.COLUMNS
|
||
WHERE TABLE_SCHEMA = DATABASE()
|
||
AND TABLE_NAME = 'martial_venue'
|
||
AND COLUMN_NAME = 'facilities';
|
||
|
||
SET @sql = IF(@col_exists = 0,
|
||
'ALTER TABLE martial_venue ADD COLUMN facilities varchar(500) DEFAULT NULL COMMENT ''场地设施'' AFTER description',
|
||
'SELECT ''facilities 字段已存在'' AS info'
|
||
);
|
||
|
||
PREPARE stmt FROM @sql;
|
||
EXECUTE stmt;
|
||
DEALLOCATE PREPARE stmt;
|
||
|
||
-- 检查并添加 status 字段（如果也缺失）
|
||
SET @col_exists = 0;
|
||
SELECT COUNT(*) INTO @col_exists
|
||
FROM information_schema.COLUMNS
|
||
WHERE TABLE_SCHEMA = DATABASE()
|
||
AND TABLE_NAME = 'martial_venue'
|
||
AND COLUMN_NAME = 'status';
|
||
|
||
SET @sql = IF(@col_exists = 0,
|
||
'ALTER TABLE martial_venue ADD COLUMN status int(2) DEFAULT 1 COMMENT ''状态(0-禁用,1-启用)'' AFTER sort_order',
|
||
'SELECT ''status 字段已存在'' AS info'
|
||
);
|
||
|
||
PREPARE stmt FROM @sql;
|
||
EXECUTE stmt;
|
||
DEALLOCATE PREPARE stmt;
|
||
|
||
-- ================================================================
|
||
-- 验证表结构
|
||
-- ================================================================
|
||
SELECT '字段添加完成，正在验证...' AS info;
|
||
|
||
SELECT COLUMN_NAME, COLUMN_TYPE, COLUMN_DEFAULT, IS_NULLABLE, COLUMN_COMMENT
|
||
FROM information_schema.COLUMNS
|
||
WHERE TABLE_SCHEMA = DATABASE()
|
||
AND TABLE_NAME = 'martial_venue'
|
||
ORDER BY ORDINAL_POSITION;
|
||
|
||
-- 检查 max_capacity 字段是否存在
|
||
SELECT
|
||
CASE
|
||
WHEN COUNT(*) > 0 THEN '✓ max_capacity 字段已成功添加'
|
||
ELSE '✗ max_capacity 字段仍然缺失'
|
||
END AS result
|
||
FROM information_schema.COLUMNS
|
||
WHERE TABLE_SCHEMA = DATABASE()
|
||
AND TABLE_NAME = 'martial_venue'
|
||
AND COLUMN_NAME = 'max_capacity';
|