mysql - 将 AUTO_INCREMENT id 列添加到大型 MySQL 表时出现问题

Question

失败的尝试 1

我试图将一个巨大的 SQL 文件(包含 6 亿条记录和 239 GB)导入到具有不同架构的 MySQL 数据库中，以便获得具有“自动生成的 ID”的新列。

我在 Windows 上使用 MySQL Workbench 6.2，但在导入数千万条记录后总是失败并显示错误消息“MySQL 服务器已消失”。

我正在使用“服务器 -> 数据导入”功能进行导入。

我尝试添加

wait_timeout = 288000
max_allowed_packet = 300G

进入MySQL Server文件夹下的my.ini，还是出现同样的错误。

错误日志如下:

14:16:37 Restoring C:\data.sql
Running: mysql.exe --defaults-file="c:\users\***\appdata\local\temp\5\tmpcfyyxk.cnf"  --protocol=tcp --host=127.0.0.1 --user=shodan --port=8080 --default-character-set=utf8 --comments --database=test2 < "C:\\data.sql"
ERROR 2006 (HY000) at line 13776162: MySQL server has gone away

Operation failed with exitcode 1
16:25:51 Import of C:\data.sql has finished with 1 errors

失败的尝试 2

如果我只是在原始表中添加一个新列，则执行 SQL 查询的时间太长而且永远不会成功。

如果无法解决导入问题，有没有办法直接在庞大的表中添加一个自动生成ID的新列？

Answer 1

与您一直在尝试的解决方案相比，我建议使用以下替代方案:

1. 创建一个复制旧表的新表 + 添加一个 auto_increment id，
2. 将旧表中的数据复制到新表中，并
3. 重命名表格，使新表格取代旧表格。

假设您的 current_table 具有列 col1 VARCHAR(255) NOT NULL，它当前充当您的主键，而 col2 VARCHAR(255) NULL，这只是一些其他数据。

CREATE TABLE new_table (
    id INT UNSIGNED AUTO_INCREMENT,
    col1 VARCHAR(255) NOT NULL, -- Make your old PK a simple not null column
    col2 VARCHAR(255) NULL,
    PRIMARY KEY (id), -- PK moves to the new id column
    INDEX (col1) -- Replace your old PK with an index
) ENGINE=InnoDB;

INSERT INTO new_table (col1, col2) 
SELECT col1, col2 
FROM current_table;

RENAME TABLE current_table TO old_table,
             new_table TO current_table;

这应该比简单地添加列或导入新数据库更有效。它还将维护当前的表结构并创建一个替换表，允许您在出现问题时进行回退。当您验证新表结构按预期工作时，您可以删除旧表。如果失败，您可以删除新表并回滚到旧表。