54 lines
4.2 KiB
SQL
54 lines
4.2 KiB
SQL
-- BOX设备表
|
||
DROP TABLE IF EXISTS `bms_box_device`;
|
||
CREATE TABLE `bms_box_device` (
|
||
`box_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'BOX设备ID',
|
||
`box_code` varchar(50) NOT NULL COMMENT 'BOX设备编号',
|
||
`box_name` varchar(100) NOT NULL COMMENT 'BOX设备名称',
|
||
`box_type` varchar(50) DEFAULT NULL COMMENT 'BOX设备类型',
|
||
`manufacturer` varchar(100) DEFAULT NULL COMMENT '制造商',
|
||
`model` varchar(100) DEFAULT NULL COMMENT '型号',
|
||
`serial_number` varchar(100) DEFAULT NULL COMMENT '序列号',
|
||
`firmware_version` varchar(50) DEFAULT NULL COMMENT '固件版本',
|
||
`hardware_version` varchar(50) DEFAULT NULL COMMENT '硬件版本',
|
||
`install_location` varchar(200) DEFAULT NULL COMMENT '安装位置',
|
||
`install_date` datetime DEFAULT NULL COMMENT '安装日期',
|
||
`status` tinyint(1) DEFAULT '1' COMMENT '设备状态（0离线 1在线 2故障 3维护）',
|
||
`communication_protocol` varchar(50) DEFAULT 'MQTT' COMMENT '通信协议',
|
||
`ip_address` varchar(50) DEFAULT NULL COMMENT 'IP地址',
|
||
`port` int(11) DEFAULT NULL COMMENT '端口号',
|
||
`battery_count` int(11) DEFAULT '0' COMMENT '电池数量',
|
||
`total_capacity` decimal(10,2) DEFAULT '0.00' COMMENT '总容量(Ah)',
|
||
`total_voltage` decimal(8,2) DEFAULT '0.00' COMMENT '总电压(V)',
|
||
`total_current` decimal(8,2) DEFAULT '0.00' COMMENT '总电流(A)',
|
||
`total_power` decimal(10,2) DEFAULT '0.00' COMMENT '总功率(W)',
|
||
`soc` decimal(5,2) DEFAULT '0.00' COMMENT '电量百分比(%)',
|
||
`soh` decimal(5,2) DEFAULT '100.00' COMMENT '健康状态(%)',
|
||
`max_temperature` decimal(5,2) DEFAULT '0.00' COMMENT '最高温度(°C)',
|
||
`min_temperature` decimal(5,2) DEFAULT '0.00' COMMENT '最低温度(°C)',
|
||
`avg_temperature` decimal(5,2) DEFAULT '0.00' COMMENT '平均温度(°C)',
|
||
`charge_cycles` int(11) DEFAULT '0' COMMENT '充电循环次数',
|
||
`last_charge_time` datetime DEFAULT NULL COMMENT '最后充电时间',
|
||
`last_discharge_time` datetime DEFAULT NULL COMMENT '最后放电时间',
|
||
`last_comm_time` datetime DEFAULT NULL COMMENT '最后通信时间',
|
||
`alarm_status` tinyint(1) DEFAULT '0' COMMENT '告警状态（0正常 1告警）',
|
||
`alarm_info` text COMMENT '告警信息',
|
||
`create_by` varchar(64) DEFAULT '' COMMENT '创建者',
|
||
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
|
||
`update_by` varchar(64) DEFAULT '' COMMENT '更新者',
|
||
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
|
||
`del_flag` char(1) DEFAULT '0' COMMENT '删除标志（0代表存在 2代表删除）',
|
||
`version` int(11) DEFAULT '0' COMMENT '乐观锁版本号',
|
||
`remark` varchar(500) DEFAULT NULL COMMENT '备注',
|
||
PRIMARY KEY (`box_id`),
|
||
UNIQUE KEY `uk_box_code` (`box_code`),
|
||
KEY `idx_box_type` (`box_type`),
|
||
KEY `idx_status` (`status`),
|
||
KEY `idx_last_comm_time` (`last_comm_time`),
|
||
KEY `idx_install_location` (`install_location`)
|
||
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='BOX设备表';
|
||
|
||
-- 插入示例数据
|
||
INSERT INTO `bms_box_device` VALUES
|
||
(1, 'BOX001', 'BOX设备001', 'Industrial', 'EvoBMS', 'EVO-BOX-100', 'SN20250001', 'V1.2.0', 'V1.0', '车间A-01', '2025-01-01 10:00:00', 1, 'MQTT', '192.168.1.100', 1883, 8, 800.00, 51.2, 10.5, 537.6, 85.5, 98.2, 25.6, 23.1, 24.2, 156, '2025-01-22 08:30:00', '2025-01-21 18:45:00', '2025-01-22 10:30:00', 0, NULL, 'admin', '2025-01-01 10:00:00', 'admin', '2025-01-22 10:30:00', '0', 0, 'BOX设备001'),
|
||
(2, 'BOX002', 'BOX设备002', 'Commercial', 'EvoBMS', 'EVO-BOX-200', 'SN20250002', 'V1.2.0', 'V1.0', '车间B-02', '2025-01-02 14:00:00', 1, 'MQTT', '192.168.1.101', 1883, 12, 1200.00, 76.8, 15.2, 1167.36, 92.3, 96.8, 26.8, 24.5, 25.4, 89, '2025-01-22 09:15:00', '2025-01-21 20:30:00', '2025-01-22 10:25:00', 0, NULL, 'admin', '2025-01-02 14:00:00', 'admin', '2025-01-22 10:25:00', '0', 0, 'BOX设备002'),
|
||
(3, 'BOX003', 'BOX设备003', 'Industrial', 'EvoBMS', 'EVO-BOX-100', 'SN20250003', 'V1.1.5', 'V1.0', '车间C-03', '2025-01-03 09:30:00', 2, 'MQTT', '192.168.1.102', 1883, 6, 600.00, 38.4, 0.0, 0.0, 0.0, 95.5, 28.9, 27.2, 28.1, 234, '2025-01-21 16:20:00', '2025-01-21 22:10:00', '2025-01-22 09:45:00', 1, '电池模块3温度过高', 'admin', '2025-01-03 09:30:00', 'admin', '2025-01-22 09:45:00', '0', 0, 'BOX设备003-故障中'); |