Here’s a fun one: I was troubleshooting some text import code that takes a pipe delimited text file and stuffs it into a database, but only if there’s been an update to the given row. The thing was, it was updating a lot more rows than it should have - for example, running the job twice in a row on the same file (which should have made for 0 updates on the second run) came up with about 50% of the rows updated.
It turns out trailing whitespace is ignored in MySQL, but only in versions prior to 5.0.3:
VARCHAR values are not padded when they are stored. Handling of trailing spaces is version-dependent. As of MySQL 5.0.3, trailing spaces are retained when values are stored and retrieved, in conformance with standard SQL. Before MySQL 5.0.3, trailing spaces are removed from values when they are stored into a VARCHAR column; this means that the spaces also are absent from retrieved values.
Technorati Tags: MySQL
Post a Comment