0

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

  • 3
    Just use UUID(). – Rick James Jun 29 '19 at 6:07
  • Thanks for the answer. However, it doesn't work as the DEFAULT attribute in a table definition and it's not perfect for a public key, because it's predictable. – CuriousChad Jun 30 '19 at 17:48
  • Some parts of UUID() are predictable, but the whole is not. – Rick James Jun 30 '19 at 21:55
  • Oh, I wonder what MySQL.com means then... Warning: Although UUID() values are intended to be unique, they are not necessarily unguessable or unpredictable. If unpredictability is required, UUID values should be generated some other way. from: dev.mysql.com/doc/refman/5.6/en/… – CuriousChad Jul 1 '19 at 22:11
  • The UUID has several parts to it: the machine id (probably "mac address"), the time (to high resolution, so not easily predictable), and other stuff. It is _designed to be world-wide unique. – Rick James Jul 2 '19 at 2:55
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for?Browse other questions tagged or ask your own question.