添加insert触发器，在insert一条新纪录时，当主单号不为空，并且新增价格和最近一次价格对比不相等时，说明价格有变化。这时触发器会自动将上一次老价格添加到当前新增行的unit_price_old老价格列。

这个需求是在一个表上，更新自己身上的其他列，这时需要用before，并且set new.列名，并且new必须在等号左边。

delimiter//createtriggerinsert_flight_cabin_unit_pricebeforeinsertonflight_cabin_book_o_updateforeachrowbegin/*注意給变量赋值，等号右边的select必须加括号*//*获取每条主单号的最近一次更新时间*/set@last_creat_time=(selectcreatfromflight_cabin_book_o_updatewherewaybill=new.waybillandflight_no=new.flight_noandflight_time=new.flight_timeorderbycreatdesclimit1);/*获取每条主单号的最近一次更新价格*/set@last_unit_price=(selectunit_pricefromflight_cabin_book_o_updatewherewaybill=new.waybillandflight_no=new.flight_noandflight_time=new.flight_timeandcreat=@last_creat_timelimit1);/*如果一个主单号不为空，并且最近一次价格和现在insert的价格不相同，就说明价格更新了*/ifnew.waybillisnotnullandnew.waybill!=''andnew.unit_price!=@last_unit_pricethensetnew.unit_price_old=@last_unit_price;setnew.unit_price_old_time=@last_creat_time;endif;end//delimiter;

这个是在一个表上update自己身上的其他列，也需要用before，并且set new.列名，并且new必须在等号左边

delimiter//createtriggerupdate_flight_cabin_unit_pricebeforeupdateonczxforeachrowbeginifnew.unit_price!=old.unit_pricethensetnew.unit_price_old=old.unit_price;setnew.is_update_price='Y';setnew.update_price_time=now();endif;end//delimiter;

#!/usr/bin/python#-*-coding:utf8-*-#author:chenzhixinfromcontextlibimportcontextmanagerimportpymysqlasmysqldbimportrequestsimporttime@contextmanagerdefget_mysql_conn(**kwargs):"""建立MySQL数据库连接:paramkwargs::return:"""conn=mysqldb.connect(host=kwargs.get('host','localhost'),user=kwargs.get('user'),password=kwargs.get('password'),port=kwargs.get('port',3306),database=kwargs.get('database'))try:yieldconnfinally:ifconn:conn.close()defexecute_mysql_select_sql(conn,sql):"""执行mysql的select类型语句:paramconn::paramsql::return:"""withconnascur:cur.execute(sql)rows=cur.fetchall()returnrowsdefexecute_mysql_sql(conn,sql):"""执行mysql的dml和ddl语句，不包括select语句:paramconn::paramsql::return:"""withconnascur:cur.execute(sql)defget_mysql_flight_cabin_book_o_update_data(conn):"""获取kb_kettle_data.flight_cabin_book_o_update的数据:paramconn::return:"""sql="select"\"id,"\"waybill,"\"flight_no,"\"flight_time,"\"unit_price,"\"unit_price_old,"\"unit_price_old_time,"\"creat"\"fromflight_cabin_book_o_update"\"whereunit_price_oldisnotnull"mysql_table_rows=execute_mysql_select_sql(conn,sql)ifmysql_table_rows:print('检测到价格变化:\n',mysql_table_rows)forindex,rowinenumerate(mysql_table_rows,1):id=row[0]waybill=row[1]flight_no=row[2]flight_time=row[3]unit_price=row[4]unit_price_old=row[5]unit_price_old_time=row[6]creat=row[7]yield{'id':id,'waybill':waybill,'flight_no':flight_no,'flight_time':flight_time,'unit_price':unit_price,'unit_price_old':unit_price_old,'unit_price_old_time':unit_price_old_time,'creat':creat}defsend_to_qyweixin(dic):"""发送消息到企业微信:paramdic::return:"""headers={"Content-Type":"text/plain"}#s="--石墨价格变化通知--\n主单号：{waybill}\n航班号：{flight_no}\n航班日期：{flight_time}\n\n时间1：{unit_price_old_time}\n-----------------------\n价格1：{unit_price_old}\n-----------------------\n{creat}\n主单号：{waybill}\n价格变为:{unit_price}".format(#waybill=dic['waybill'],#unit_price_old=dic['unit_price_old'],#unit_price_old_time=dic['unit_price_old_time'],#creat=dic['creat'],#unit_price=dic['unit_price']#)s="""---石墨价格变化通知---主单号：{waybill}航班号：{flight_no}航班日期：{flight_time}时间1：{unit_price_old_time}价格1：{unit_price_old}时间2：{creat}价格2：{unit_price}""".format(waybill=dic['waybill'],flight_no=dic['flight_no'],flight_time=dic['flight_time'],unit_price_old=dic['unit_price_old'],unit_price_old_time=dic['unit_price_old_time'],creat=dic['creat'],unit_price=dic['unit_price'])data={"msgtype":"text","text":{"content":s,}}r=requests.post(url='https://qyapi.weixin.qq.com/cgi-bin/webhook/sexxxd5-eb13',headers=headers,json=data)print(r.text)defmain():mysql_conn_args=dict(user='user1',host='10.xx.xx.xx',password='123456',database='xxxxx')withget_mysql_conn(**mysql_conn_args)asmysql_conn:whileTrue:print('正在监听价格是否有变化.....')#1、首先获取mysql_flight_cabin_book_o_update表中有价格更新的所有行数据mysql_data_dic=get_mysql_flight_cabin_book_o_update_data(mysql_conn)#2、其次遍历有价格变化的行数据，发送给企业微信fordicinmysql_data_dic:#发送到企业微信send_to_qyweixin(dic)#3、最后把mysql_flight_cabin_book_o_update表中的标记位is_update_price置为空update_flag_sql="updateflight_cabin_book_o_updatesetunit_price_old=null,unit_price_old_time=nullwherewaybill='{}'".format(dic['waybill'])execute_mysql_sql(mysql_conn,update_flag_sql)time.sleep(60)if__name__=='__main__':main()

[root@gfs02 wangsn]# nohup python /usr/local/shell/monitor_price_qywx/monitor_price_to_qiyeweixin.py > /dev/null 2>&1 &