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
