-- 设备区域（车间/位置）父子递归结构
|
CREATE TABLE IF NOT EXISTS `device_area` (
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
|
`name` varchar(255) NOT NULL COMMENT '区域/车间/位置名称',
|
`parent_id` bigint NULL COMMENT '父级区域ID，根节点为NULL',
|
`ancestors` varchar(1000) NULL COMMENT '祖级路径，如 1,2,3',
|
`sort` int NULL COMMENT '排序',
|
`dept_id` bigint NULL COMMENT '部门ID',
|
`tenant_id` bigint NULL COMMENT '租户ID',
|
`create_user` int NULL COMMENT '创建人',
|
`create_time` datetime NULL COMMENT '创建时间',
|
`update_user` int NULL COMMENT '更新人',
|
`update_time` datetime NULL COMMENT '更新时间',
|
PRIMARY KEY (`id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='设备区域表（车间/位置）';
|
|
-- 设备台账关联区域（幂等：已存在列时跳过）
|
SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS
|
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'device_ledger' AND COLUMN_NAME = 'area_id');
|
SET @ddl = IF(@col_exists = 0,
|
'ALTER TABLE `device_ledger` ADD COLUMN `area_id` bigint NULL COMMENT ''设备区域ID'' AFTER `dept_id`',
|
'SELECT 1');
|
PREPARE stmt FROM @ddl;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
