sql 触发器使用

    日常开发过程,由于单表查询速度变慢,原才有直接调用数据库项目表,发现影响速度:

                      原sql:如图

才有union 连接获取统计表数据,(注意:union连接获取数据能够去重,而union Alll不行

              union连接字句进行排序  order by 的时候发现不好使,需加上 limit 才行

           

          本sql  太长速度太慢,后对sql 进行优化,采用临时表type虚拟字段排序 发现速度还是太慢:

          如图:

           后才有mysql 触发器,新建表,把需要数据处理好后,放入新表:

ON DUPLICATE key UPDATE   :该函数(先判断插入是否有数据,插入时存在则更新,否则插入)

begin
      if (NEW.compare_target=0 AND left(NEW.project_status,1) in ('2','3')) then
      
       INSERT INTO `stat_proj_approve`
(
`project_id`,
`projectName`,
`update_time`,
`confirm_time`,
`approval_time`,
`approval_time_start`,
`address`,
`cityName`,
`city_code`,
`auction_status`,
`project_status`,
`projectApprovalTime`,
`InvestmentCommitteeTime`,
`type`,
`typedesc`)
VALUES
(
NEW.id,
NEW.name,
NEW.update_time,
NEW.confirm_time,
NEW.approval_time,
NEW.approval_time_start,
NEW.location_description,
(select name from t_city where code=NEW.city_code),
NEW.city_code,
NEW.auction_status,
NEW.project_status,
DATEDIFF(DATE(NOW()), NEW.confirm_time),
DATEDIFF(DATE(NOW()),NEW.approval_time),
2,
'其他'
        ) ON DUPLICATE key UPDATE   
               auction_status=NEW.auction_status,
   project_status=NEW.project_status,
   update_time=NEW.update_time,
   confirm_time=NEW.confirm_time,
   approval_time=NEW.approval_time,
   approval_time_start=NEW.approval_time_start,
   projectName=NEW.name,
   address=NEW.location_description,
   city_code=NEW.city_code,
   projectApprovalTime=DATEDIFF(DATE(NOW()), NEW.confirm_time),
   InvestmentCommitteeTime=DATEDIFF(DATE(NOW()),NEW.approval_time),
   cityName=(select name from t_city where code=NEW.city_code),
   type=(case when type=1 and NEW.auction_status in(22,13) then 2 
  when  NEW.auction_status=20 and DATEDIFF(DATE(NOW()),NEW.confirm_time)>60 then 1 
  when  NEW.auction_status=30 and DATEDIFF(DATE(NOW()),NEW.approval_time)>30 then 1
  else 2 end ) ;    
       end if;
  end

阅读更多
想对作者说点什么?

博主推荐

换一批

没有更多推荐了,返回首页