views:

482

answers:

4

We are porting an app which formerly used Openbase 7 to now use MySQL 5.0.

OB 7 did have quite badly defined (i.e. undocumented) behavior regarding case-sensitivity. We only found this out now when trying the same queries with MySQL.

It appears that OB 7 treats lookups using "=" differently from those using "LIKE": If you have two values "a" and "A", and make a query with WHERE f="a", then it finds only the "a" field, not the "A" field. However, if you use LIKE instead of "=", then it finds both.

Our tests with MySQL showed that if we're using a non-binary collation (e.g. latin1), then both "=" and "LIKE" compare case-insensitively. However, to simulate OB's behavior, we need to get only "=" to be case-sensitive.

We're now trying to figure out how to deal with this in MySQL without having to add a lot of LOWER() function calls to all our queries (there are a lot!).

We have full control over the MySQL DB, meaning we can choose its collation mode as we like (our table names and unique indexes are not affected by the case sensitivity issues, fortunately).

Any suggestions how to simulate the OpenBase behaviour on MySQL with the least amount of code changes?

(I realize that a few smart regex replacements in our source code to add the LOWER calls might do the trick, but we'd rather find a different way)

A: 

These two articles talk about case sensitivity in mysql:

Both were early hits in this Google search:

Olie
Those docs are no news to me. I know how to use collation. I was trying to find out if there are ways to get '=' to work case-sensitive and LIKE to work case-insensitive without adding extra operators to the query (because that's how Openbase behaved)
Thomas Tempelmann
A: 

I know that this is not the answer you are looking for .. but given that you want to keep this behaviour, shouldn't you explicitly code it (rather than changing some magic 'config' somewhere)?

It's probably quite some work, but at least you'd know which areas of your code are affected.

IronGoofy
The app is an old big beast which I do not fully understand, that's why I'm looking for an easier solution to emulate the old DB's behavior.
Thomas Tempelmann
I understand, but if you have to delve into the app anyways, this would be a good chance to expand you knowledge.
IronGoofy
+1  A: 

Another idea .. does MySQL offer something like User Defined Functions? You could then write a UDF-version of like that is case insesitive (ci_like or so) and change all like's to ci_like. Probably easier to do than regexing a call to lower in ..

IronGoofy
I have no experience with UDF. Might look into this eventually, though.
Thomas Tempelmann
A: 

A quick look at the MySQL docs seems to indicate that this is exactly how MySQL does it:

This means that if you search with col_name LIKE 'a%', you get all column values that start with A or a.

Cd-MaN
Our tests showed that if we're using a non-binary collation (e.g. latin1), then both "=" and "LIKE" compare case-insensitively. However, to simulate OB's behavior, we need to get "=" to be case-sensitive.
Thomas Tempelmann