program story

MySQL을 사용하여 임의의 고유 한 8 자 문자열 생성

inputbox 2020. 8. 29. 11:01
반응형

MySQL을 사용하여 임의의 고유 한 8 자 문자열 생성

나는 어느 시점에서 차량과 관련된 게임을 작업하고 있습니다. 차량에 대한 번호판을 저장하는 "plate"열을 포함하여 차량에 대한 데이터를 포함하는 "vehicles"라는 MySQL 테이블이 있습니다.

이제 제가 문제가있는 부분이 있습니다. 새 차량을 만들기 전에 사용하지 않은 번호판을 찾아야합니다. 영숫자 8 자로 된 임의의 문자열이어야합니다. 내가 이것을 달성 한 방법은 내가 프로그래밍하는 언어 인 Lua에서 while 루프를 사용하여 문자열을 생성하고 DB를 쿼리하여 사용 여부를 확인하는 것이 었습니다. 하지만 차량 수가 늘어남에 따라 지금보다 훨씬 비효율적 일 것으로 예상됩니다. 따라서 MySQL 쿼리를 사용하여이 문제를 해결하기로 결정했습니다.

필요한 쿼리는 테이블에 아직없는 8 자 영숫자 문자열을 생성해야합니다. 다시 생성 및 확인 루프 접근 방식을 생각했지만 더 효율적인 질문이있는 경우에만이 질문을 제한하지 않습니다. 허용 된 모든 문자를 포함하는 문자열을 정의하고 임의로 하위 문자열을 지정하여 문자열을 생성 할 수있었습니다.

도움을 주시면 감사하겠습니다.

이 문제는 매우 다른 두 가지 하위 문제로 구성됩니다.

  • 문자열은 겉보기에는 무작위로 보여야합니다.
  • 문자열은 고유해야합니다.

임의성은 매우 쉽게 얻을 수 있지만 재시도 루프가없는 고유성은 그렇지 않습니다. 이것은 우리가 먼저 독창성에 집중하게합니다. 무작위가 아닌 고유성은 AUTO_INCREMENT. 따라서 고유성을 유지하는 의사 랜덤 변환을 사용하는 것이 좋습니다.

  • @paul이 해시를 제안했습니다.
  • AES 암호화도 적합합니다.
  • 그러나 좋은 것이 있습니다 : RAND(N)그 자체입니다!

동일한 시드로 생성 된 일련의 난수는

  • 재생할 수 있는
  • 처음 8 회 반복에 대해 다름
  • 씨앗이 INT32

그래서 우리는 @AndreyVolk 또는 @GordonLinoff의 접근 방식을 사용하지만 시드가 있습니다 RAND .

예를 들어 Assumin id은 AUTO_INCREMENT열입니다.

INSERT INTO vehicles VALUES (blah); -- leaving out the number plate
SELECT @lid:=LAST_INSERT_ID();
UPDATE vehicles SET numberplate=concat(
  substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@lid)*4294967296))*36+1, 1),
  substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
  substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
  substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
  substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
  substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
  substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed:=round(rand(@seed)*4294967296))*36+1, 1),
  substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand(@seed)*36+1, 1)
)
WHERE id=@lid;

내 의견에서 언급했듯이 충돌 가능성에 대해서는 신경 쓰지 않습니다. 임의의 문자열을 생성하고 존재하는지 확인하십시오. 만약 그렇다면, 다시 시도하세요. 이미 많은 수의 플레이트가 할당되어 있지 않는 한 두 번 더 할 필요가 없습니다.

순수 (My) SQL에서 8 자 길이의 의사 랜덤 문자열을 생성하는 또 다른 솔루션 :

SELECT LEFT(UUID(), 8);

다음 (의사 코드)을 시도 할 수 있습니다.

DO 
    SELECT LEFT(UUID(), 8) INTO @plate;
    INSERT INTO plates (@plate);
WHILE there_is_a_unique_constraint_violation
-- @plate is your newly assigned plate number

이 게시물이 예상치 못한 수준의 관심을 받았기 때문에 ADTC의 의견을 강조 하겠습니다 . 위의 코드는 상당히 멍청하고 연속적인 숫자를 생성합니다.

약간 덜 어리석은 무작위성을 위해 대신 다음과 같이 시도하십시오.

SELECT LEFT(MD5(RAND()), 8)

