SET = (SELECT id FROM `Vehicle` WHERE `plate` = NEW.plate)
#Random data generator sql code#
If we want to keep the collision checking code out of the app, we can create a trigger: DELIMITER $$ Instead you'll need to generate a string, check if it's already in use, and try again if it is.Ĭheck if the random string is already in use You can customize the isn't guaranteed - as you'll see in the comments to other solutions, this just isn't possible. Usage SELECT RANDSTRING(8) to return an 8 character string. SET = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789' DELIMITER $$ĬREATE FUNCTION `RandString`(length SMALLINT(3)) RETURNS varchar(100) CHARSET utf8 Here's a MySQL function to create a random string of a given length. Substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', 1) Assumin id is an AUTO_INCREMENT column: INSERT INTO vehicles VALUES (blah) - leaving out the number plate So we use or approach, but with a seeded RAND:Į.g. But there is a nice one: RAND(N) itself!Ī sequence of random numbers created by the same seed is guaranteed to be.Hash has been suggested by AES-encrypt fits also.So using a uniqueness-preserving, pseudo-random transformation would be fine: Non-random uniqueness can trivially be achieved with AUTO_INCREMENT. This brings us to concentrate on the uniqueness first. While randomness is quite easily achieved, the uniqueness without a retry loop is not.
This problem consists of two very different sub-problems: I've been able to generate strings by defining a string containing all the allowed chars and randomly substringing it, and nothing more. I thought of the generate&check loop approach again, but I'm not limiting this question to that just in case there's a more efficient one. The query I need should simply generate a 8-character alphanumeric string which is not already in the table. Therefore, I decided to try and solve this issue using a MySQL query. However, as the number of vehicles increases, I expect this to become even more inefficient it is right now. How I achieved this was using a while loop in Lua, which is the language I'm programming in, to generate strings and query the DB to see if it is used.
#Random data generator sql license#
I need to find an unused license plate before creating a new vehicle - it should be an alphanumeric 8-char random string. Now here comes the part I'm having problems with. I have a MySQL table named "vehicles" containing the data about the vehicles, including the column "plate" which stores the License Plates for the vehicles. I'm working on a game which involves vehicles at some point.
0 kommentar(er)
