82 lines
5.1 KiB
SQL
Executable File
82 lines
5.1 KiB
SQL
Executable File
-- 处方增加共享字段（安全版本 - 字段存在则跳过）
|
|
|
|
-- 1. 处方名称
|
|
SET @col_exists = 0;
|
|
SELECT COUNT(*) INTO @col_exists FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'zyt_tcm_prescription' AND COLUMN_NAME = 'prescription_name';
|
|
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `zyt_tcm_prescription` ADD COLUMN `prescription_name` varchar(100) NOT NULL DEFAULT "" COMMENT "处方名称" AFTER `sn`', 'SELECT "prescription_name already exists"');
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
-- 2. 处方类型
|
|
SET @col_exists = 0;
|
|
SELECT COUNT(*) INTO @col_exists FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'zyt_tcm_prescription' AND COLUMN_NAME = 'prescription_type';
|
|
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `zyt_tcm_prescription` ADD COLUMN `prescription_type` varchar(50) NOT NULL DEFAULT "浓缩水丸" COMMENT "处方类型：浓缩水丸、饮片、颗粒、丸剂、散剂等" AFTER `prescription_name`', 'SELECT "prescription_type already exists"');
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
-- 3. 舌象图片
|
|
SET @col_exists = 0;
|
|
SELECT COUNT(*) INTO @col_exists FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'zyt_tcm_prescription' AND COLUMN_NAME = 'tongue_image';
|
|
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `zyt_tcm_prescription` ADD COLUMN `tongue_image` varchar(255) NOT NULL DEFAULT "" COMMENT "舌象图片" AFTER `tongue`', 'SELECT "tongue_image already exists"');
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
-- 4. 脉象详情
|
|
SET @col_exists = 0;
|
|
SELECT COUNT(*) INTO @col_exists FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'zyt_tcm_prescription' AND COLUMN_NAME = 'pulse_condition';
|
|
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `zyt_tcm_prescription` ADD COLUMN `pulse_condition` varchar(100) NOT NULL DEFAULT "" COMMENT "脉象详情" AFTER `pulse`', 'SELECT "pulse_condition already exists"');
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
-- 5. 服用天数
|
|
SET @col_exists = 0;
|
|
SELECT COUNT(*) INTO @col_exists FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'zyt_tcm_prescription' AND COLUMN_NAME = 'usage_days';
|
|
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `zyt_tcm_prescription` ADD COLUMN `usage_days` int(11) NOT NULL DEFAULT 7 COMMENT "服用天数" AFTER `dose_unit`', 'SELECT "usage_days already exists"');
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
-- 6. 服用时间
|
|
SET @col_exists = 0;
|
|
SELECT COUNT(*) INTO @col_exists FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'zyt_tcm_prescription' AND COLUMN_NAME = 'usage_time';
|
|
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `zyt_tcm_prescription` ADD COLUMN `usage_time` varchar(50) NOT NULL DEFAULT "饭前" COMMENT "服用时间：饭前、饭后、饭中、空腹、睡前、晨起、随时" AFTER `usage_instruction`', 'SELECT "usage_time already exists"');
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
-- 7. 服用方式
|
|
SET @col_exists = 0;
|
|
SELECT COUNT(*) INTO @col_exists FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'zyt_tcm_prescription' AND COLUMN_NAME = 'usage_way';
|
|
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `zyt_tcm_prescription` ADD COLUMN `usage_way` varchar(50) NOT NULL DEFAULT "温水送服" COMMENT "服用方式：温水送服、开水冲服、黄酒送服等" AFTER `usage_time`', 'SELECT "usage_way already exists"');
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
-- 8. 忌口
|
|
SET @col_exists = 0;
|
|
SELECT COUNT(*) INTO @col_exists FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'zyt_tcm_prescription' AND COLUMN_NAME = 'dietary_taboo';
|
|
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `zyt_tcm_prescription` ADD COLUMN `dietary_taboo` varchar(200) NOT NULL DEFAULT "" COMMENT "忌口（逗号分隔）：辛辣食物、生冷食物、油腻食物等" AFTER `usage_way`', 'SELECT "dietary_taboo already exists"');
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
-- 9. 其他服用说明
|
|
SET @col_exists = 0;
|
|
SELECT COUNT(*) INTO @col_exists FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'zyt_tcm_prescription' AND COLUMN_NAME = 'usage_notes';
|
|
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `zyt_tcm_prescription` ADD COLUMN `usage_notes` varchar(200) NOT NULL DEFAULT "" COMMENT "其他服用说明" AFTER `dietary_taboo`', 'SELECT "usage_notes already exists"');
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
-- 10. 是否共享
|
|
SET @col_exists = 0;
|
|
SELECT COUNT(*) INTO @col_exists FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'zyt_tcm_prescription' AND COLUMN_NAME = 'is_shared';
|
|
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `zyt_tcm_prescription` ADD COLUMN `is_shared` tinyint(1) NOT NULL DEFAULT 0 COMMENT "是否共享：0-不共享(仅自己可见) 1-共享(所有人可见)" AFTER `template_id`', 'SELECT "is_shared already exists"');
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|