mysqldump 表而不转储主键

Question

我有一张表分布在两台运行 MySql 4 的服务器上。我需要将它们合并到一台服务器中以用于我们的测试环境。

这些表实际上每个都有数百万条记录，它们之所以位于两台服务器上是因为它们太大了。 对表的任何更改和分页都会给我们带来巨大的性能损失。

因为它们位于生产环境中，所以我不可能在其现有服务器上以任何方式更改它们。

问题是主键是一个唯一的自动递增字段，因此存在交叉点。

我一直在尝试找出如何使用 mysqldump 命令来忽略某些字段，但 --disable-keys 只是更改表，而不是完全删除键。

此时看起来我需要修改数据库结构以利用主键的校验和或哈希作为实际上应该是唯一的两个唯一字段的组合...我真的不想做这个。

帮助！

Answer 1

为了解决这个问题，我查了这个问题，找到了@pumpkinthehead的答案，并意识到我们需要做的就是find+用NULL替换每行中的主键，这样mysql就会使用默认的auto_increment值。

（完整的 mysqldump 命令）| sed -e "s/([0-9]*,/(NULL,/gi" > my_dump_with_no_primary_keys.sql

原始输出：

INSERT INTO `core_config_data` VALUES
    (2735,'default',0,'productupdates/configuration/sender_email_identity','general'),
    (2736,'default',0,'productupdates/configuration/unsubscribe','1'),

转换后的输出：

INSERT INTO `core_config_data` VALUES
    (NULL,'default',0,'productupdates/configuration/sender_email_identity','general'),
    (NULL,'default',0,'productupdates/configuration/unsubscribe','1'),

注意：这仍然是一个 hack；例如，它会失败如果您的自动增量列不是第一列，但 99% 的时间解决了我的问题。

Answer 2

如果您不关心 auto_increment 列的值是什么，则只需加载第一个文件，重命名表，然后重新创建表并加载第二个文件。 最后，使用

INSERT newly_created_table_name (all, columns, except, the, auto_increment, column)
       SELECT all, columns, except, the, auto_increment, column
         FROM renamed_table_name

Answer 3

您可以创建一个没有主键列的表视图，然后在该视图上运行 mysqldump。

因此，如果您的表“用户”具有以下列：id、name、email

> CREATE VIEW myView AS
  SELECT name, email FROM users

编辑：啊我明白了，我不确定是否还有其他方法。

Answer 4

1. 克隆您的表
2. 删除克隆表中的列 转储
3. 不带结构的克隆表（但使用 -c 选项以获得完整插入）
4. 导入您想要的位置

Answer 5

这是一种彻底的痛苦。 解决这个问题

sed -e "s/([0-9]*,/(/gi" export.sql > expor2.sql 

我通过在转储上运行类似的操作来摆脱主键，然后

sed -e "s/VALUES/(col1,col2,...etc.) VALUES/gi" LinxImport2.sql > LinxImport3.sql

运行除主键之外的所有列来 。 当然，您必须小心 ([0-9]*, 不会替换您真正想要的任何内容。

希望对某人有所帮助。

Answer 6

SELECT null as fake_pk, `col_2`, `col_3`, `col_4` INTO OUTFILE 'your_file'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM your_table;

LOAD DATA INFILE 'your_file' INTO TABLE your_table
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

为了增加奇特感，您可以在接收表上设置一个 before insert 触发器，在插入发生之前设置到达行的新主键，从而使用常规转储并仍然清除您的 pk。 没有测试过，但对此很有信心。

Answer 7

使用虚拟临时主键：

通常使用mysqldump--opts -c。 例如，您的主键是“id”。
编辑输出文件，并将行“dummy_id”添加到表的结构中，其类型与“id”相同（当然不是主键）。 然后修改 INSERT 语句并将“id”替换为“dummy_id”。 导入后，删除“dummy_id”列。

Answer 8

吉米走在正确的道路上。

这就是自动增量键成为 PITA 的原因之一。 一种解决方案是不删除数据而是添加数据。

CREATE VIEW myView AS
SELECT id*10+$x, name, email FROM users

（其中 $x 是唯一标识原始数据库的单个数字）在源数据库上创建视图（您暗示这可能不可能）或使用像 Autocracy 描述的那样的提取例程或将数据加载到源数据库上的临时表中测试盒。

或者，不要在测试系统上创建表 - 而是为 src 数据放入单独的表，然后创建一个从它们中获取的视图：

CREATE VIEW users AS
(SELECT * FROM users_on_a) UNION (SELECT * FROM users_on_b)

C.

Answer 9

我一直使用的解决方案是对要导出的数据进行常规 SQL 导出，然后使用 RegEx 查找和替换编辑器从插入语句中删除主键。 我个人使用 Sublime Text，但我确信 TextMate、Notepad++ 等也可以做到同样的事情。

然后，我只需将查询复制粘贴到 HeidiSQL 的查询窗口或 PHPMyAdmin 中，运行数据应插入到的数据库中的查询。 如果有大量数据，我会将插入查询保存到 SQL 文件并使用文件导入。 复制& 粘贴大量文本通常会使 Chrome 冻结。

这听起来可能需要大量工作，但我很少在导出和导入之间使用超过几分钟的时间。 可能比我在接受的解决方案上使用的要少得多。 我已经在数十万行上使用了这种解决方案方法，没有出现任何问题，但我认为当您达到数百万行时，就会出现问题。

Answer 10

我喜欢临时表路线。

create temporary table my_table_copy
select * from my_table;

alter table my_table_copy drop id;

// Use your favorite dumping method for the temporary table

与其他解决方案一样，这不是一个万能的解决方案（特别是考虑到 OP 的数百万行），但即使在 10^6 行时，也需要几秒钟的时间才能运行，但可以正常工作。

Answer 11

最简单的方法：

1) Drop the primary id field
2) Dump the table with -c option
3) Re-add Primary Key to the dump file
4) run the dump to create new keys

不需要克隆表或类似的东西； 如果您只是想清理您的钥匙，这就是您需要做的。 如果你想排序或重新排序，你可以在 2 到 4 之间进行