MySQL：在创建临时表时，是否自动创建主键？

Question

我有一个查询需要处理很长时间（大约1100万条数据），并涉及三个连接（无法停止进行检查）。其中一个连接是使用临时表实现的。

当我使用具有主键的表中的数据创建临时表时，新表是否会继承索引？或者我需要在新的临时表中显式创建一个索引（使用父表中的主键）？

- Nicolas

---

这个问题看起来像是XY问题。 - N.B.

Answer 1

不会自动定义索引 - 对于明确定义的临时表，不会自动定义索引。您需要在表创建时定义索引或使用 ALTER TABLE .. 在之后定义索引。

您可以使用 SHOW CREATE TABLE my_temptable 检查它。

尝试以下脚本：

drop table if exists my_persisted_table;
create table my_persisted_table (
    id int auto_increment primary key,
    col varchar(50)
);
insert into my_persisted_table(col) values ('a'), ('b');

drop temporary table if exists my_temptable;
create temporary table my_temptable as 
    select * from my_persisted_table;

show create table my_temptable;

alter table my_temptable add index (id);

show create table my_temptable;

第一个SHOW CREATE语句将不会显示索引：

CREATE TEMPORARY TABLE `my_temptable` (
  `id` int(11) NOT NULL DEFAULT '0',
  `col` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

使用ALTER TABLE创建索引后，我们可以通过第二个SHOW CREATE语句来查看它：

CREATE TEMPORARY TABLE `my_temptable` (
  `id` int(11) NOT NULL DEFAULT '0',
  `col` varchar(50) DEFAULT NULL,
  KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Demo: http://rextester.com/JZQCP29681

- Paul Spiegel

Answer 2

这个语法也可以工作：

create temporary table my_temptable
    ( PRIMARY KEY(id) )
    select * from my_persisted_table;

也就是说，您可以从一开始就使用额外的CREATE TABLE子句。如果SELECT将行传递到具有PK顺序的InnoDB表中，则这尤其有效。

create temporary table my_temptable
    ( PRIMARY KEY(id) )
        ENGINE=InnoDB
    select * from my_persisted_table
        ORDER BY id;

- Rick James

Answer 3

临时表与数据库（模式）之间的关系非常松散。删除数据库不会自动删除在该数据库中创建的任何临时表。此外，如果在CREATE TABLE语句中使用数据库名称限定表名，则可以在不存在的数据库中创建临时表。在这种情况下，对表的所有后续引用都必须限定为数据库名称。

during generation of TEMPORARY table you have to mention all record of the table

https://dev.mysql.com/doc/refman/5.7/en/create-temporary-table.html

- Md Nazrul Islam

---

那不是问题。我需要重新创建索引吗？ - Nicolas

好的，我明白了。你不需要从这些列创建索引。 - Md Nazrul Islam