List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:March 12 2003 5:16pm
Subject:RE: automatically incrementing an int value
View as plain text  
At 11:41 -0500 3/12/03, Douglas B. Jones wrote:
>Hi,
>
>I understood replace to only increment n when it matches the
>name value. There are 122,111 statements, but when you add
>up the numbers in the n column, they exceed 122,111. They should

I don't know what you're trying to say here.  If you *add up* the
numbers in the column, of course they will exceed the number of records.

>sum up (when you add all them up including the ones I did not
>show) to 122,111. If you add up the ones I show, you get way
>more than 122,111. I did a little test on:
>
>replace into virus values(NULL,"VBS/LoveLet-E");
>replace into virus values(NULL,"VBS/LoveLet-E");
>replace into virus values(NULL,"VBS/LoveLet-E");
>replace into virus values(NULL,"VBS/LoveLet-G");
>replace into virus values(NULL,"WM97/Myna-C");
>
>The results were:
>
>INSERT INTO virus VALUES (3,'VBS/LoveLet-E');
>INSERT INTO virus VALUES (4,'VBS/LoveLet-G');
>INSERT INTO virus VALUES (5,'WM97/Myna-C');
>
>I would have expected 3,4,5 to be 3,1,1. I was expecting it to start
>from zero each time it got a new name, it looks like it takes the last
>n value and then start from there with the new name. Does this make
>sense what I am asking? Is there a way of doing what I want? Yes, I
>could
>insert and do a count, but I was looking for another way so that the
>select would not be so resource expensive.

It sounds like you're expecting values to be reused in an AUTO_INCREMENT
column.

They aren't.

If you want to prevent generation of new values whenever you get a duplicate
name value, try the strategy I suggested (below) of using INSERT and ignoring
duplicate-key errors.


>
>Thanks,
>Cheers,
>Douglas
>
>-----Original Message-----
>From: Paul DuBois [mailto:paul@stripped]
>Sent: Wednesday, March 12, 2003 11:29 AM
>To: douglas@stripped; Alec.Cawley@stripped
>Cc: mysql@stripped; Douglas B Jones
>Subject: RE: automatically incrementing an int value
>
>
>At 10:22 -0500 3/12/03, Douglas B. Jones wrote:
>>Hi,
>>
>>I just tried the below:
>>
>>create table virus (
>>          n int auto_increment not null,
>>          name char(128) not null,
>>          primary key(n),
>>          unique(name(100))
>>);
>>
>>with a data file that has 122,111 sql commands like:
>>
>>replace into virus values(NULL,"VBS/LoveLet-E");
>>replace into virus values(NULL,"VBS/LoveLet-E");
>>replace into virus values(NULL,"VBS/LoveLet-E");
>>replace into virus values(NULL,"VBS/LoveLet-G");
>>replace into virus values(NULL,"WM97/Myna-C");
>>replace into virus values(NULL,"VBS/LoveLet-G");
>>replace into virus values(NULL,"WM97/Myna-C");
>>replace into virus values(NULL,"VBS/LoveLet-G");
>>replace into virus values(NULL,"VBS/LoveLet-G");
>>replace into virus values(NULL,"W32/Sircam-A");
>>
>>Now when I do a:
>>
>>grep VBS/LoveLet-G sqlfile | wc
>>
>>I get:
>>
>>         123       492      6027
>>
>>123 entries for VBS/LoveLet-G in the file. When I do a mysqldump of
>>the data file and just grep for VBS:
>>
>>mysqldump virus|grep VBS
>  >
>  >INSERT INTO virus VALUES (3,'VBS/LoveLet-E');
>>INSERT INTO virus VALUES (111009,'VBS/LoveLet-G');
>>INSERT INTO virus VALUES (55841,'VBS/Stages-A');
>>INSERT INTO virus VALUES (121521,'VBS/LoveLet-AS');
>>INSERT INTO virus VALUES (1208,'VBS/SST-A');
>>INSERT INTO virus VALUES (85602,'VBS/VBSWG-X');
>>INSERT INTO virus VALUES (1215,'VBS/VBSWG-Z');
>>INSERT INTO virus VALUES (5846,'VBS/LoveLet-CL');
>>INSERT INTO virus VALUES (5996,'VBS/VBSWG-Fam');
>>INSERT INTO virus VALUES (83835,'VBS/Haptime-Fam');
>>INSERT INTO virus VALUES (55356,'VBS/LoveLet-F');
>>INSERT INTO virus VALUES (55546,'VBS/FreeLinks');
>>INSERT INTO virus VALUES (91207,'VBS/Kakworm');
>>INSERT INTO virus VALUES (117623,'VBS/Redlof-A');
>>
>>As you can see, the numbers (n field) are way to high? Is this a bug
>
>Why do you say that?  You indicated that the data file has 122,111
>statements
>in it.  I see no values for the n column that are larger than that
>value.
>REPLACE will increment the value of n when you specify a value of NULL
>for that column.  It's behaving as it's supposed to.  Perhaps you should
>use INSERT instead of REPLACE and process the file with mysql --force
>to ignore duplicate key errors.  Specify the statements like this:
>
>INSERT INTO virus (name) VALUES('VBS/xxx');
>
>>in mysql or n the sql? Even if I say unique(name) instead of
>>unique(name(100)),
>>I get the same results. Please note that I have tried destroying the
>>table
>>as well as the db, still get the same results.
>>
>>Any ideas?
>>
>>Thanks,
>>Cheers,
>>Douglas

Thread
automatically incrementing an int valueDouglas B. Jones10 Mar
Re: automatically incrementing an int valueAlec.Cawley10 Mar
  • RE: automatically incrementing an int valueDouglas B. Jones10 Mar
    • RE: automatically incrementing an int valuePaul DuBois10 Mar
      • RE: automatically incrementing an int valueDouglas B. Jones12 Mar
        • RE: automatically incrementing an int valueDouglas B. Jones12 Mar
        • RE: automatically incrementing an int valuePaul DuBois12 Mar
          • RE: automatically incrementing an int valueDouglas B. Jones12 Mar
            • RE: automatically incrementing an int valuePaul DuBois12 Mar
              • Re: automatically incrementing an int valueBenjamin Pflugmann12 Mar
            • Re: automatically incrementing an int valueJoseph Bueno12 Mar
            • RE: automatically incrementing an int valueKeith C. Ivey12 Mar
        • RE: automatically incrementing an int valueKeith C. Ivey12 Mar
          • RE: automatically incrementing an int valueDouglas B. Jones12 Mar
          • RE: automatically incrementing an int valuePaul DuBois12 Mar
RE: automatically incrementing an int valueAlec.Cawley12 Mar
  • RE: automatically incrementing an int valueDouglas B. Jones12 Mar