on duplicate key update 自增id 不连续的问题sql_query =''' SELECT card.imsi, DATE_FORMAT(flow.consume_date,\

 

由于项目中需要统计每个月卡的在线数（也就是每天只要卡有流量消耗，就算上线），所以需要先统计每张卡每个月的在线数。如果卡的记录已经存在，那么就把在线数 加 1， 否则就插入一条新的记录。

sql_query ='''

SELECT card.imsi, DATE_FORMAT(flow.consume_date,\'%%Y-%%m\') FROM %s flow JOIN flowmgn_flowcard card

ON (flow.card_id = card.card_id)

WHERE flow.used_totalflow > 0 and DATE_FORMAT(flow.consume_date,\'%%Y-%%m-%%d\') >=\'%s\'and DATE_FORMAT(flow.consume_date,\'%%Y-%%m-%%d\') <\'%s\'

GROUP BY flow.card_id, DATE_FORMAT(flow.consume_date,\'%%Y-%%m\')

limit 5

'''% (table_name, start, calcEndDay)

 

为了效率用到了 insert into tbl() values() on duplicate key update 进行自动判断是更新还是新增，一段时间后发现该表的主键id (已经设置为连续自增)，不是连续的自增，总是跳跃的增加。这样就造成 id 自增过快。

 

on duplicate key update有一个特性就是,每次是更新的情况下id也是会自增加1的,比如说现在id最大值的5,然后进行了一次更新操作,再进行一次插入操作时,id的值就变成了7而不是6.

为了解决这个问题,有两种方式,第一种是修改innodb_autoinc_lock_mode中的模式,第二种是将语句修拆分为更新和操作2个动作

第一种方式:innodb_autoinc_lock_mode中有3中模式,0,1和2,mysql5的默认配置是1,

0 是每次分配自增id的时候都会锁表.

1 只有在bulk insert的时候才会锁表,简单insert的时候只会使用一个light-weight mutex,比0的并发性能高

2 没有仔细看,好像是很多的不保证...不太安全.

数据库默认是1的情况下,就会发生上面的那种现象,每次使用insert into .. on duplicate key update 的时候都会把简单自增id增加,不管是发生了insert还是update

由于该代码数据量大,同时需要更新和添加的数据量多,不能使用将0模式,只能将数据库代码拆分成为更新和插入2个步骤,第一步先根据用户名和软件名更新使用时长,