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:

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;
CREATE FUNCTION random_alphanumeric
(len INT)
    DECLARE result VARCHAR(1200);
    DECLARE segment VARCHAR(700);
    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(
        #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;
    RETURN SUBSTRING(result,-1*len);
END //

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;
CREATE PROCEDURE test_random_insert
( IN int_value INT, IN varchar_value VARCHAR(45))
            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;

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!

Related Questions

Counting rows from a subquery

3  Asked on November 14, 2021 by rwcommand


How to get the registers in the highest time of period

1  Asked on November 14, 2021 by wilson-eduardo-millan-vargas


Mix ;WITH NAMESPACES and schema collections?

1  Asked on November 14, 2021 by user1664043


MariaDB tmpdir & table copy operations

1  Asked on November 11, 2021 by user120053


SAP Oracle Full Online Backup Failing

1  Asked on November 11, 2021 by plamenbv


SQL Server Replication

1  Asked on November 8, 2021 by derek-czarny


Performance MySQL query with for loop

1  Asked on November 8, 2021 by fabian-sierra


Ask a Question

Get help from others!

© 2022 All rights reserved. Sites we Love: PCI Database, MenuIva, UKBizDB, Menu Kuliner, Sharing RPP