分享SinoDB生成随机数方法

[zuoyou]

1、前言

  在数据库运维中，有时需要创建测试表并将随机数据加载到数据库中。本文将介绍我使用的生成随机数据的方法。

2、shell脚本方式

  以下shell脚本，生成5行，每行包含30个字段的随机数，每个字段长度的长度为20，数据保存在当前目录下的random.text。

#!/bin/bash
characters=("A" "B" "C" "D" "E" "F" "G" "H" "I" "J" "K" "L" "M" "N" "O" "P" "Q" "R" "S" "T" "U" "V" "W" "X" "Y" "Z" "a" "b" "c" "d" "e" "f" "g" "h" "i" "j" "k" "l" "m" "n" "o" "p" "q" "r" "s" "t" "u" "v" "w" "x" "y" "z" "0" "1" "2" "3" "4" "5" "6" "7" "8" "9")


generate_random_string() {
  local length="$1"
  local result=""
  for ((i=1; i<=length; i++)); do
    random_character=${characters[$((RANDOM % ${#characters[@]}))]}
    result="$result$random_character"
  done
  echo "$result"
}

for ((line=1; line<=5; line++)); do
  for ((i=1; i<=30; i++)); do
    random_field=$(generate_random_string 20)
    echo -n "$random_field " >>random.text
  done
  echo  >>random.text
done

3、SinoDB DBMS_RANDOM函数方式

  数据库中sinodb注册excompat.1.0 模块(需要日志模式，且必须为非ANSI）

范例：dbms_random_random生成是整数型随机数

dbms_random_random是无法创建字符串功能，但可以生成随机数，然后创建函数转换为字符串，这是一个使用dbs_random_random()函数生成ASCII(包含字母数字字符)字符串的用户定义函数的sql：

create function random_string()
returning varchar(10)
define s varchar(10);
define i, n int;
let s ="";

for i = 1 to 10
  let n = mod(abs(dbms_random_random()), 62);
  if (n < 10)
  then
    let n = n + 48;
  elif (n < 36)
  then
    let n = n + 55;
  else
    let n = n + 61;
  end if
  let s = s || chr(n);
end for

return s;
end function

[chenzy]

这里分享另一个random string 函数，是可以兼容Oracle DBMS_RANDOM.STRING function.

drop function dbms_random_string;
create function dbms_random_string(arg1 char,arg2 integer) returning lvarchar

define alpha_mixed varchar(52);
define lower_only varchar(26);
define upper_only varchar(26);
define upper_alphanum varchar(36);
define printable varchar(98);
define opt char(1);
define charset varchar(99);
define chrset_size integer;
define i, seed, pos integer;
define str varchar(99);

let alpha_mixed=“abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ”;
let lower_only=“abcdefghijklmnopqrstuvwxyz”;
let upper_only=“ABCDEFGHIJKLMNOPQRSTUVWXYZ”;
let upper_alphanum=“0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ”;
let printable=“`1234567890-=qwertyuiopasdfghjkl;'zxcvbnm,./!@#$%^&*()_+QWERTYUIOP{}ASDFGHJKL:|ZXCVVBNM<>?~”;

let opt=arg1;
case opt
when ‘a’ then
begin
let charset = alpha_mixed;
let chrset_size = len(alpha_mixed);
end;
when ‘A’ then
begin
let charset = alpha_mixed;
let chrset_size = len(alpha_mixed);
end;
when ‘l’ then
begin
let charset = lower_only;
let chrset_size = len(lower_only);
end;
when ‘L’ then
begin
let charset = lower_only;
let chrset_size = len(lower_only);
end;
when ‘u’ then
begin
let charset = upper_only;
let chrset_size = len(upper_only);
end;
when ‘U’ then
begin
let charset = upper_only;
let chrset_size = len(upper_only);
end;
when ‘x’ then
begin
let charset = upper_alphanum;
let chrset_size = len(upper_alphanum);
end;
when ‘X’ then
begin
let charset = upper_alphanum;
let chrset_size = len(upper_alphanum);
end;
when ‘p’ then
begin
let charset = printable;
let chrset_size = len(printable);
end;
when ‘P’ then
begin
let charset = printable;
let chrset_size = len(printable);
end;
else
begin
let charset = NULL;
let chrset_size = 0;
end;
end case;

let str=“”;
for i in (1 to arg2) loop
let pos=mod(abs(dbms_random_random()), chrset_size);
let str=concat(str,substr(charset,pos,1));
exit when i=arg2;
end loop;

return str;

end function;

[zuoyou]