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