ALTER TABLE `4dkankan_v4`.`t_rtk_device` ADD COLUMN `account_type` int NULL DEFAULT 0 COMMENT '0账号池,1专用账号' AFTER `failure_time`; ALTER TABLE `4dkankan_v4`.`t_rtk_device` ADD COLUMN `camera_type` int NULL COMMENT '0看看,1看见,2深时,3深光' AFTER `account_type`; ALTER TABLE `4dkankan_v4`.`t_rtk_device` ADD COLUMN `user_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL AFTER `camera_type`, ADD COLUMN `password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL AFTER `user_name`, ADD COLUMN `ip_addr` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL AFTER `password`, ADD COLUMN `mount_point` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL AFTER `ip_addr`, ADD COLUMN `port` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL AFTER `mount_point`, ADD COLUMN `operator` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '运营商' AFTER `port`; ALTER TABLE `4dkankan_v4`.`t_rtk_account` DROP COLUMN `type`; CREATE TABLE `t_manage_agent_log` ( `id` int NOT NULL AUTO_INCREMENT, `agent_id` int DEFAULT NULL, `down_add_num` int DEFAULT NULL, `major_add_num` int DEFAULT NULL, `high_add_num` int DEFAULT NULL, `sys_user_id` int DEFAULT NULL, `rec_status` varchar(255) DEFAULT 'A', `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP, `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; ALTER TABLE `4dkankan_v4`.`t_manage_agent_log` ADD COLUMN `down_add_num_total` varchar(255) NULL AFTER `high_add_num`, ADD COLUMN `major_add_num_total` varchar(255) NULL AFTER `down_add_num_total`, ADD COLUMN `high_add_num_total` varchar(255) NULL AFTER `major_add_num_total`; CREATE TEMPORARY TABLE temp_table AS SELECT rd.id,camera_sn ,camera_type,d.type from t_rtk_device rd LEFT JOIN t_camera a on rd.camera_sn = a.sn_code LEFT JOIN t_camera_detail d on a.id = d.camera_id WHERE rd.rec_status ='A' and camera_sn is not null and camera_type is null; UPDATE t_rtk_device set camera_type = 0 WHERE id in (SELECT id from temp_table WHERE type = 1); UPDATE t_rtk_device set camera_type = 1 WHERE id in (SELECT id from temp_table WHERE type = 9); UPDATE t_rtk_device set camera_type = 2 WHERE id in (SELECT id from temp_table WHERE type = 10); UPDATE t_rtk_device set camera_type = 3 WHERE id in (SELECT id from temp_table WHERE type = 11); DROP TABLE IF EXISTS temp_table;