需要更新包含数百万行的MySQL表

Question

我有两张桌子：

Cities
Region| City Name 

States
ID| State | Region_Key

我需要在cities表上执行一个更新查询，比如设置cities.region = statres.id where states.region_key = cities.region

问题是，cities数据库有超过270万条记录，我尝试执行这样的查询，结果mysql挂起并消失。

update cities c, states c set c.region = s.id where c.region = s.region_key

编辑===================

这是我正在使用的sql，但它不起作用，我得到一个错误，指出UPDATE和LIMIT的用法不正确

update cities w, states s 
set w.region_id = s.id, 
w.updated = 1 
where w.region = s.w_code and w.updated = 0
LIMIT 10000

Answer 1

1. 将可空的位列HasBeenUpdated添加到cities表
2. 将Set c.HasBeenUpdated = 1添加到update子句
3. 添加以下where条件AND c.HasBeenUpdated IS NULL
4. Add a new代码条件<cities>d12WHERE >。这是必需的，因为您不能在多表Update (source)上使用Limit语句。这还假设您有一个ID列作为城市的PK (如果没有，请考虑添加一个)。现在，update语句一次只处理10,000行(并且只处理未处理的行)。

如果您可以使用您的应用程序逻辑将其放入循环中，则可以将其用于自动化。根据需要更改限制数量，完成后，删除HasBeenUpdated列。

这应该允许您最小化更新对表和数据库的影响，并允许您以可管理的批处理方式在整个表中执行更新。

编辑：Update步骤4通过子查询筛选要更新的行，因为Limit语句不能用于多表更新。

Answer 2

使用SELECT INTO NEW_TABLE创建一个包含所需内容的新表，然后删除/重命名旧表，并使用RENAME TABLE将新创建的表重命名为正确的名称：

CREATE TABLE new_cities SELECT 
   states.id AS region_id, cities.name 
FROM cities JOIN states ON cities.region = states.w_code;

RENAME TABLE cities TO old_cities, new_cities TO cities;