如何将一个相同的表中的索引复制到另一个已存在的相同表中？

Question

我有大约250个表格（mysql），它们的结构完全相同，除了索引。其中一个表格拥有所有正确、当前和最新的索引，我想将该索引结构传播到其他249个表格。

我搜索到的所有内容都是关于复制带有其内容和索引结构的表格，但我不想这样做（每个表格中的数据都不同，只有结构相同），或者创建一个具有相同结构的新表格，我也不想这样做。

我希望有一种方法可以使用ALTER TABLE告诉mysql模仿正确表格的表格结构并将其应用于其他表格，优化表格，然后通过php脚本运行这些命令以应用于所有249个表格，但我无法找到一个ALTER TABLE命令来实现这一点。

有什么建议吗？

- user1621030

---

我有大约250个表（mysql），它们都共享完全相同的结构...哦不！！！ - Mark Byers

不，没有MySQL命令可以自动完成这个操作，因为这不是任何人应该遇到的情况。 - Dan Grossman

Answer 1

在“good”表上运行SHOW CREATE TABLE [table_name]命令。复制索引行，并将其用于脚本中，以运行每个其他表的等效ALTER TABLE CREATE INDEX命令。

如果您想要完全自动化整个过程，您需要从SHOW INDEXES或SHOW CREATE TABLE调用中解析索引定义。但我假设您只需运行一次，因为这是一个奇怪的情况。

- Matt S

Answer 2

你可以使用 CREATE TABLE LIKE 命令来创建新表，该表具有正确的结构和索引，但没有数据，然后使用 INSERT 语句将旧数据复制过来。

例如，假设我们有一个名为 users_master 的表具有正确的索引，现在我们希望将正确的索引赋予 users_n 表。我们可以像这样完成：

CREATE TABLE users_n_indexed LIKE users_master;
INSERT users_n_indexed SELECT * FROM users_n;
DROP TABLE users_n;
RENAME TABLE users_n_indexed users_n;

在运行任何DROP语句之前，务必创建备份。

- Jeremy Salwen

---

只是为了补充一下这个答案，我会提交一个新的答案给那些偶然发现这个问题的人。 - Jeremie

Answer 3

很棒的答案，Jeremy。以防万一你的mysql出问题，

CREATE TABLE users_n_indexed LIKE users_master;
INSERT users_n_indexed SELECT * FROM users_master;
DROP TABLE users_n;
RENAME TABLE users_n_indexed TO users_n;

你需要在“REMOVE TABLE”语句中添加一个“TO”。

- Jeremie

Answer 4

我花了一些时间为此创建了一些东西。我的要求在最后发生了变化，所以我无法很好地测试它。但我相信这将不需要太多更改就可以工作。我不喜欢编辑“SHOW CREATE”的输出 - 太脏了。因此，我转而使用“SHOW INDEX”创建的数据库字段：

$res = mysqli_query($conn,"SHOW INDEX FROM tableA");
$indexes = array();
$nonuniques = array();
while($row = mysqli_fetch_assoc($res))
{ if(!isset($indexes[$row['Key_name']]))
    $indexes[$row['Key_name']] = array();
  $indexes[$row['Key_name']][] = $row['Column_name'];
  $nonuniques[$row['Key_name']] = $row['Non_unique'];
}
foreach($indexes AS $index => $fields)
{ $prefix = "";
  $indexname = "`".$index."`";
  if($index == "PRIMARY")
  { $indexname = "";
    $prefix = "PRIMARY";
  }
  else if($nonuniques[$index] == 0)
    $prefix = "UNIQUE";
  mysqli_query($conn,"ALTER TABLE tableB ADD ".$prefix." INDEX ".$indexname." ("`".implode("`,`", $fields)."`");
}

- user2587656

Answer 5

这是一篇旧的帖子，但我想我可以帮助其他人。你可以像这样做：

SELECT concat("ALTER TABLE ",trim(TABLE_NAME)),group_concat(IF(INDEX_NAME="PRIMARY", 
"ADD PRIMARY KEY "," ADD INDEX `"),INDEX_NAME,"` (`",column_name,"` ASC) "), ";" 
FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = 'SCHEMA' and table_name like "TABLE_NAME%" 
and seq_in_index=1 group by table_name;`

或者将索引从一个具有相同字段的表复制到其他表中：

SELECT concat("ALTER TABLE ",trim(b.TABLE_NAME)),
group_concat(IF(a.INDEX_NAME="PRIMARY","ADD PRIMARY KEY "," ADD INDEX `"),
a.INDEX_NAME,"` (`",a.column_name,"` ASC) "), ";" FROM INFORMATION_SCHEMA.STATISTICS a 
join INFORMATION_SCHEMA.STATISTICS b  WHERE a.TABLE_SCHEMA = 'SCHEMA' and  
a.table_name ="INDEX_TABLE" and b.table_name like "TABLES_TO_INDEX%" and 
a.seq_in_index=1 group by b.table_name;

然后复制粘贴到查询窗口。

- Ricardo Frasson