shanxia2.0/backend/scripts/migration_20260712_pl_personnel_affiliation.sql

-- pl_personnel 增加人员归属：self-山峡 carrier-非山峡
SET @col_exists := (
  SELECT COUNT(*)
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'pl_personnel'
    AND COLUMN_NAME = 'personnel_affiliation'
);

SET @ddl := IF(
  @col_exists = 0,
  'ALTER TABLE `pl_personnel`
     ADD COLUMN `personnel_affiliation` varchar(20) NOT NULL DEFAULT ''self''
       COMMENT ''人员归属 self-山峡 carrier-非山峡''
       AFTER `personnel_type`',
  'SELECT ''personnel_affiliation already exists'' AS msg'
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

UPDATE `pl_personnel`
SET `personnel_affiliation` = 'self'
WHERE `personnel_affiliation` IS NULL OR `personnel_affiliation` = '';