Jun 9, 2015

Generate random utf8 string in MySQL

Generate random utf8 string in mysql :
-- please note that probably it isn't fully correct, because char_length() doesn't show expected number
-- additionally - performance may be not acceptable at all for heavy tests
-- but it was useful enough for my purposes
-- use like this : select gen_utf8(128); (up to 2000 characters - increase in code if needed)

set names utf8;
drop function if exists gen_utf8char;
drop function if exists gen_utf8;

delimiter //

create function gen_utf8char()
returns char(1) character set utf8
declare res char(3) character set binary;

case ceil(rand()*3)
when 1 then return char(floor(rand()*127));
when 2 then return char(b'11000000' | 2 + floor(14*rand()), b'10000000' | floor(32*rand()));
else        return char(b'11100000' | 1 + floor( 7*rand()), b'10000000' | floor(32*rand()), b'10000000' | floor(32*rand()));
end case;

create function gen_utf8(n int)
returns varchar(2000) character set utf8

declare res varchar(2000) character set utf8 default "";

label1: LOOP
set res = concat(res, gen_utf8char());

    IF n > 1 THEN
      SET n = n - 1;
      ITERATE label1;
    END IF;

    LEAVE label1;
END LOOP label1;

return res;

delimiter ;

No comments:

Post a Comment