运维脚本

作者: 注册号码 | 来源:发表于2024-09-03 08:39 被阅读0次

linux: 常用shell脚本珍藏
从DevOps到AIOps，阿里如何实现智能化运维？ | 技术贴
Golang 运维脚本
运维脚本常用
运维常用脚本
crontab定时清理文件,java日志,es记录等
Shell入门
shell Scripts
SpringBoot项目运维脚本
Openshift 常见运维脚本

一、云食堂订单表加索引

DROP PROCEDURE IF EXISTS ADD_INDEX;
DELIMITER $$
-- 1表示新增索引,3表示删除索引,4新增唯一索引,另修改索引先删后新增
CREATE PROCEDURE ADD_INDEX(TableName VARCHAR(50),IndexName VARCHAR(50),SqlStr VARCHAR(4000),CType INT)
BEGIN
DECLARE Rows1 INT;
SET Rows1=0;
SELECT COUNT(*) INTO Rows1  FROM INFORMATION_SCHEMA.statistics
WHERE table_schema= DATABASE() AND table_name=TableName AND index_name=IndexName;
-- 新增索引
IF (CType=1 AND Rows1<=0) THEN
SET SqlStr := CONCAT( 'ALTER TABLE ',TableName,' ADD INDEX ',IndexName,' ',SqlStr);
-- 删除索引
ELSEIF (CType=3 AND Rows1>0) THEN
SET SqlStr := CONCAT('ALTER TABLE  ',TableName,' DROP INDEX ',IndexName);
-- 新增唯一索引
ELSEIF (CType=4 AND Rows1<=0) THEN
SET SqlStr := CONCAT( 'ALTER TABLE ',TableName,' ADD UNIQUE INDEX ',IndexName,' ',SqlStr);
ELSE  SET SqlStr :='';
END IF;
-- 执行命令
IF (SqlStr<>'') THEN
SET @SQL1 = SqlStr;
PREPARE stmt1 FROM @SQL1;
EXECUTE stmt1;
END IF;
END $$
DELIMITER ;
-- 新增索引
CALL ADD_INDEX ('ord_app_info', 'index_mac_ord_id', '(mac_ord_id)',1);
CALL ADD_INDEX ('ord_app_info', 'index_ord_time', '(ord_time)',1);
CALL ADD_INDEX ('ord_app_info', 'index_ord_goods_date', '(ord_goods_date)',1);
CALL ADD_INDEX ('ord_app_info', 'index_pay_time', '(pay_time)',1);
CALL ADD_INDEX ('ord_app_info', 'index_relate_ord_id', '(relate_ord_id)',1);
CALL ADD_INDEX ('ord_app_info', 'index_interval_id', '(interval_id)',1);
CALL ADD_INDEX ('ord_app_info', 'index_machine_sn', '(machine_sn)',1);
-- 最后再执行一遍删除存储过程
DROP PROCEDURE IF EXISTS ADD_INDEX;

二、算法不通导致脏数据删除后加索引确保唯一

ALTER TABLE `cust_photo` ADD UNIQUE INDEX `index_uniqe_custid_facever`(`cust_id`, `face_ver`) USING BTREE;

三、老云食堂重复订单

SELECT mac_ord_id from ord_app_info WHERE if_reverse=2 GROUP BY mac_ord_id HAVING COUNT(mac_ord_id)>1

四、右边匹配,不包含本身

select org_full_id from cust_org where org_full_id LIKE CONCAT('1','_%')

五、历史数据备份迁移走

--  1备份订单标本表
create table ord_app_info_history select * from ord_app_info;
--  2清除原表
TRUNCATE  ord_app_info;
--  3恢复近一年数据

insert into  ord_app_info select * from ord_app_info_history WHERE crtime > '2022-01-01 00:00:00';
--  1备份订单明细表
create table ord_detail_history select * from ord_detail;
--  2清除原表
TRUNCATE  ord_detail;
--  3恢复近一年数据
insert into  ord_detail select * from ord_detail_history WHERE crtime > '2022-01-01 00:00:00';

六、注销人员恢复脚本

UPDATE cust_info ci
JOIN cust_acc_temp cat ON cat.cust_id = ci.cust_id
JOIN acc_info ai ON cat.cust_id = ai.cust_id 
SET
cat.acc_status = 1,
cat.cust_state = 1,
ci.cust_state = 1,
ai.acc_status = 1 
WHERE
ci.cust_id IN (246524926633205760);

