MySql定义变量,应用范例

1.定义用户变量(或者赋值)

set @变量名=值

-- 创建自定义用户变量，当当前的用断开连接的时候就会消失
set @value=1;

 

set @pid=1162;
set @smart_id=1356;
select * from tb_iot where corp_id=115 and pid=@pid and properties like CONCAT('%',@smart_id,'%');

 

应用范例：复制一个大棚后，增加修改数据。

#复制大棚#2，改下面的参数。

#1更新网关ID 更新一个棚所有网关 
set @name='大棚#6';
set @pidPeng=1627;   #大棚id 下的所有  
set @pidAllSmart=1628;     #所有智能设备 下的所有 
set @NewgatewayId=20190027; 
#以上参数要改
set @OldgatewayId=20190020; #原    复制大棚#2的数据    
update tb_iot set properties=REPLACE(properties,CONCAT('"gatewayId":"',@OldgatewayId,'"'),CONCAT('"gatewayId":"',@NewgatewayId,'"')) where corp_id=115 and name=@name;
update tb_iot set properties=REPLACE(properties,CONCAT('"gatewayId":"',@OldgatewayId,'"'),CONCAT('"gatewayId":"',@NewgatewayId,'"')) where corp_id=115 and pid in (@pidPeng,@pidAllSmart); #大棚id 和 #所有智能设备





#2更新传感器的smartid
#set @pidPeng=1539;  #大棚id 下的所有  同上
set @Newsmart_id=1629; #新电磁阀
#以上参数要改

set @Oldsmart_id=1442; #原电磁阀 复制大棚#2的数据
update tb_iot set smart_id=@Newsmart_id ,properties=REPLACE(properties,CONCAT('"smartId":"',@Oldsmart_id,'"'),CONCAT('"smartId":"',@Newsmart_id,'"')) where corp_id=115 and pid=@pidPeng  and properties like CONCAT('%"smartId":"',@Oldsmart_id,'"%');  

set @Newsmart_id=@Newsmart_id+1; #新 卷被
set @Oldsmart_id=@Oldsmart_id+1; #原
update tb_iot set smart_id=@Newsmart_id ,properties=REPLACE(properties,CONCAT('"smartId":"',@Oldsmart_id,'"'),CONCAT('"smartId":"',@Newsmart_id,'"')) where corp_id=115 and pid=@pidPeng  and properties like CONCAT('%"smartId":"',@Oldsmart_id,'"%');  

set @Newsmart_id=@Newsmart_id+1; #新 土壤温湿度#3
set @Oldsmart_id=@Oldsmart_id+1; #原
update tb_iot set smart_id=@Newsmart_id ,properties=REPLACE(properties,CONCAT('"smartId":"',@Oldsmart_id,'"'),CONCAT('"smartId":"',@Newsmart_id,'"')) where corp_id=115 and pid=@pidPeng  and properties like CONCAT('%"smartId":"',@Oldsmart_id,'"%');  

set @Newsmart_id=@Newsmart_id+1; #新 
set @Oldsmart_id=@Oldsmart_id+1; #原
update tb_iot set smart_id=@Newsmart_id ,properties=REPLACE(properties,CONCAT('"smartId":"',@Oldsmart_id,'"'),CONCAT('"smartId":"',@Newsmart_id,'"')) where corp_id=115 and pid=@pidPeng  and properties like CONCAT('%"smartId":"',@Oldsmart_id,'"%');  

set @Newsmart_id=@Newsmart_id+1; #新 
set @Oldsmart_id=@Oldsmart_id+1; #原
update tb_iot set smart_id=@Newsmart_id ,properties=REPLACE(properties,CONCAT('"smartId":"',@Oldsmart_id,'"'),CONCAT('"smartId":"',@Newsmart_id,'"')) where corp_id=115 and pid=@pidPeng  and properties like CONCAT('%"smartId":"',@Oldsmart_id,'"%');  

set @Newsmart_id=@Newsmart_id+1; #新 
set @Oldsmart_id=@Oldsmart_id+1; #原
update tb_iot set smart_id=@Newsmart_id ,properties=REPLACE(properties,CONCAT('"smartId":"',@Oldsmart_id,'"'),CONCAT('"smartId":"',@Newsmart_id,'"')) where corp_id=115 and pid=@pidPeng  and properties like CONCAT('%"smartId":"',@Oldsmart_id,'"%');  

set @Newsmart_id=@Newsmart_id+1; #新 
set @Oldsmart_id=@Oldsmart_id+1; #原
update tb_iot set smart_id=@Newsmart_id ,properties=REPLACE(properties,CONCAT('"smartId":"',@Oldsmart_id,'"'),CONCAT('"smartId":"',@Newsmart_id,'"')) where corp_id=115 and pid=@pidPeng  and properties like CONCAT('%"smartId":"',@Oldsmart_id,'"%');  

set @Newsmart_id=@Newsmart_id+1; #新 
set @Oldsmart_id=@Oldsmart_id+1; #原
update tb_iot set smart_id=@Newsmart_id ,properties=REPLACE(properties,CONCAT('"smartId":"',@Oldsmart_id,'"'),CONCAT('"smartId":"',@Newsmart_id,'"')) where corp_id=115 and pid=@pidPeng  and properties like CONCAT('%"smartId":"',@Oldsmart_id,'"%');  

set @Newsmart_id=@Newsmart_id+1; #新 
set @Oldsmart_id=@Oldsmart_id+1; #原
update tb_iot set smart_id=@Newsmart_id ,properties=REPLACE(properties,CONCAT('"smartId":"',@Oldsmart_id,'"'),CONCAT('"smartId":"',@Newsmart_id,'"')) where corp_id=115 and pid=@pidPeng  and properties like CONCAT('%"smartId":"',@Oldsmart_id,'"%');  

set @Newsmart_id=@Newsmart_id+1; #新 
set @Oldsmart_id=@Oldsmart_id+1; #原
update tb_iot set smart_id=@Newsmart_id ,properties=REPLACE(properties,CONCAT('"smartId":"',@Oldsmart_id,'"'),CONCAT('"smartId":"',@Newsmart_id,'"')) where corp_id=115 and pid=@pidPeng  and properties like CONCAT('%"smartId":"',@Oldsmart_id,'"%');  

set @Newsmart_id=@Newsmart_id+1; #新 
set @Oldsmart_id=@Oldsmart_id+1; #原
update tb_iot set smart_id=@Newsmart_id ,properties=REPLACE(properties,CONCAT('"smartId":"',@Oldsmart_id,'"'),CONCAT('"smartId":"',@Newsmart_id,'"')) where corp_id=115 and pid=@pidPeng  and properties like CONCAT('%"smartId":"',@Oldsmart_id,'"%');  

set @Newsmart_id=@Newsmart_id+1; #新 
set @Oldsmart_id=@Oldsmart_id+1; #原
update tb_iot set smart_id=@Newsmart_id ,properties=REPLACE(properties,CONCAT('"smartId":"',@Oldsmart_id,'"'),CONCAT('"smartId":"',@Newsmart_id,'"')) where corp_id=115 and pid=@pidPeng  and properties like CONCAT('%"smartId":"',@Oldsmart_id,'"%');  


#1442	电磁阀
#1443	卷被
#1444	土壤温湿度#3
#1445	放风机
#1446	百叶窗#1
#1447	土壤PH值#1
#1448	土壤温湿度#1
#1449	百叶窗#2
#1450	土壤温湿度#2
#1451	百叶窗#3
#1452	土壤PH值#2
#1453	土壤PH值#3

#select * from tb_iot where corp_id=115 and pid=@pidPeng;