MySQL. random string(alphabet + number)

MySQL / Mariadb 에서 내장함수를 사용하여 무작위로 문자열을 생성하는 방법에 대한 기록이다.

랜덤한 문자열을 주는 내장 함수 

16진수로 표현된 문자열을 준다. 

(랜덤 문자열을 생성하기 위해서 RAND()함수를 추가한다.)

*16진수로 표현된 문자열을 준다는 말은 모든 알파벳을 주지 않는다는 말이다. (0123456789abcdef)

• SHA1(RAND())   => 40자리 
• MD5(RAND())     => 32자리
• HEX(RAND()*0xFFFFFFFF)   => F의 갯수만큼
• UUID()      => 4개 하이픈(-) 포함 36자리
• 기타 다른 것들도 있다.

 

모든 알파벳과 숫자를 포함하는 랜덤 문자열을 생성하려면 어떻게 해야할까?

여러가지 방법이 있겠지만 그 중에 내장함수중에 TO_BASE64() 라는 것이 있다.

위의 생성된 16진수 표현의 랜덤문자열을 base64로 인코딩해준다.

예) select TO_BASE64(MD5(RAND()));

base64 인코딩은 영어 대/소문자, 숫자에 두가지 문자(일반적으로 +, /)를 사용하고 마지막에 자리수를 채워주기 위한 패딩문자(=)가 2개까지 추가될수 있다.

* 아직 16진수 문자열을 base64로 인코딩했을 때 +와 /가 나오는 경우는 보지 못했다.

  base64의 인코딩 방식을 봤을 때 16진수의 조합에서는 +와 /가 안나오는 것일 수도 있다. (이 부분은 추후에 검토)

만약을 위해 REGEXP_REPLACE()를 통해 +,/,=를 제거할 수 있다. 

예) select REGEXP_REPLACE(TO_BASE64(MD5(RAND())),'[+/=]','');

 

랜덤 문자열 사용법

다른 내장 함수와 조합하여 사용할 수 있다.

• UPPER() : 영어 대문자로 변환
• LOWER() : 영어 소문자로 변환
  
• SUBSTRING(랜덤문자열, 1+RAND()*10, 8) : 필요한 만큼 잘라내어 사용한다.

* UUID의 경우 uuid 버전에 따라 시간값을 사용하기 때문에 굉장히 비슷한 랜덤 문자열이 생성될수 있다. 

그 말은 base64 인코딩도 비슷한 부분이 생길수 있다는 말이다.(특히 비슷한 시간에 UUID를 생성했다면..)

=> 왠만하면 UUID는 사용하지 말자.

 

응용

1. 영어만 랜덤 생성 

  select REGEXP_REPLACE(TO_BASE64(MD5(RAND())),'[0-9+/=]','');
> 숫자, +,/,=  제거
> base64 인코딩시 영어가 대소문자 합쳐서 52자 이다. 숫자보다 영어가 나올 확률이 높기 때문에 이렇게 해도 필요한 만큼의 랜덤 영어를 생성할 수 있을 듯하다.

2. 숫자만 랜덤 생성

select floor(rand()*100000);
1의 방법에서 영어를 제거하면 충분한 숫자를 얻을 수 없다.
rand() 를 이용해 필요한만큼 랜덤 숫자를 생성한다.

 

기타

char() 함수를 이용해 필요한 길이만큼 반복해서 생성할 수도 있다.

예)
select  CHAR(
    round(rand()*25)+97,
    round(rand()*25)+97,
    round(rand()*25)+97) ;

CHAR(ROUND(RAND()*25)+97) : 영어 소문자

CHAR(ROUND(RAND()*25)+65) : 영어 대문자

CHAR(ROUND(RAND()*9)+48) : 숫자