七、cust_acc_temp表和custInfo表数据一致

DELETE FROM  cust_acc_temp a WHERE  NOT EXISTS ( SELECT 1 FROM cust_info b WHERE a.cust_id = b.cust_id );

八、更换组织名称

SET @oldString = '旧公司名称';
SET @newString = '新公司名称';
-- 食堂区域
UPDATE merc_merchant
SET merchant_name = REPLACE(merchant_name, @oldString, @newString);
-- 组织表  
UPDATE cust_org
SET org_name = REPLACE(org_name, @oldString, @newString),
    org_full_name = REPLACE(org_full_name, @oldString, @newString);
-- 人员表
UPDATE cust_info
SET org_full_name = REPLACE(org_full_name, @oldString, @newString);
-- 位置表
UPDATE cust_place
SET  place_name = REPLACE(place_name, @oldString, @newString),
    place_full_name = REPLACE(place_full_name, @oldString, @newString);


不支持环境变量场景
-- 食堂区域
UPDATE merc_merchant
SET merchant_name = REPLACE(merchant_name,'海港***(默认)', '海港**(默认) 新公司');
    
-- 组织表  
UPDATE cust_org
SET org_name = REPLACE(org_name, '海港区**(默认)', '海港**(默认) 新公司'),
    org_full_name = REPLACE(org_full_name,'海港区**(默认)', '海港**(默认) 新公司');
-- 人员表
UPDATE cust_info
SET org_full_name = REPLACE(org_full_name, '海港区**(默认)', '海港**(默认) 新公司');
-- 位置表
UPDATE cust_place
SET  place_name = REPLACE(place_name, '海港区**(默认)', '海港**(默认) 新公司'),
    place_full_name = REPLACE(place_full_name, '海港区**(默认)', '海港**(默认) 新公司');

九、人员三方id更新

-- 先查询确认下
SELECT   cust_num ,cust_third_id  from cust_info  WHERE cust_third_id is null;

-- 再更新三方id和人员编号一样
UPDATE  cust_info set cust_third_id=cust_num WHERE cust_third_id is null;
UPDATE  cust_acc_temp set cust_third_id=cust_num WHERE cust_third_id is null;

十、人员编号重复

SELECT super_id, org_name , COUNT(*) as occurrence
FROM cust_org
GROUP BY super_id, org_name
HAVING occurrence > 1;

-- 人员编号重复
SELECT * from cust_info WHERE cust_num in (
SELECT cust_num from cust_info WHERE cust_state=1 GROUP BY cust_num HAVING COUNT(cust_num)>1);

十一、人员表中类别名称同步

UPDATE cust_info ci JOIN cust_psn_type cat ON cat.psn_type = ci.psn_type
SET  ci.psn_type_name = cat.psn_type_name;

十二、人员表中组织名称和组织表一致

UPDATE cust_info ci
JOIN cust_org cat ON cat.org_id = ci.org_id
SET ci.org_full_name = cat.org_full_name WHERE ci.cust_state=1;


SELECT a.org_full_name,b.org_full_name  from cust_info a JOIN cust_org b on a.org_id=b.org_id and a.cust_state=1  
and  a.org_full_name != b.org_full_name;

十三、主键自增调整中间id值

-- 新建字段
ALTER TABLE`ord_app_info` 
ADD COLUMN `id_a` bigint NULL;
-- 赋值
UPDATE  `ord_app_info` set id_a= id;
-- 调整顺序
UPDATE ord_app_info
SET id_a = id_a+1
WHERE id_a > 281296;

-- 验证查询
SELECT id, id_a from   ord_app_info WHERE id>=281296  ORDER BY id;

-- 修改主键
ALTER TABLE `ord_app_info` 
MODIFY COLUMN `id` bigint NOT NULL COMMENT '主键id' FIRST,
MODIFY COLUMN `id_a` bigint NOT NULL AUTO_INCREMENT ,
DROP PRIMARY KEY,
ADD PRIMARY KEY (`id_a`) USING BTREE;
-- 赋值
UPDATE  `ord_app_info` set id=id_a;

-- 验证查询
SELECT id, id_a from   ord_app_info WHERE id>=281296  ORDER BY id;

-- 还原
ALTER TABLE `ord_app_info` 
DROP COLUMN `id_a`,
MODIFY COLUMN `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键id' FIRST,
DROP PRIMARY KEY,
ADD PRIMARY KEY (`id`) USING BTREE;