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
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
Get help from others!
Recent Questions
Recent Answers
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP