TransWikia.com

How can I create a unique and random alphanumeric string using MySQL's Default Value? (v 5.6)

Database Administrators Asked by CuriousChad on November 7, 2020

I am building a MySQL database and think using implicit DEFAULTS seems very promising.

I am successfully using DEFAULT CURRENT_TIMESTAMP and DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP for on insert timestamps and on update timestamps.

I would like to add a default for a public key. A 32 character unique and random alphanumeric string. Is this possible using the DEFAULT field specification? If so, how?

Or must I handle this by creating a random value and checking it’s uniqueness, for the 1 in 1.5^50 chance it’s a duplicate, on INSERT?

This article seemed promising, but didn’t give me an definitive answers: https://www.percona.com/blog/2015/04/29/generated-virtual-columns-in-mysql-5-7-labs/

Thank you in advance

One Answer

I'm a bit late, but I've been having difficulty finding a way to insert cryptographically secure alphanumeric text, so here's what I've come up with.

I created a function to generate the strings and a stored procedure to insert data into the table. It uses INSERT IGNORE and checks ROW_COUNT() to see if the insert succeeded. I know INSERT IGNORE is not ideal, but this is the best I have so far.

DROP FUNCTION IF EXISTS random_alphanumeric;
DELIMITER //
CREATE FUNCTION random_alphanumeric
(len INT)
RETURNS VARCHAR(512)
BEGIN
    DECLARE result VARCHAR(1200);
    DECLARE segment VARCHAR(700);
    DECLARE l INT;
    IF len<0 THEN
        SIGNAL SQLSTATE '45000' SET message_text = 'Attempt to generate random alphanumeric with negative length.';
    END IF;
    IF len>512 THEN
        SIGNAL SQLSTATE '45000' SET message_text = 'Cannot generate random alphanumeric longer than 512 characters.';
    END IF;
    SET result='';
    WHILE LENGTH(result)<len DO
        SET segment=REPLACE(
            REPLACE(
                REPLACE(
                    REPLACE(
                        TO_BASE64(RANDOM_BYTES((len-LENGTH(result))+3)),'/',''
                    ),'=',''
                ),'+',''
            ),'n',''
        );
        #The last characters of a base 64 string aren't always well distributed
        #Trim a couple off
        SET l=LENGTH(segment)-3;
        IF l>0 THEN
            SET result=CONCAT(result,SUBSTRING(segment,1,l));
        END IF;
    END WHILE;
    RETURN SUBSTRING(result,-1*len);
END //
DELIMITER ;

CREATE TABLE `test_random` (
  `id` VARCHAR(32) NOT NULL,
  `int_col` INT,
  `varchar_col` VARCHAR(45),
  PRIMARY KEY (`id`));


DROP PROCEDURE IF EXISTS test_random_insert;
DELIMITER //
CREATE PROCEDURE test_random_insert
( IN int_value INT, IN varchar_value VARCHAR(45))
        BEGIN
            DECLARE id_value VARCHAR(32);
            DECLARE ok INT;
            SET ok=0;
            WHILE NOT ok DO
                SET id_value=random_alphanumeric(32);
                INSERT IGNORE INTO `test_random` (`id`,`int_col`,`varchar_col`) VALUES (id_value, int_value, varchar_value);
                SET ok=ROW_COUNT();
            END WHILE;
            SELECT id_value id;
        END//
DELIMITER ;

To insert a record CALL test_random_insert(100,'text value'); The returned recordset has an id column, since LAST_INSERT_ID() only applies to autoincrement values.

Answered by Jon Hulka on November 7, 2020

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP