To overcome this one could set that variable before token generated i.e.: MySql supports different block encryption modes which if changed will completely change your token space making old tokens useless. This is good enough plus its reversable so you'd not have to store that token in your table but to generate it instead.Īnother advantage is once you decode your PK from that token you do not have to do heavy full text searches over your table but simple and quick PK search. SELECT HEX(AES_ENCRYPT(your_pk,'your_password')) AS 'token' FROM your_table To get unique and random looking tokens you could just encrypt your primary key i.e.: This will give you a 8 digit unique character everytime. SET COUNT(*) INTO FROM ',tableName,' WHERE ',columnName,' like ''',newUniqueValue,'''') Īnd call the stored procedure as GenerateUniqueValue('tableName','columnName'). This approach guarantees uniqueness and takes any processing load away from the insert transaction and into the batch routine, where it does not affect the user.ĭROP PROCEDURE IF EXISTS `GenerateUniqueValue`$$ĬREATE PROCEDURE `GenerateUniqueValue`(IN tableName VARCHAR(255),IN columnName VARCHAR(255))ĭECLARE uniqueValue VARCHAR(8) DEFAULT "" ĭECLARE newUniqueValue VARCHAR(8) DEFAULT "" (If I ever get 10000 orders in one day I'll have a problem - but that would be a good problem to have!) I have a routine that runs every night which pre-loads this table with enough unique_id rows to more than cover the orders that might be inserted in the next 24 hours. Then I have another table - unique_ids with (order_id, unique_id). The user does not need to see this internal id. This id is generated on the fly when the user enters the order, incrementally 1,2,3 etc forever. What I do is pre-generate unique id's for future use, that way I can take my own sweet time and absolutely guarantee they are unique, and there's no processing to be done at the time of the insert.įor example I have an orders table with order_id in it. My point is that you do not need to generate these unique id's at the time of creating your rows, because they are essentially independent of the data being inserted. How you generate the unique_ids is a useful question - but you seem to be making a counter productive assumption about when you generate them!
0 kommentar(er)
