MySQL将表创建为SELECT [英] MySQL Create Table as SELECT

问题描述

每次我使用MySQL的 CREATE TABLE AS SELECT ... ，所有从中选择的表/索引都会在查询的持续时间内被锁定 。我真的不明白为什么？有什么办法吗？

Everytime I use MySQL's CREATE TABLE AS SELECT ... all the tables/indexes being selected from are locked for the duration of the query. I do not really understand why? Is there any way around this?

使用： MySQL 5.1.41 code> InnoDB

Using: MySQL 5.1.41 and InnoDB

添加示例：

例如，以下查询最多可能需要10分钟才能完成：

For example, the following query might take up to 10 minutes to complete:

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

尝试更新表a，b或c中的值上面的查询将等待上面的查询完成。我想避免这个锁，因为我对创建的临时表中的最完整的数据不感兴趣。

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.

p.s。 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 不会改变行为。

p.s. SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; yields no change in behavior.

推荐答案

另请参阅
http ：//www.mysqlperformanceblog.com/2006/07/12/insert-into-select-performance-with-innodb-tables/

如果没有使用复制，可以更改innodb_locks_unsafe_for_binlog以更改此锁定行为。

if not using replication, can change innodb_locks_unsafe_for_binlog to change this locking behaviour.

或者可以将数据转储到文件，然后从文件重新加载数据。这也避免了锁。

Or can dump the data to a file, then reload the data from a file. This also avoids the locks.

这篇关于MySQL将表创建为SELECT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！