Generating a random and unique varchar(n) in MySql?

Question

in my application i want to add to every record in a database table a code which must be unique and randomly generated (so unpredictable for users). I assume that generating this by code (java ee) is a bad idea because it will need to request frequently the database management system (MySQL) to check for the unicity. Can someone help me to generate this code by SQL like for a variable char (varchar) with a size n. Thanks for your help.

possible duplicate of [How to generate unique id in MySQL?](http://stackoverflow.com/questions/1467581/how-to-generate-unique-id-in-mysql). Also of interest: [Generating a random & unique 8 character string using MySQL](http://stackoverflow.com/a/16738409/1446005) — RandomSeed, Jun 19 '14 at 11:44

score 1 · Answer 1 · answered Dec 19 '11 at 17:42

1

UUID()

Will this work? Of course you would have to store the value of the UUID as your VARCHAR if that is what you wanted rather than the UUID type.

answered Dec 19 '11 at 17:42

Bueller

2,336
17
11

+1 - Using a UUID function is the only correct way to solve this problem. – TehShrike Dec 19 '11 at 18:13

score 0 · Answer 2 · answered Dec 19 '11 at 17:50

MySQL can verify the uniqueness of a value if you assign a Unique Index to that field. For instance, if you have a table with the columns name, age, code, and you can set a unique key to code and it will make sure that code is never duplicated.

That way you can generate a unique string/number/whatever in whatever language you want (e.g. Java EE) and then insert that string into the table. MySQL will reject an insertion if the key is already being used.

Here's a link to MySQL documentation on adding keys to the table: http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

Hope that helps.

Good luck.

Generating a random and unique varchar(n) in MySql?

2 Answers2