I've seen this problem myself on 3.22.32. The problem is the quotes, remove
them and everything works fine.
The real pain is that if you are using DBI with Perl and pass the value
through the quote function (we pass everything through just out of habit),
it adds quotes to the number and you see this error. We ended up writing a
wrapper around the MySQL object to test to see if the value is 100% numeric
and if so not quote it. I'm not sure why MySQL can't reliably compare a
bigint with quotes around it, but that is definitely the problem. Despite
the docs, we use a lot of unsigned big ints (as keys)and this is the only
problem we've ever had.
Eric Pickup
http://buildacommunity.com
----- Original Message -----
From: "Fred van Engen" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, February 20, 2001 9:41 AM
Subject: Re: BigInt with primary key
> On Tue, Feb 20, 2001 at 06:56:28PM +0800, chchen wrote:
> > hi all
> >
> > i use mysql-3.23.32
> >
> > my project need to use unsigned Bigint as a primary key.
> > but when i insert many rows in this table.
> > sometimes it will error with Duplicate such like
> >
> > insert into Table
values('9231852172526977164',0,0,52056,0,0,0,0,0,52056,0,0,0,0,0,0,0,11,0,'1
84000','184000',1),'9231898557453533324',0,0,5532,0,0,0,0,0,5532,0,0,0,0,0,0
,0,11,0,'184000','184000',1),'9230422383529723532',147,0,0,0,0,0,0,0,147,0,0
,0,0,0,0,0,91,0,'184000','184000',1) query failed
> > Duplicate entry '9231898557453533324' for key 1
> >
> > but actually if i select * from Table where a='9231898557453533324'
> >
> > it can't find anything.........what's wrong with this, bug?
> >
> > p.s. unsigned Bigint should be from 0 to 18446744073709551615, so
'9231898557453533324' is included in it, right?
> >
>
> True, but the manual warns against using UNSIGNED BIGINT for anything
> else than bitsets:
>
> ! BIGINT[(M)] [UNSIGNED] [ZEROFILL]
> ! A large integer. The signed range is -9223372036854775808 to
> ! 9223372036854775807. The unsigned range is 0 to
> ! 18446744073709551615. Note that all arithmetic is done using signed
> ! BIGINT or DOUBLE values, so you shouldn't use unsigned big integers
> ! larger than 9223372036854775807 (63 bits) except with bit functions!
> ! Note that `-', `+', and `*' will use BIGINT arithmetic when both
> ! arguments are INTEGER values! This means that if you multiply two
> ! big integers (or results from functions that return integers) you
> ! may get unexpected results if the result is larger than
> ! 9223372036854775807.
>
> I could imagine that this includes using it for indexes, because
> this will do some comparisons on BIGINT values. But then, the manual
> is not conclusive about this I think.
>
> Can you get away with smaller (i.e. UNSIGNED) BIGINT values? It may
> be worth a try then.
>
>
> Regards,
>
> Fred.
>
> --
> Fred van Engen XO Communications B.V.
> email: [EMAIL PROTECTED] Televisieweg 2
> tel: +31 36 5462400 1322 AC Almere
> fax: +31 36 5462424 The Netherlands
>
> ---------------------------------------------------------------------
> 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
---------------------------------------------------------------------
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
