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 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. 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
