此查询将锁定哪些表？
我正在使用下面的查询在两个表之间迁移数据。我遇到了表锁定问题，因此我正在考虑将表引擎从 MyISAM 更改为 InnoDB。我需要为哪些表执行此操作？只是我正在写入的表，还是我正在写入的表和我正在读取的表？
INSERT INTO table1 (
field1, field2, field2
)
SELECT
field1, field2,
(
SELECT
temp.field4
FROM
table1 AS temp
WHERE
temp.id = table2.id
ORDER BY
temp.something DESC
LIMIT
1
) + 1 AS field3
FROM
table2
WHERE
NOT EXISTS (
SELECT
1
FROM
table1 AS temp
WHERE
temp.id = table2.id
)
I am migrating data between two tables using the query below. I had table locking issues, so I am considering changing the table engines from MyISAM to InnoDB. Which tables do I need to do that for? Just the table I am writing to, or both the table I am writing to and the one I am reading from?
INSERT INTO table1 (
field1, field2, field2
)
SELECT
field1, field2,
(
SELECT
temp.field4
FROM
table1 AS temp
WHERE
temp.id = table2.id
ORDER BY
temp.something DESC
LIMIT
1
) + 1 AS field3
FROM
table2
WHERE
NOT EXISTS (
SELECT
1
FROM
table1 AS temp
WHERE
temp.id = table2.id
)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
锁定什么
这取决于。如果您想确保在执行更新时不会写入新数据，则应该锁定两个表。如果这只是一个写入问题，那么只锁定要进行更新的表。
常见误解
MyISAM 不能进行锁定是错误。 MyISAM 无法实现行级锁定。 MyISAM 完全能够锁定表。
根据以下列表做出您的决定。
决策助手
您的表的插入、删除和更新次数是否比选择的次数多得多？
=> InnoDB
需要全文检索吗
=> MyISAM
您是否喜欢/需要明确的外键约束
=> InnoDB
磁盘空间或内存是问题吗？
=> MyISAM
有疑问吗？
=>MyISAM
没有赢家。
What to lock
It depends. If you want to make sure that no new data can be written while you perform the update you should lock both tables. If it's just a matter of writing then only lock the table where you're doing the update.
Common missconception
It is wrong that MyISAM cannot do locks. It is only row level locking that MyISAM is not capable of. MyISAM is perfectly able to lock tables.
Make your desicion based on the following list.
Decision Helper
Is your table going to be inserted, deleted, and updated much more than it is going to be selected?
=> InnoDB
Do you need full-text search
=> MyISAM
Do you prefer/require clear foreign key constraints
=> InnoDB
Is disk-space or ram an issue?
=> MyISAM
In Doubt?
=>MyISAM
There is no winner.
表 1 应被锁定。有人可以确认吗？
我只用Innodb。我忘了myisam是做什么的！
不管怎样，你应该把一切都切换到 Innodb。如今 Innodb 的读取速度同样快。
table 1 should be locked. Can someone confirm?
I only use Innodb. i forget what myisam does!
Either way, you should switch everything to Innodb. These days Innodb is just as fast for reads anyway.