그리고 진정한 (암호 화학적으로 안전한) 임의성을 위해 RANDOM_BYTES()대신 사용 RAND()하십시오.

순차 정수의 MD5 (또는 기타) 해시를 계산 한 다음 처음 8자를 사용하는 것은 어떻습니까?

즉 

MD5(1) = c4ca4238a0b923820dcc509a6f75849b => c4ca4238
MD5(2) = c81e728d9d4c2f636f067f89cc14862c => c81e728d
MD5(3) = eccbc87e4b5ce2fe28308fd9f2a7baf3 => eccbc87e

기타

주의 : 충돌 전에 얼마나 많은 것을 할당 할 수 있는지 모르겠습니다 (하지만 알고 있고 일정한 값이 될 것입니다).

편집 : 이것은 이제 오래된 대답이지만 시간이 지남에 따라 다시 보았으므로 관찰에서 ...

모든 숫자의 확률 = 2.35 %

모든 글자의 확률 = 0.05 %

MD5 (82945) = "7b763dcb ..."일 때 첫 번째 충돌 (MD5 (25302)과 동일한 결과)

임의의 문자열 만들기

다음은 주어진 길이의 임의의 문자열을 생성하는 MySQL 함수입니다.

DELIMITER $$

CREATE DEFINER=`root`@`%` FUNCTION `RandString`(length SMALLINT(3)) RETURNS varchar(100) CHARSET utf8
begin
    SET @returnStr = '';
    SET @allowedChars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
    SET @i = 0;

    WHILE (@i < length) DO
        SET @returnStr = CONCAT(@returnStr, substring(@allowedChars, FLOOR(RAND() * LENGTH(@allowedChars) + 1), 1));
        SET @i = @i + 1;
    END WHILE;

    RETURN @returnStr;
END

SELECT RANDSTRING(8)8 자 문자열을 반환하는 데 사용 됩니다.

당신은 사용자 정의 할 수 있습니다 @allowedChars.

고유성이 보장되지는 않습니다. 다른 솔루션에 대한 의견에서 볼 수 있듯이 이것은 불가능합니다. 대신 문자열을 생성하고 이미 사용 중인지 확인한 다음 사용중인 경우 다시 시도해야합니다.

임의의 문자열이 이미 사용 중인지 확인하십시오.

앱에서 충돌 검사 코드를 유지하려면 트리거를 만들 수 있습니다.

DELIMITER $$

CREATE TRIGGER Vehicle_beforeInsert
  BEFORE INSERT ON `Vehicle`
  FOR EACH ROW
  BEGIN
    SET @vehicleId = 1;
    WHILE (@vehicleId IS NOT NULL) DO 
      SET NEW.plate = RANDSTRING(8);
      SET @vehicleId = (SELECT id FROM `Vehicle` WHERE `plate` = NEW.plate);
    END WHILE;
  END;$$
DELIMITER ;

다음은 영숫자를 유효한 문자로 사용하는 한 가지 방법입니다.

select concat(substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand()*36+1, 1),
              substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand()*36+1, 1),
              substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand()*36+1, 1),
              substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand()*36+1, 1),
              substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand()*36+1, 1),
              substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand()*36+1, 1),
              substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand()*36+1, 1),
              substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', rand()*36+1, 1)
             ) as LicensePlaceNumber;

고유성을 보장하지 않습니다. 별도로 확인해야합니다.

다음은 임의의 문자열을 생성하는 또 다른 방법입니다.

SELECT SUBSTRING(MD5(RAND()) FROM 1 FOR 8) AS myrandomstring

MySQL의 rand () 및 char () 함수를 사용할 수 있습니다 .

select concat( 
    char(round(rand()*25)+97),
    char(round(rand()*25)+97),
    char(round(rand()*25)+97),
    char(round(rand()*25)+97),
    char(round(rand()*25)+97),
    char(round(rand()*25)+97),
    char(round(rand()*25)+97),
    char(round(rand()*25)+97)
) as name;

다음을 사용하여 임의의 영숫자 문자열을 생성 할 수 있습니다.

lpad(conv(floor(rand()*pow(36,8)), 10, 36), 8, 0);

BEFORE INSERT트리거에서 사용하고 while 루프에서 중복을 확인할 수 있습니다 .

CREATE TABLE `vehicles` (
    `plate` CHAR(8) NULL DEFAULT NULL,
    `data` VARCHAR(50) NOT NULL,
    UNIQUE INDEX `plate` (`plate`)
);

