RE: automatically incrementing an int value

Paul DuBois Wed, 12 Mar 2003 10:04:37 -0800

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:[EMAIL PROTECTED]
Sent: Wednesday, March 12, 2003 11:29 AM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]; 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

---------------------------------------------------------------------
Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/           (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: automatically incrementing an int value

Reply via email to