69 lines
1.9 KiB
SQL
69 lines
1.9 KiB
SQL
-- OnboardX 新版入职登记表与框架实体字段对齐
|
|
-- 仅补列，不删除或重建现有业务数据。
|
|
|
|
SET NAMES utf8mb4;
|
|
|
|
DROP PROCEDURE IF EXISTS `ob_add_column_if_missing`;
|
|
DELIMITER $$
|
|
CREATE PROCEDURE `ob_add_column_if_missing`(
|
|
IN p_table_name VARCHAR(128),
|
|
IN p_column_name VARCHAR(128),
|
|
IN p_column_definition VARCHAR(1000)
|
|
)
|
|
BEGIN
|
|
IF NOT EXISTS (
|
|
SELECT 1
|
|
FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = DATABASE()
|
|
AND TABLE_NAME = p_table_name
|
|
AND COLUMN_NAME = p_column_name
|
|
) THEN
|
|
SET @ddl = CONCAT(
|
|
'ALTER TABLE `', p_table_name, '` ADD COLUMN `',
|
|
p_column_name, '` ', p_column_definition
|
|
);
|
|
PREPARE stmt FROM @ddl;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
END IF;
|
|
END$$
|
|
DELIMITER ;
|
|
|
|
CALL `ob_add_column_if_missing`(
|
|
'ob_variable_definition',
|
|
'Status',
|
|
'VARCHAR(20) NULL DEFAULT ''enable'' COMMENT ''状态'' AFTER `Remark`'
|
|
);
|
|
CALL `ob_add_column_if_missing`(
|
|
'ob_variable_definition',
|
|
'CreateUserId',
|
|
'BIGINT NULL COMMENT ''创建者Id'' AFTER `UpdateTime`'
|
|
);
|
|
CALL `ob_add_column_if_missing`(
|
|
'ob_variable_definition',
|
|
'UpdateUserId',
|
|
'BIGINT NULL COMMENT ''修改者Id'' AFTER `CreateUserId`'
|
|
);
|
|
|
|
CALL `ob_add_column_if_missing`(
|
|
'ob_task_employee_info',
|
|
'Status',
|
|
'VARCHAR(20) NULL DEFAULT ''enable'' COMMENT ''状态'' AFTER `CustomVariablesJson`'
|
|
);
|
|
CALL `ob_add_column_if_missing`(
|
|
'ob_task_employee_info',
|
|
'CreateUserId',
|
|
'BIGINT NULL COMMENT ''创建者Id'' AFTER `UpdateTime`'
|
|
);
|
|
CALL `ob_add_column_if_missing`(
|
|
'ob_task_employee_info',
|
|
'UpdateUserId',
|
|
'BIGINT NULL COMMENT ''修改者Id'' AFTER `CreateUserId`'
|
|
);
|
|
|
|
DROP PROCEDURE IF EXISTS `ob_add_column_if_missing`;
|
|
|
|
-- 检查结果：
|
|
-- SHOW COLUMNS FROM ob_variable_definition;
|
|
-- SHOW COLUMNS FROM ob_task_employee_info;
|