CREATE ... SELECT 中的 MySQL 奇怪错误：错误 1062 (23000)：重复条目 '0'对于钥匙 1
这是有问题的查询（其预期含义：将与实体 530 配对的所有实体拉入一个新表，并包含对的计数）：
CREATE TEMPORARY TABLE paired (
entity_id INTEGER PRIMARY KEY,
numrels INTEGER
)
SELECT I.entity2_id, COUNT(I.relation_id) AS numrels
FROM pairs I
WHERE I.entity1_id = 530 AND I.entity2_id IS NOT NULL
GROUP BY I.entity2_id
;
我将错误消息解释为：
ERROR 1062 (23000): Duplicate entry '0' for key 1
抱怨我违反了主键的唯一性。但是，我按该值分组，这应该确保唯一性，对吗？然后我想尝试一下：
CREATE TEMPORARY TABLE paired (
entity_id INTEGER PRIMARY KEY,
numrels INTEGER
)
;
INSERT INTO paired
SELECT I.entity2_id, COUNT(I.relation_id) AS numrels
FROM pairs I
WHERE I.entity1_id = 530 AND I.entity2_id IS NOT NULL
GROUP BY I.entity2_id
;
令人惊讶的是，这没有任何问题，尽管根据我的理解，两者应该是等效的。
什么给？！？
供参考：
mysql Ver 14.12 Distrib 5.0.82sp1, for redhat-linux-gnu (x86_64) using readline 5.1
This is the problematic query (with the intended meaning: pull all entities paired with entity 530 into a new table, with the count of the pairs):
CREATE TEMPORARY TABLE paired (
entity_id INTEGER PRIMARY KEY,
numrels INTEGER
)
SELECT I.entity2_id, COUNT(I.relation_id) AS numrels
FROM pairs I
WHERE I.entity1_id = 530 AND I.entity2_id IS NOT NULL
GROUP BY I.entity2_id
;
I interpret the error message:
ERROR 1062 (23000): Duplicate entry '0' for key 1
as the complaint that I am violating primary key's uniqueness. However, I am grouping by that value, which should ensure the uniqueness, right? Then I thought to try this:
CREATE TEMPORARY TABLE paired (
entity_id INTEGER PRIMARY KEY,
numrels INTEGER
)
;
INSERT INTO paired
SELECT I.entity2_id, COUNT(I.relation_id) AS numrels
FROM pairs I
WHERE I.entity1_id = 530 AND I.entity2_id IS NOT NULL
GROUP BY I.entity2_id
;
Surprisingly enough, this works without any problems, even though, according to my understanding, the two should be equivalent.
What gives?!?
For reference:
mysql Ver 14.12 Distrib 5.0.82sp1, for redhat-linux-gnu (x86_64) using readline 5.1
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您的陈述并不等同。
CREATE ... SELECT创建您在语句的CREATE部分中提到的列（即，entity_id 和 numrels），此外还为语句的SELECT部分。您的新表中最终会出现四列。SELECT的结果被插入到最后两列中。其他列填充了默认值，这会导致违反主键的唯一性。另请参见 http://dev.mysql.com/doc /refman/5.0/en/create-table-select.html
Your statements are not equivalent.
CREATE ... SELECTcreates the columns you mention in theCREATEpart of the statement (that is, entity_id and numrels), and in addition creates columns for each column of theSELECTpart of the statement. You end up with four columns in your new table. The results ofSELECTare inserted into the last two columns. The other columns are filled with their default values, which results in violating the uniqueness of your primary key.See also http://dev.mysql.com/doc/refman/5.0/en/create-table-select.html