请教1个sql语句的问题，困扰我1下午了...:(

猪头骑士

想把一个自己写的论坛程序中的数据导入到phpbb中
在导topics表的时候出现下面问题：
用下面的语句倒入数据，一次性成功，没有错误。
mysql>; insert into phpbb_topics
       ->; select id,type,title,id,
            UNIX_TIMESTAMP(time),hit,reply_num,0,0,0,id,id,0
       ->; FROM post where kind=0;
Query OK, 55942 rows affected (10.67 sec)
Records: 55942  Duplicates: 0  Warnings: 0

然后我清空数据表
mysql>; delete from phpbb_topics;
Query OK, 55942 rows affected (0.02 sec)

接着用下面的方法导入
mysql>; insert into phpbb_topics
    ->; select p.id,p.type,p.title,u.id as user_id, UNIX_TIMESTAMP(p.time),p.hit,p.reply_num,0,0,0,p.id,p.id,0
    ->; FROM post p
    ->; LEFT JOIN user u ON u.username = p.writer
    ->; where p.kind=0;
ERROR 1062: Duplicate entry '75457' for key 1

就出错了
想了半天也没想明白为什么会出错
不可能有重复数据的...

joint

是auto_increment的问题，这个值是自增量的话是不能恢复的只能向上增加

猪头骑士

可是我加上一个order by id的排序语句也没有用


mysql>; insert into phpbb_topics
    ->; select p.id,p.type,p.title,u.id as user_id, UNIX_TIMESTAMP(p.time),p.hit,p.reply_num,0,0,0,p.id,p.id,0
    ->; FROM post p
    ->; LEFT JOIN user u ON u.username = p.writer
    ->; where p.kind=0
    ->; order by p.id;
ERROR 1062: Duplicate entry '75457' for key 1

Trotter

把那张表dorp掉，新建一张表，给进倒，试试。

猪头骑士

[quote]原帖由 "Trotter"]把那张表dorp掉，新建一张表，给进倒，试试。[/quote 发表：
     

没用
一样报错 ....

  

猪头骑士

mysql>; insert into phpbb_topics
    ->; select p.id,p.type,p.title,u.id as user_id, UNIX_TIMESTAMP(p.time),p.hit,p.reply_num,0,0,0,p.id,p.id,0
    ->; FROM post p
    ->; LEFT JOIN user u ON u.username = p.writer
    ->; where p.kind=0
    ->; order by p.id desc;
ERROR 1062: Duplicate entry '117823' for key 1

order子句加上desc也不行
这个时候只insert了1100条记录
还不如不加order的时候能insert 19000多条呢
玉门

猪头骑士

我上午又做了一些尝试
把目标表中的topic_id的auto_increment的属性给去掉

发现数据能到进去了
不过倒入的数据有一些问题

  应该倒入的纪录条数为55960  
   实际倒入的条数为      56157

   多了几百对 一模一样的纪录( 2个2个一模一样，不是几百个都是一个样)
  怎么才能删掉2个一模一样的纪录中的1个啊？

猪头骑士

仔细翻看了1下mysql manual
才发现还有个replace语句..
以前居然没用过
sigh...


6.4.8 REPLACE Syntax

    REPLACE [LOW_PRIORITY | DELAYED]
        [INTO] tbl_name [(col_name,...)]
        VALUES (expression,...),(...),...
or  REPLACE [LOW_PRIORITY | DELAYED]
        [INTO] tbl_name [(col_name,...)]
        SELECT ...
or  REPLACE [LOW_PRIORITY | DELAYED]
        [INTO] tbl_name
        SET col_name=expression, col_name=expression,...

REPLACE works exactly like INSERT, except that if an old record in the table has the same value as a new record on a UNIQUE index or PRIMARY KEY, the old record is deleted before the new record is inserted. See section 6.4.3 INSERT Syntax.


mysql>; REPLACE into phpbb_topics
    ->; select p.id,p.type,p.title,u.id as user_id, UNIX_TIMESTAMP(p.time),p.hit,p.reply_num,0,0,0,p.id,p.id,0
    ->; FROM post p
    ->; LEFT JOIN user u ON u.username = p.writer
    ->; where p.kind=0
    ->; order by p.id ;
Query OK, 56163 rows affected (28.28 sec)
Records: 56163  Duplicates: 198  Warnings: 1

总算搞定了