当前项目中用到了elfhash函数算出HASH值后再进行分表,
当需要在MYSQL里进行查询时比较麻烦,需要先算出HASH值才能确定用户数据被分到哪个表里,
于是把这个函数用SQL写成函数,方便使用。
CREATE DEFINER=`test`@`127.0.0.1` FUNCTION `elfhash`(strkey varchar(256)) RETURNS int(11) NO SQL BEGIN #Routine body goes here... /* unsigned long ELFHash(const char *key) { unsigned long h = 0; unsigned long x = 0; while (*key) { h = (h << 4) + (*key++); if ((x = h & 0xF0000000L) != 0) { h ^= (x >> 24); h &= ~x; } } return (h & 0x7FFFFFFF); } */ DECLARE h INT UNSIGNED DEFAULT 0; DECLARE x INT UNSIGNED DEFAULT 0; DECLARE k INT UNSIGNED DEFAULT 0; DECLARE strLen INT UNSIGNED DEFAULT 0; DECLARE pos INT UNSIGNED DEFAULT 0; SET strLen = LENGTH(strkey)+1; WHILE (pos<strLen) DO set k = ASCII(MID(strkey,pos,1)); set h = (h << 4) + k; SET x = h & 0xF0000000; IF (x!= 0) THEN set h = h^(x >> 24); set h = h&(~x); END IF; SET pos = pos +1; END WHILE; SET h = h & 0x7FFFFFFF; RETURN h; END
转载请注明:爱开源 » ELFHash SQL实现