使用 MySQL 实现 Java 版的 hashCode 函数

前提

MySQL 数据库的编码需要设置为 utf8 或 utf8mb4，因为下面给出的代码是以用 utf8 编码储存数据为前提的。

MySQL Function

drop function if exists utf8_unicode;
create function utf8_unicode(str varchar(1)) returns bigint
begin
    declare num_utf8 bigint default ord(str);
    declare bit8 int default num_utf8 & 0xff;
    declare num_unicode bigint default 0;
    declare byte_count int default 0;
    while bit8 >> 6 = 2 do
        set num_unicode = num_unicode | ((bit8 & 0x3f) << (byte_count * 6));
        set byte_count = byte_count + 1;
        # 超出 utf8 编码范围
        if byte_count >= 6 then
            return null;
        end if;
        set num_utf8 = num_utf8 >> 8;
        set bit8 = num_utf8 & 0xff;
    end while;
    if byte_count = 0 and bit8 & 0x80 = 0 then
        return num_utf8;
    elseif bit8 >> (6 - byte_count) = (1 << (byte_count + 2)) - 2 then
        set num_unicode = num_unicode | ((bit8 & ((1 << (6 - byte_count)) - 1)) << (byte_count * 6));
        return num_unicode;
    end if;
    return null;
end;

drop function if exists java_hash;
create function java_hash(str varchar(128)) returns bigint
begin
    declare _pos int default 1;
    declare _hash bigint default 0;
    declare _size int default char_length(str);
    while _pos <= _size do
        set _hash = _hash * 31 + utf8_unicode(substring(str, _pos, 1));
        set _pos = _pos + 1;
    end while;
    return _hash;
end;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

说明

总共就两个函数，一个用于将 utf8 编码转为 unicode；一个使用 unicode 计算 Java 版的 hash 值。

附

最后附上查找以 hash 模式分表的数据的存储过程。例如 shop_book 表，按 hash 分成 16 个表，实际的表名则是：shop_book_0, shop_book_1, shop_book_2, ... , shop_book_15。

create
    definer = sa@`%` procedure find_sharding_tables(IN t_name varchar(100), IN t_where varchar(1000))
proc: begin
    # 1. 找出分表中有 book_id = '62f0c54172eba41decbab9f5' 这条数据的表序号
    #     call find_sharding_tables('shop_book', 'book_id="62f0c54172eba41decbab9f5"');
    #     RESULT: 8,
    # 2. 找出分表中储存了数据的表序号
    #     call find_sharding_tables('shop_book', null);
    #     RESULT: 0,8,15,
    declare res varchar(255) default '';
    declare i int default 0;
    declare sd_t_name varchar(100);
    declare sd_t_where varchar(1000) default '';
    if t_name is null or length(trim(t_name)) < 1 then
        leave proc;
    elseif t_name regexp '^[.\\w]+_\\d*$' then
        set t_name = left(t_name, length(t_name) - locate('_', reverse(t_name)));
    end if;
    if t_where is not null and length(trim(t_where)) > 0 then
        set sd_t_where = concat(' where ', if(locate('''', t_where) > 0, t_where, replace(t_where, '"', '''')));
    end if;
    lp: loop
        set sd_t_name = concat(t_name, '_', i);
        select count(1) into @existed from information_schema.TABLES where (TABLE_SCHEMA = database() and TABLE_NAME = sd_t_name) or concat(TABLE_SCHEMA, '.', TABLE_NAME) = sd_t_name;
        if @existed <= 0 then
            leave lp;
        end if;
        set @sql = concat('select count(1) into @found from ', sd_t_name, sd_t_where);
        prepare stmt from @sql;
        execute stmt;
        deallocate prepare stmt;
        if @found > 0 then
            set res = concat(res, i, ',');
        end if;
        set i = i + 1;
    end loop;
    select if(length(trim(res)) < 1, if(i != 0, 'NOT FOUND', 'NO SUCH TABLE'), res) as RESULT;
end;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

使用方式：

# 1. 找出分表中有 book_id = '62f0c54172eba41decbab9f5' 这条数据的表序号
call find_sharding_tables('shop_book', 'book_id="62f0c54172eba41decbab9f5"');

# 2. 找出分表中储存了数据的表序号
call find_sharding_tables('shop_book', null);

# 3. 支持双单引号
call find_sharding_tables('shop_book', 'book_id=''62f0c54172eba41decbab9f5''');

# 4. 支持多条件
call find_sharding_tables('shop_book', 'book_id="62f0c54172eba41decbab9f5" and (deleted is null or deleted = 0)');

# 5. 支持忽略表序号
call find_sharding_tables('shop_book_3', 'book_id="62f0c54172eba41decbab9f5"');

# 6. 支持指定库名
call find_sharding_tables('test_db_2.shop_book', 'book_id="62f0c54172eba41decbab9f5"');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17