在MySQL中实现upsert功能

1 语法示例

INSERT INTO table_name (column1, column2, ...)
SELECT column1, column2, ...
FROM temp_table
ON DUPLICATE KEY UPDATE
    column1 = VALUES(column1),
    column2 = VALUES(column2),
    ...;

　　

2 自己项目中的SQL代码示例

INSERT INTO hs_stock_market_data_daily_basic_lastday (rowkey,ts_code,close,trade_date,turnover_rate,turnover_rate_f,volume_ratio,pe,pe_ttm,pb,ps,ps_ttm,dv_ratio,dv_ttm,total_share,float_share,free_share,total_mv,circ_mv,stock_level,stock_flow_level)

   select ts_code,ts_code,close,trade_date,turnover_rate,turnover_rate_f,volume_ratio,pe,pe_ttm,pb,ps,ps_ttm,dv_ratio,dv_ttm,total_share,float_share,free_share,total_mv,circ_mv,
CASE
    WHEN total_mv <= 300000 THEN 1
    WHEN total_mv > 300000 and total_mv <=500000 THEN 2
    WHEN total_mv > 500000 and total_mv <=1000000 THEN 3
    WHEN total_mv > 1000000 and total_mv <=3000000 THEN 4
    WHEN total_mv > 3000000 and total_mv <=5000000 THEN 5
    ELSE 6
  END AS stock_level,
    CASE
    WHEN circ_mv <= 300000 THEN 1
    WHEN circ_mv > 300000 and circ_mv <=500000 THEN 2
    WHEN circ_mv > 500000 and circ_mv <=1000000 THEN 3
    WHEN circ_mv > 1000000 and circ_mv <=3000000 THEN 4
    WHEN circ_mv > 3000000 and circ_mv <=5000000 THEN 5
    ELSE 6
  END AS stock_flow_level
from  hs_stock_market_data_daily_basic where trade_date =  ( select MAX(trade_date) from hs_stock_market_data_daily_basic limit 1 )

ON DUPLICATE KEY UPDATE
rowkey = VALUES(ts_code),
ts_code = VALUES(ts_code),
close = VALUES(close),
trade_date = VALUES(trade_date),
turnover_rate = VALUES(turnover_rate),
turnover_rate_f = VALUES(turnover_rate_f),
volume_ratio = VALUES(volume_ratio),
pe = VALUES(pe),
pe_ttm = VALUES(pe_ttm),
pb = VALUES(pb),
ps = VALUES(ps),
ps_ttm = VALUES(ps_ttm),
dv_ratio = VALUES(dv_ratio),
dv_ttm = VALUES(dv_ttm),
total_share = VALUES(total_share),
float_share = VALUES(float_share),
free_share = VALUES(free_share),
total_mv = VALUES(total_mv),
circ_mv = VALUES(circ_mv),
stock_level = VALUES(stock_level),
stock_flow_level = VALUES(stock_flow_level);