53 lines
2.9 KiB
SQL
53 lines
2.9 KiB
SQL
-- 健康模块步骤一：老人基本信息采集字段。
|
||
-- 仅增加字段，不删除或改写既有身份、志愿者、家属数据；脚本可重复执行。
|
||
SET NAMES utf8mb4;
|
||
|
||
SET @sql = IF((SELECT COUNT(*) FROM information_schema.columns
|
||
WHERE table_schema = DATABASE() AND table_name = 'health_identity' AND column_name = 'id_card') = 0,
|
||
'ALTER TABLE health_identity ADD COLUMN id_card varchar(256) NULL COMMENT ''身份证号，使用 EncryptTypeHandler AES 加密存储'' AFTER real_name',
|
||
'DO 0');
|
||
PREPARE health_step1_stmt FROM @sql;
|
||
EXECUTE health_step1_stmt;
|
||
DEALLOCATE PREPARE health_step1_stmt;
|
||
|
||
SET @sql = IF((SELECT COUNT(*) FROM information_schema.columns
|
||
WHERE table_schema = DATABASE() AND table_name = 'health_identity' AND column_name = 'ethnicity') = 0,
|
||
'ALTER TABLE health_identity ADD COLUMN ethnicity varchar(30) NULL COMMENT ''民族'' AFTER id_card',
|
||
'DO 0');
|
||
PREPARE health_step1_stmt FROM @sql;
|
||
EXECUTE health_step1_stmt;
|
||
DEALLOCATE PREPARE health_step1_stmt;
|
||
|
||
SET @sql = IF((SELECT COUNT(*) FROM information_schema.columns
|
||
WHERE table_schema = DATABASE() AND table_name = 'health_identity' AND column_name = 'has_wechat') = 0,
|
||
'ALTER TABLE health_identity ADD COLUMN has_wechat bit(1) NULL COMMENT ''是否有微信'' AFTER ethnicity',
|
||
'DO 0');
|
||
PREPARE health_step1_stmt FROM @sql;
|
||
EXECUTE health_step1_stmt;
|
||
DEALLOCATE PREPARE health_step1_stmt;
|
||
|
||
SET @sql = IF((SELECT COUNT(*) FROM information_schema.columns
|
||
WHERE table_schema = DATABASE() AND table_name = 'health_elderly_profile' AND column_name = 'collection_step') = 0,
|
||
'ALTER TABLE health_elderly_profile ADD COLUMN collection_step tinyint NOT NULL DEFAULT 1 COMMENT ''资料采集当前步骤，1-7'' AFTER special_needs',
|
||
'DO 0');
|
||
PREPARE health_step1_stmt FROM @sql;
|
||
EXECUTE health_step1_stmt;
|
||
DEALLOCATE PREPARE health_step1_stmt;
|
||
|
||
SET @sql = IF((SELECT COUNT(*) FROM information_schema.columns
|
||
WHERE table_schema = DATABASE() AND table_name = 'health_elderly_profile' AND column_name = 'special_case') = 0,
|
||
'ALTER TABLE health_elderly_profile ADD COLUMN special_case bit(1) NOT NULL DEFAULT b''0'' COMMENT ''管理员强制录入特殊情况标记'' AFTER collection_step',
|
||
'DO 0');
|
||
PREPARE health_step1_stmt FROM @sql;
|
||
EXECUTE health_step1_stmt;
|
||
DEALLOCATE PREPARE health_step1_stmt;
|
||
|
||
SET @sql = IF((SELECT COUNT(*) FROM information_schema.statistics
|
||
WHERE table_schema = DATABASE() AND table_name = 'health_identity'
|
||
AND index_name = 'uk_health_identity_id_card') = 0,
|
||
'ALTER TABLE health_identity ADD UNIQUE KEY uk_health_identity_id_card (tenant_id, id_card)',
|
||
'DO 0');
|
||
PREPARE health_step1_stmt FROM @sql;
|
||
EXECUTE health_step1_stmt;
|
||
DEALLOCATE PREPARE health_step1_stmt;
|