A ‘Grrrr!’ moment

Imagine, if you will, you have a series of n dot separated strings, something like ‘foo.bar.bat.blah’, and you want to reduce it to the final term in the series, in this case ‘blah’. What does the SQL look like to that?

Seeing as there can be any number of strings before the last one, you end up doing something like this:

reverse(substring(reverse(col), 1, locate(‘.’, reverse(col)) – 1))

which: reverses the value of the column; locates the first ‘.’; creates a substring that contains everything up to the index before the ‘.’; then reverses the result. Cute, huh? Bleeeugh!

That’s when you find out that MySQL has a substring_index(str, delim, count) function that will do the same with:

substring_index(col, ‘.’, -1)



Things You Don’t Want To Do

So, you have to compile mysql to make sure that a crash has been fixed, otherwise you’re going to end up filing a bug… and we all know how much hassle that can be! Unfortunately you need a 64bit build (x86_64) on OS X… and you’ve never built mysql before.

This is what you need:

CFLAGS='-O3 -fno-common -arch x86_64' \
LDFLAGS='-O3 -arch x86_64' \
CXXFLAGS='-O3 -fno-common -arch x86_64' \
./configure -with-plugins=innobase
$ make
$ sudo make install
After that it’s all nice and easy. Up until then… not so much.