首页 文章

使用UNIQUE KEY的mySQL INSERT IGNORE SELECT不起作用

提问于
浏览 次
1

尝试使用 INSERT INTO SELECT 填充空MySQL表并连接两个源表 . 希望 IGNORE 基于定义为 UNIQUE KEY 的两个目标表列插入重复行，但由于某种原因，仍会插入基于这两列的重复行 .

目的地表定义：

CREATE TABLE `item` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `item_id` int(10) unsigned NOT NULL,
  `account_id` int(10) unsigned NOT NULL,
  `creation_date` datetime NOT NULL,
  `modification_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_item` (`item_id`,`account_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

插入查询：

INSERT IGNORE INTO item
            (id,
             item_id,
             account_id,
             creation_date,
             modification_date)
SELECT tblItem.ID,
       tblItem.itemID,
       tblOrder.accID,
       tblItem.itemTime,
       '0000-00-00 00:00:00'
FROM   tblItem
   INNER JOIN tblOrder
           ON tblItem.orderID = tblOrder.ID

正在插入 item 重复行，类似于以下内容：

id    item_id   account_id  creation_date           modification_date
2587    0       2           11/19/11 2:43           0000-00-00 00:00:00
2575    0       1120        11/17/11 19:32          0000-00-00 00:00:00
2575    0       1120        11/17/11 19:32          0000-00-00 00:00:00
382     60      193         0000-00-00 00:00:00     0000-00-00 00:00:00
941     95      916         10/28/11 15:52          0000-00-00 00:00:00
369     108     1           0000-00-00 00:00:00     0000-00-00 00:00:00
373     108     2           0000-00-00 00:00:00     0000-00-00 00:00:00
378     109     2           0000-00-00 00:00:00     0000-00-00 00:00:00
378     109     2           0000-00-00 00:00:00     0000-00-00 00:00:00

我错过了什么？

提前致谢！

mysql insert

3 回答

  • 0

    尝试 SELECT 子句中的 DISTINCT 关键字：

    INSERT IGNORE INTO item
        (id,
         item_id,
         account_id,
         creation_date,
         modification_date)
SELECT DISTINCT tblItem.ID,
   tblItem.itemID,
   tblOrder.accID,
   tblItem.itemTime,
   '0000-00-00 00:00:00'
FROM   tblItem
   INNER JOIN tblOrder
       ON tblItem.orderID = tblOrder.ID
    回复于
  • 0

    这是我的想法 .

    您似乎在 AUTO_INCREMENT 字段中插入ID . 我建议让你的 item 表分配ID . 如果我正确阅读了手册，使用 IGNORE 会将错误静音到警告并让语句继续 . 执行 INSERT 时，这可能会忽略对表的关键约束 .

    回复于
  • 4

    如CREATE TABLE Syntax所述：

    UNIQUE索引创建一个约束，使索引中的所有值必须是不同的 .

    您在列 (item_id,account_id) 上定义了复合索引，因此约束仅要求每个记录都具有这两列的不同组合 .

    在上面的示例中，似乎违反此约束的唯一记录是：

    +------+---------+------------+---------------------+---------------------+
|   id | item_id | account_id |       creation_date |   modification_date |
+------+---------+------------+---------------------+---------------------+
| 2575 |       0 |       1120 | 2011-11-17 19:32:00 | 0000-00-00 00:00:00 |
| 2575 |       0 |       1120 | 2011-11-17 19:32:00 | 0000-00-00 00:00:00 |
|  378 |     109 |          2 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
|  378 |     109 |          2 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
+------+---------+------------+---------------------+---------------------+

    但是，这些记录似乎也违反了 id 上的 PRIMARY KEY 约束（即它们看起来是相同的记录） . 您显示的输出不太可能是您定义的 item 表的摘录：您几乎肯定会查看其他表或查询的内容 .

    一种可能的解释是，您已经定义了一个同名的 TEMPORARY TABLE ，它隐藏了定义 UNIQUE 约束的基础 item 表 . SHOW CREATE TABLE item; 应该有助于确认您所指的是您所指的表以及该表已经定义了您期望的约束 .

    如果您完全确定该表确实包含 UNIQUE 约束的重复条目（如果 id 列中有重复项，则以下语句将返回 TRUE ），您可以尝试使用myisamchk执行某些表维护 .

    SELECT EXISTS (SELECT * FROM item GROUP BY id HAVING COUNT(*) > 1);
    回复于

相关问题