需求情景,一个表里的字典字段,另外一个表是记录,需要拿字典里的记录更新记录里的某些字段项,本来想写存储过程的,但是看到可以直接update语句就试了一下,效率挺高,直接上sql语句;200万条记录1分钟搞定;
UPDATE
voter_bak a,
precinct b
SET a.countyName=b.countyName,
a.registrationCenterAddress=b.address,
a.registrationCenterName=b.precinctName,
a.electoralDistrict=b.electoralDistrict
WHERE
a.precinctCode=b.precinctCode
AND
a.pollingPlace=b.pollingPlace;
Query OK, 2183119 rows affected (1 min 6.20 sec)
Rows matched: 2183119 Changed: 2183119 Warnings: 0
另外附上备份mysql出现提示表lock的问题;
解决办法:
mysqldump -u dbuser -ppass db --skip-lock-tables > db.sql
从select count中的记录更新另外一个表;
update agent a,(select count(posId) as count,agentId from pos group by agentId ) b set a.posCount=b.count where a.agentId=b.agentId;