I would try storing the SHA1() result as a hexadecimal string. Adolfo
> -----Original Message----- > From: Benjamin Pflugmann [mailto:[EMAIL PROTECTED]] > Sent: Sunday, December 29, 2002 7:35 AM > To: Philip Mak > Cc: [EMAIL PROTECTED] > Subject: Re: Storing a SHA1 checksum > > > On Sun 2002-12-29 at 05:28:57 -0500, [EMAIL PROTECTED] wrote: > > sql, table > > > > I'm storing a SHA1 checksum as "varchar(20) binary" in my > application. > > > > After running a test, it seems MySQL will strip trailing > spaces from a > > varchar column, even if it is binary! > > Yes, the BINARY keyword only influences how comparisons are > done (mainly case-sensivity, but also umlauts, etc...). > > Stripping space from VARCHAR is a known deficiency: > > http://www.mysql.com/doc/en/Bugs.html > > It also mentions, that the TEXT/BLOB types are save from it. > > > That means if the last character of my SHA1 checksum > happens to be a > > space, MySQL will corrupt it. > > > > What should I do? It seems I can: > > > > 1. Use blob instead of varchar. > > Problem: blob type is slower. > > Is that really a problem? Did you measure it? If so, I would > be intersted in the results. > > Advantage: Other application programmers do not need to be aware > of the hack. After MySQL is fixed, the source doesn't contain > redundant code. > > > 2. Make my application pad the checksum out to 20 spaces. > > Problem: Increases my code complexity a bit. > Advantage: Doesn't affect performance (noticeably). The DBA > doesn't need to be aware of the hack. > > > 3. Wait for MySQL to fix the strip trailing spaces bug. > > Problem: That doesn't provide an immediate solution. > > 4. Append a non-space at the end, and ignore it on retrieval > Problem: Same as 2. > Although 2. looks like the prettier solution, 4. makes easier to > spot the problem, if the additional handling is forgotten in new > code. > > Well, what you should do? It depends on what you need. It's a > trade-off and no one except you can answer what your priorities are. > > If, for example, you have many applications / programmers who > access this stuff, 1. is least intrusive. OTOH, if it is used > only in one place, perhaps in a well-encapsulated object, 2. > is the least intrusive change. And someone (that includes > yourself in 1 year) looking at your SQL dump wouldn't know > why you have chosen a BLOB, while you can have a neat comment > in the source about it. > > Since any of the solutions involves only minor changes, I > would not bother to waste time on the decision. Simply go > with one and rewrite if it really turns out to become a > problem later (which I don't believe). > > HTH, > > Benjamin. > > -- > [EMAIL PROTECTED] > > --------------------------------------------------------------------- > 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