DELIMITER //
CREATE TRIGGER `vehicles_before_insert` BEFORE INSERT ON `vehicles`
FOR EACH ROW BEGIN

    declare str_len int default 8;
    declare ready int default 0;
    declare rnd_str text;
    while not ready do
        set rnd_str := lpad(conv(floor(rand()*pow(36,str_len)), 10, 36), str_len, 0);
        if not exists (select * from vehicles where plate = rnd_str) then
            set new.plate = rnd_str;
            set ready := 1;
        end if;
    end while;

END//
DELIMITER ;

이제 다음과 같이 데이터를 삽입하십시오.

insert into vehicles(col1, col2) values ('value1', 'value2');

그리고 트리거는 plate열에 대한 값을 생성합니다 .

( sqlfiddle 데모 )

열이 NULL을 허용하는 경우 이러한 방식으로 작동합니다. NOT NULL이되도록하려면 기본값을 정의해야합니다.

`plate` CHAR(8) NOT NULL DEFAULT 'default',

대문자 영숫자가 원하는 것이 아닌 경우 트리거에서 다른 임의의 문자열 생성 알고리즘을 사용할 수도 있습니다. 그러나 방아쇠는 고유성을 처리합니다.

임의의 문자열을 생성하려면 다음을 사용할 수 있습니다.

SUBSTRING(MD5(RAND()) FROM 1 FOR 8)

당신은 다음과 같은 것을받습니다.

353E50CC

다른 열의 데이터를 사용하여 "해시"또는 고유 한 문자열을 생성합니다.

UPDATE table_name SET column_name = Right( MD5(another_column_with_data), 8 )

8 letters from the alphabet - All caps:

UPDATE `tablename` SET `tablename`.`randomstring`= concat(CHAR(FLOOR(65 + (RAND() * 25))),CHAR(FLOOR(65 + (RAND() * 25))),CHAR(FLOOR(65 + (RAND() * 25))),CHAR(FLOOR(65 + (RAND() * 25)))CHAR(FLOOR(65 + (RAND() * 25))),CHAR(FLOOR(65 + (RAND() * 25))),CHAR(FLOOR(65 + (RAND() * 25))),CHAR(FLOOR(65 + (RAND() * 25))));

For a String consisting of 8 random numbers and upper- and lowercase letters, this is my solution:

LPAD(LEFT(REPLACE(REPLACE(REPLACE(TO_BASE64(UNHEX(MD5(RAND()))), "/", ""), "+", ""), "=", ""), 8), 8, 0)

Explained from inside out:

  1. RAND generates a random number between 0 and 1
  2. MD5 calculates the MD5 sum of (1), 32 characters from a-f and 0-9
  3. UNHEX translates (2) into 16 bytes with values from 00 to FF
  4. TO_BASE64 encodes (3) as base64, 22 characters from a-z and A-Z and 0-9 plus "/" and "+", followed by two "="
  5. the three REPLACEs remove the "/", "+" and "=" characters from (4)
  6. LEFT takes the first 8 characters from (5), change 8 to something else if you need more or less characters in your random string
  7. LPAD inserts zeroes at the beginning of (6) if it is less than 8 characters long; again, change 8 to something else if needed

If you dont have a id or seed, like its its for a values list in insert:

REPLACE(RAND(), '.', '')

If you're OK with "random" but entirely predictable license plates, you can use a linear-feedback shift register to choose the next plate number - it's guaranteed to go through every number before repeating. However, without some complex math, you won't be able to go through every 8 character alphanumeric string (you'll get 2^41 out of the 36^8 (78%) possible plates). To make this fill your space better, you could exclude a letter from the plates (maybe O), giving you 97%.

Taking into account the total number of characters that you require, you would have a very small chance of generating two exactly similar number plates. Thus you could probably get away with generating the numbers in LUA.

