如何使用ON DUPLICATE KEY插入... SELECT [英] How to INSERT INTO...SELECT with ON DUPLICATE KEY

问题描述

我有两个结构相同的表.表A包含所有当前广告，表B包含已存档的广告.第1列(ad_id)是主键，AI，INT.表引擎是MyISAM.

我需要将某个日期之前的所有表A广告复制到存档表B中.我的目标是重复除ad_id之外的所有字段，ad_id应该自动递增.这是我尝试过的:

INSERT INTO B`(`ad_id`, `ad_advertiser`, `ad_ln`, `ad_expire`) 
    SELECT *
    FROM A
    WHERE YEAR( ad_expire ) <= 2012

表B有成千上万的广告，表A经常被刷新，以至于独特的id字段具有很少的数字，该数字经常与表B中的id重复.因此MySQL发出颤抖并告诉我我有一个Duplicate entry '8577' for key 'PRIMARY'./p>

所以我做了几次尝试来克服这个问题:

首先，我尝试选择要插入的各个列，将ad_id设置为NULL:

INSERT INTO B(`ad_id`, `ad_advertiser`, `ad_ln`, `ad_expire`) 
    SELECT (NULL, `ad_advertiser`, `ad_ln`, `ad_expire`)
    FROM A
    WHERE YEAR( ad_expire ) <= 2012 

这将导致错误#1241 - Operand should contain 1 column(s)，如果我使用通配符*选择器，该错误将消失，但随后出现重复错误.

接下来，我尝试了SELECT LAST_INSERT_ID()，它总是返回0.

然后我使用ON DUPLICATE KEY UPDATE尝试了一些，但似乎无法正常工作.

我什至尝试通过以下方式获取最高ID:

SELECT @max := max(ad_id) FROM B;

INSERT INTO B`(`ad_id`, `ad_advertiser`, `ad_ln`, `ad_expire`) 
  SELECT *
  FROM A
  WHERE YEAR( ad_expire ) <= 2012

ON DUPLICATE KEY UPDATE ad_id = @max + 1

这仅适用于一行，然后再次导致重复条目(因为@max是静态变量).

我在这里做错了什么?我使这种方式变得太困难了吗?

解决方案

在您的情况下，为什么不使用?

INSERT INTO B(`ad_advertiser`, `ad_ln`, `ad_expire`) 
    SELECT (`ad_advertiser`, `ad_ln`, `ad_expire`)
    FROM A
    WHERE YEAR( ad_expire ) <= 2012 

I have two tables with identical structure. Table A contains all the current ads, Table B contains the archived ads. Column 1 (ad_id) is Primary Key, AI, INT. Table engine is MyISAM.

I need to copy all the table A ads preceding a certain date to the archive, table B. My goal is that all fields except ad_id are duplicated, ad_id should get auto-incremented. Here is what I have attempted:

INSERT INTO B`(`ad_id`, `ad_advertiser`, `ad_ln`, `ad_expire`) 
    SELECT *
    FROM A
    WHERE YEAR( ad_expire ) <= 2012

Table B has many thousands of ads, Table A gets flushed often enough that the unique id field has low numbers that often duplicate the id's in Table B. So MySQL chucks a wobbly and tells me I have a Duplicate entry '8577' for key 'PRIMARY'.

So I made several attempts to get past that:

First I tried selecting the individual columns to insert, setting ad_id to NULL:

INSERT INTO B(`ad_id`, `ad_advertiser`, `ad_ln`, `ad_expire`) 
    SELECT (NULL, `ad_advertiser`, `ad_ln`, `ad_expire`)
    FROM A
    WHERE YEAR( ad_expire ) <= 2012 

That results in the error #1241 - Operand should contain 1 column(s), which goes away if I use the wildcard * selector, but then I get the duplicate error.

Next I tried SELECT LAST_INSERT_ID(), which always returns 0.

Then I tried a few using ON DUPLICATE KEY UPDATE, but I can't seem to get that to work.

I even tried to grab the highest id with:

SELECT @max := max(ad_id) FROM B;

INSERT INTO B`(`ad_id`, `ad_advertiser`, `ad_ln`, `ad_expire`) 
  SELECT *
  FROM A
  WHERE YEAR( ad_expire ) <= 2012

ON DUPLICATE KEY UPDATE ad_id = @max + 1

This works for exactly one row, then results in a duplicate entry again (since @max is a static variable).

What am I doing wrong here? Am I making this way too difficult?

解决方案

in your case why not use ?

INSERT INTO B(`ad_advertiser`, `ad_ln`, `ad_expire`) 
    SELECT (`ad_advertiser`, `ad_ln`, `ad_expire`)
    FROM A
    WHERE YEAR( ad_expire ) <= 2012 

这篇关于如何使用ON DUPLICATE KEY插入... SELECT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！