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

Question

我这里有两个表：

Cities
Region| City Name 

States
ID| State | Region_Key

我需要对城市表进行更新查询，如下所示，即设置 cars.region = statres.id 其中 states.region_key = cars.region

问题是城市数据库有超过 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 添加到更新子句
3. 添加以下 where 条件 AND c.HasBeenUpdated IS NULL
4. 添加新的 WHERE 条件 AND c.ID in (Select ID from Cities where HasBeenUpdated Is Null Limit 10000）。这是必需的，因为您不能在多表 Update 上使用 Limit 语句（来源）。这还假设您有一个 ID 列作为城市的 PK（如果没有，则考虑添加一列）。现在，更新语句一次只会处理 10,000 行（并且只会处理未处理的行）。

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

这应该允许您最大限度地减少更新对表和数据库的影响，并允许您以可管理的批次在整个表中执行更新。

编辑：更新步骤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;