You have 36^8 different unique numberplates (2,821,109,907,456, that's a lot), even if you already had a million numberplates already, you'd have a very small chance of generating one you already have, about 0.000035%

Of course, it all depends on how many numberplates you will end up creating.

This function generates a Random string based on your input length and allowed characters like this:

SELECT str_rand(8, '23456789abcdefghijkmnpqrstuvwxyz');

function code: 

DROP FUNCTION IF EXISTS str_rand;

DELIMITER //

CREATE FUNCTION str_rand(
    u_count INT UNSIGNED,
    v_chars TEXT
)
RETURNS TEXT
NOT DETERMINISTIC
NO SQL
SQL SECURITY INVOKER
COMMENT ''
BEGIN
    DECLARE v_retval TEXT DEFAULT '';
    DECLARE u_pos    INT UNSIGNED;
    DECLARE u        INT UNSIGNED;

    SET u = LENGTH(v_chars);
    WHILE u_count > 0
    DO
      SET u_pos = 1 + FLOOR(RAND() * u);
      SET v_retval = CONCAT(v_retval, MID(v_chars, u_pos, 1));
      SET u_count = u_count - 1;
    END WHILE;

    RETURN v_retval;
END;
//
DELIMITER ;

This code is based on shuffle string function sends by "Ross Smith II"

DELIMITER $$

USE `temp` $$

DROP PROCEDURE IF EXISTS `GenerateUniqueValue`$$

CREATE PROCEDURE `GenerateUniqueValue`(IN tableName VARCHAR(255),IN columnName VARCHAR(255)) 
BEGIN
    DECLARE uniqueValue VARCHAR(8) DEFAULT "";
    WHILE LENGTH(uniqueValue) = 0 DO
        SELECT CONCAT(SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789', RAND()*34+1, 1),
                SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789', RAND()*34+1, 1),
                SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789', RAND()*34+1, 1),
                SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789', RAND()*34+1, 1),
                SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789', RAND()*34+1, 1),
                SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789', RAND()*34+1, 1),
                SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789', RAND()*34+1, 1),
                SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789', RAND()*34+1, 1)
                ) INTO @newUniqueValue;
        SET @rcount = -1;
        SET @query=CONCAT('SELECT COUNT(*) INTO @rcount FROM  ',tableName,' WHERE ',columnName,'  like ''',@newUniqueValue,'''');
        PREPARE stmt FROM  @query;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    IF @rcount = 0 THEN
            SET uniqueValue = @newUniqueValue ;
        END IF ;
    END WHILE ;
    SELECT uniqueValue;
    END$$

DELIMITER ;

Use this stored procedure and use it everytime like

Call GenerateUniqueValue('tableName','columnName')

An easy way that generate a unique number 

set @i = 0;
update vehicles set plate = CONCAT(@i:=@i+1, ROUND(RAND() * 1000)) 
order by rand();

Generate 8 characters key 

lpad(conv(floor(rand()*pow(36,6)), 10, 36), 8, 0);

How do I generate a unique, random string for one of my MySql table columns?

I was looking for something similar and I decided to make my own version where you can also specify a different seed if wanted (list of characters) as parameter:

CREATE FUNCTION `random_string`(length SMALLINT(3), seed VARCHAR(255)) RETURNS varchar(255) CHARSET utf8
    NO SQL
BEGIN
    SET @output = '';

    IF seed IS NULL OR seed = '' THEN SET seed = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789'; END IF;

    SET @rnd_multiplier = LENGTH(seed);

    WHILE LENGTH(@output) < length DO
        # Select random character and add to output
        SET @output = CONCAT(@output, SUBSTRING(seed, RAND() * (@rnd_multiplier + 1), 1));
    END WHILE;

    RETURN @output;
END

Can be used as:

SELECT random_string(10, '')

Which would use the built-in seed of upper- and lowercase characters + digits. NULL would also be value instead of ''.

But one could specify a custom seed while calling:

SELECT random_string(10, '1234')

참고URL : https://stackoverflow.com/questions/16737910/generating-a-random-unique-8-character-string-using-mysql

반응형

'program story' 카테고리의 다른 글

문서 Main.storyboard에는 Xcode 8.0 이상이 필요합니다.  (0) 2020.08.29
.NET은 바이트를 KB, MB, GB 등으로 쉽게 변환하는 방법을 제공합니까?  (0) 2020.08.29
CameraUpdateFactory.newLatLngBounds가있는 moveCamera가 다운 됨  (0) 2020.08.29
부트 스트랩 팝 오버 내에 양식이 포함되어 있습니까?  (0) 2020.08.29
Fragment ActionBarCompat 내부의 getSupportActionBar  (0) 2020.08.29

'program story'의 다른글

  • 현재글MySQL을 사용하여 임의의 고유 한 8 자 문자열 생성

관련글

댓글

티스토리툴바