Damerau-Levenshtein Distance UDF for MySQL - Supports upper bounding for fast searching and UTF-8 case insensitive throught iconv.
OTHER License
Damerau-Levenshtein Distance UDF for MySQL Supports upper bounding for fast searching and UTF-8 case insensitive throught iconv.
Copyright (C) 2013 Diego Torres
Implementing
https://github.com/torvalds/linux/blob/8a72f3820c4d14b27ad5336aed00063a7a7f1bef/tools/perf/util/levenshtein.c
Redistribute as you wish, but leave this information intact.
Compiling
Run make. It will try to autodetect architecture and generate 32 or x86_64 bit
version. Enable testing with "make debug". An executable instead of
a library will be built. Execute it to run some tests regarding
memory allocation and levenshtein core calculations.
Errors you may get when compiling:
In file included from ./src/damlevlim.c:16:0: ./src/damlevlim.h:26:23: fatal error: my_global.h: No such file or directory #include <my_global.h> ^ compilation terminated. Makefile:30: recipe for target 'mysqldamlevlim' failed make: *** [mysqldamlevlim] Error 1
Development libraries from mysql are missing.
Do "sudo apt-get install libmysqlclient-dev"
/usr/include/gnu/stubs.h:7:27: fatal error: gnu/stubs-32.h: No such file or directory
You're missing the 32 bit libc dev package. This is answered here:
http://stackoverflow.com/questions/7412548/gnu-stubs-32-h-no-such-file-or-directory
- On Ubuntu it's called libc6-dev-i386 - do "sudo apt-get install"
libc6-dev-i386. See below for extra instructions for Ubuntu 12.04.
- On Red Hat distros, the package name is glibc-devel.i686 (Thanks to
David Gardner's comment)
- On CentOS 5.8, the package name is glibc-devel.i386 (Thanks to JimKleck's comment)
- On CentOS 6.3, the package name is glibc-devel.i686.
- On SLES it's called glibc-devel-32bit - do zypper in glibc-devel-32bit
Loading UDFs in MySQL STEP 1
Copy compiled udf to plugin dir. To know where exactly, ask MySQL:
mysql> SHOW VARIABLES LIKE 'plugin_dir';
and use result directory.
To modify plugin_dir to point to a custom location:
my.cnf: plugin_dir = /usr/lib/mysql/plugin
Don't forget to restart MySQL!!
Loading UDFs in MySQL STEP 2
Bind the library to an UDF inside MySQL (only do it once,
survives MySQL reboot). Depending your MySQL binaries (32
or 64 bits), insert the right library.
mysql> DROP FUNCTION IF EXISTS damlevlim;
mysql> CREATE FUNCTION damlevlim RETURNS INT SONAME 'mysqldamlevlim.so';
Check if UDF was loaded OK.
mysql> SELECT * FROM mysql.func;
+-----------+-----+-------------------+----------+
| name | ret | dl | type |
+-----------+-----+-------------------+----------+
| damlevlim | 2 | mysqldamlevlim.so | function |
+-----------+-----+-------------------+----------+
1 row in set (0.00 sec)
Using damlevlim
mysql> select damlevlim();
ERROR: DAMLEVLIM() requires three arguments (string, string, int)
First string is compared against second string. Compare only the first
int chars (upper bound).
mysql> select damlevlim('coche', 'çóche', 5);
+--------------------------------+
| damlevlim('coche', 'çòche', 5) |
+--------------------------------+
| 0 |
+--------------------------------+
1 row in set (0.00 sec)
Further reading about MySql, UTF8 and charset encoding
http://www.oreillynet.com/onlamp/blog/2006/01/turning_mysql_data_in_latin1_t.html
How to search for multibyte characters in a column
Use MySql CHAR_LENGTH function to find rows with multi-byte characters:
mysql> SELECT name FROM users WHERE LENGTH(name) != CHAR_LENGTH(name);
How to search for strange multibyte characters in a column
(don't remember source of original tip)
Use MySql HEX and PHP bin2hex functions
mysql> SELECT name, HEX(name) FROM users;
Get the result back into PHP, and run a bin2hex on the string, compare
it to MySQL’s hex of that same string.