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
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
1 Asked on November 14, 2021
1 Asked on November 14, 2021 by mag-musik
1 Asked on November 14, 2021 by ziimakc
1 Asked on November 14, 2021
1 Asked on November 14, 2021 by wilson-eduardo-millan-vargas
1 Asked on November 14, 2021 by user1664043
4 Asked on November 14, 2021 by sharvari
3 Asked on November 13, 2021 by saravana-kumar
1 Asked on November 11, 2021 by plamenbv
1 Asked on November 11, 2021 by palash-jadhav
3 Asked on November 11, 2021
1 Asked on November 11, 2021
1 Asked on November 11, 2021 by alf47
1 Asked on November 11, 2021 by chad-kieffer
1 Asked on November 8, 2021
1 Asked on November 8, 2021 by derek-czarny
1 Asked on November 8, 2021 by lenniey
Get help from others!