/*查询表中多余的指定列*/
Select DISTINCT RELATION_ID from rmcp_device_bind WHERE RELATION_ID not in (select RELATION_ID from camera_info_extend) GROUP BY RELATION_ID;
/*将查询好的数据插入另一个表中,去除指定重复列*/
insert into camera_info_extend_bak(RELATION_ID,DEV_CODE,ONLINE_TIME,status)
(Select RELATION_ID,DEV_CODE,UPDATE_TIME ONLINE_TIME,0 `STATUS` from rmcp_device_bind WHERE RELATION_ID not in (select RELATION_ID from camera_info_extend) GROUP BY RELATION_ID);
/*修改表名*/
ALTER TABLE camera_info_extend_back RENAME TO camera_info_extend;
/*已经创建的表添加主键*/
ALTER TABLE camera_info_extend ADD PRIMARY KEY (RELATION_ID);
备份表
1、SHOW CREATE TABLE table_name_new \G;
2、拷贝生成的sql语句,修改表名,重新执行。
3、insert into table_name_new () select * from table_name_old;
MySql 常用的命令
最新推荐文章于 2024-08-23 10:27:05 发布