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
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
-----Original Message-----
From: Paul DuBois [mailto:[EMAIL PROTECTED]
Sent: Monday, March 10, 2003 11:14 AM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]; Douglas B Jones
Subject: RE: automatically incrementing an int value
At 11:09 -0500 3/10/03, Douglas B. Jones wrote:
>Hi,
>
>
>Great! This works, I did not use the 'null' and that is
>where I had a problem. One other questions: is this atomic?
>If I have several processes trying to do this at one time,
>will each one correctly update the table (assuming they have
>the same 'name' value.
Yes.
> Also, in the part: unique (name(100)),
>what does the 100 do here? Is that saying the first 100 chars
>are considered unique?
Exactly.
>
>Thanks,
>Cheers,
>Douglas
>
>> -----Original Message-----
>> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
>> Sent: Monday, March 10, 2003 10:36 AM
>> To: Douglas B Jones
>> Cc: [EMAIL PROTECTED]
>> Subject: Re: automatically incrementing an int value
>>
>>
>>
>> As I read the manual, the REPLACE command will do what you want.
>>
>> Make the name field UNIQUE, and the number field AUTO_INCREMENT NOT
NULL.
>> Replace dos a delete-if-present, insert. The insert generates a new
ID.
>>
>> See test below, and note two rows affected by second replace.
>>
>> mysql> create table test (a int auto_increment not null, name
tinytext not
>> null, primary key (a), unique (name(100))) ;
>> Query OK, 0 rows affected (0.02 sec)
>>
>> mysql> replace into test values (null, "hello") ;
>> Query OK, 1 row affected (0.01 sec)
>>
>> mysql> select * from test ;
>> +---+-------+
>> | a | name |
>> +---+-------+
>> | 1 | hello |
>> +---+-------+
>> 1 row in set (0.01 sec)
>>
>> mysql> replace into test values (null, "hello") ;
>> Query OK, 2 rows affected (0.02 sec)
>>
>> mysql> select * from test ;
>> +---+-------+
>> | a | name |
>> +---+-------+
>> | 2 | hello |
>> +---+-------+
> > 1 row in set (0.00 sec)
---------------------------------------------------------------------
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
