MySQL 创建表为 SELECT
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9715580/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL Create Table as SELECT
提问by clops
Everytime I use MySQL's CREATE TABLE AS SELECT ...all the tables/indexes being selected from are locked for the durationof the query. I do not really understand why? Is there any way around this?
每次我使用 MySQL 时，CREATE TABLE AS SELECT ...所有从中选择的表/索引在查询期间都被锁定。我真的不明白为什么？有没有办法解决？
Using:MySQL 5.1.41and InnoDB
使用：MySQL 5.1.41和InnoDB
Added Example:
添加示例：
For example, the following query might take up to 10 minutes to complete:
例如，以下查询最多可能需要 10 分钟才能完成：
CREATE TABLE temp_lots_of_data_xxx AS
SELECT
a.*
b.*
c.*
FROM a
LEFT JOIN b ON a.foo = b.foo
LEFT JOIN c ON a.foo = c.foo
Trying to update values in tables a, b or c during the above query will wait for the above query to finish first. I want to avoid this lock, as I am not interested in the most complete data in the created temp table.
在上述查询期间尝试更新表 a、b 或 c 中的值将等待上述查询首先完成。我想避免这种锁定，因为我对创建的临时表中最完整的数据不感兴趣。
p.s. SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;yields no change in behavior.
psSET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;不会改变行为。
采纳答案by barryhunter
See also http://www.mysqlperformanceblog.com/2006/07/12/insert-into-select-performance-with-innodb-tables/
另见 http://www.mysqlperformanceblog.com/2006/07/12/insert-into-select-performance-with-innodb-tables/
if not using replication, can change innodb_locks_unsafe_for_binlog to change this locking behaviour.
如果不使用复制，可以更改 innodb_locks_unsafe_for_binlog 以更改此锁定行为。
Or can dump the data to a file, then reload the data from a file. This also avoids the locks.
或者可以将数据转储到文件，然后从文件重新加载数据。这也避免了锁。
回答by ypercube??
Have you tried to do the operation in 2 phases (first Create the table, then Insert the values) and having set the lowest isolation level?:
您是否尝试分 2 个阶段进行操作（首先创建表，然后插入值）并设置了最低隔离级别？：
CREATE TABLE temp_lots_of_data_xxx AS
SELECT
a.*
b.*
c.*
FROM a
LEFT JOIN b ON a.foo = b.foo
LEFT JOIN c ON a.foo = c.foo
WHERE FALSE
INSERT INTO temp_lots_of_data_xxx
SELECT
a.*
b.*
c.*
FROM a
LEFT JOIN b ON a.foo = b.foo
LEFT JOIN c ON a.foo = c.foo
回答by fancyPants
I didn't test this, but you might have a try with
我没有测试这个，但你可以试试
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
CREATE TABLE ...
COMMIT ; /*See comment by Somnath Muluk*/
But be aware:
但请注意：
Select statements are performed in a nonlocking fashion, but a possible earlier version of a row might be used. Thus, using this isolation level, such reads are not consistent. This is also called a “dirty read.”
Select 语句以非锁定方式执行，但可能会使用行的早期版本。因此，使用此隔离级别，此类读取不一致。这也称为“脏读”。
Read more about it here:
在此处阅读更多相关信息：
MySQL SET TRANSACTION manual entry
EDIT: added the COMMIT ;
编辑：添加了 COMMIT ;
回答by huzeyfe
If your engine is InnoDB than it uses automatic row-level locking. Update statements have higher priority then select statements so that's why you are having this problem.
如果您的引擎是 InnoDB，则它使用自动行级锁定。更新语句的优先级高于选择语句，这就是您遇到此问题的原因。
In order to workaround this issue you could SET LOW_PRIORITY_UPDATES=1and then you should be able to run your command. But this does not fully fit your case. So you could also give higher priority to a SELECTstatement as well. To give a specific SELECTstatement higher priority, use the HIGH_PRIORITYattribute.
为了解决此问题，您可以SET LOW_PRIORITY_UPDATES=1然后应该能够运行您的命令。但这并不完全适合您的情况。所以你也可以给SELECT语句赋予更高的优先级。要赋予特定SELECT语句更高的优先级，请使用该HIGH_PRIORITY属性。
CREATE TABLE temp_lots_of_data_xxx AS
SELECT HIGH_PRIORITY
a.*
b.*
c.*
FROM a
LEFT JOIN b ON a.foo = b.foo
LEFT JOIN c ON a.foo = c.foo
For details please refer to this page table-locking-issuesthis page select-syntaxand also this page: option_mysqld_low-priority-updates
有关详细信息，请参阅此页面table-locking-issues此页面select-syntax以及此页面：option_mysqld_low-priority-updates
回答by Shalom Craimer
My psychic debugging skills suggest that you're trying to access the tables/indexes while you're debugging the query that uses them.
我的通灵调试技巧建议您在调试使用它们的查询时尝试访问表/索引。
In general, I'd not be too surprised if a CREATE TABLE query locks all the tables and indexes from which it is reading.
一般来说，如果 CREATE TABLE 查询锁定它正在读取的所有表和索引，我不会太惊讶。
If my psychic premonition is right, I'd suggest letting the query finish before accessing the tables and indexes it is using.
如果我的心理预感是正确的，我建议在访问它正在使用的表和索引之前让查询完成。
(Please correct me if I made any wrong assumptions.)
（如果我做出了任何错误的假设，请纠正我。）
回答by hjpotter92
All InnoDB locks held by a transaction are released when the transaction is committed or aborted. Thus, it does not make much sense to invoke LOCK TABLES on InnoDB tables in autocommit=1 mode because the acquired InnoDB table locks would be released immediately.
当事务提交或中止时，事务持有的所有 InnoDB 锁都会被释放。因此，在 autocommit=1 模式下调用 InnoDB 表上的 LOCK TABLES 没有多大意义，因为获取的 InnoDB 表锁会立即释放。
As read here
在这里阅读
EDITand this:
编辑和这个：
You cannot lock additional tables in the middle of a transaction because LOCK TABLES performs an implicit COMMIT and UNLOCK TABLES.
您不能在事务中间锁定其他表，因为 LOCK TABLES 执行隐式 COMMIT 和 UNLOCK TABLES。
回答by cat
I'm not as experienced in mysql but I ran into the same problem in mssql. The solution there was to run the "create table as select..." with zero rows so that it only creates the table with the appropriate structure and releases the locks immediately. Then use "insert into" with the same select statement to populate the table, which won't hold any schema locks while it runs.
我在 mysql 中没有那么有经验，但我在 mssql 中遇到了同样的问题。那里的解决方案是使用零行运行“创建表作为选择...”，以便它只创建具有适当结构的表并立即释放锁。然后使用具有相同选择语句的“插入”来填充表，该表在运行时不会持有任何模式锁。
