How to make more than 6 characters random alphanumeric by this code in trigger of MySQL?

144
August 27, 2017, at 06:30 AM

I have a form for request registering by php and html. I found a code to make 6 character random string for tracking code and should not be duplicate.this code is in triggers for users table in mySql before inserting new row.

How can I make more than 6 characters random alphanumeric by this code in trigger of MySQL?

And in your opinion is this code useful or is better than a code for random string in php??

Whats your suggestion?

BEGIN
  declare ready int default 0;
  declare rnd_str text;
  while not ready DO set rnd_str := LOWER(lpad(conv(floor(rand()*pow(36,6)),10, 36), 6, 0));
     if not exists (select * from stu where st_trc = rnd_str)
     then
       set new.trck= rnd_str;
       set ready := 1;
     end if;
   end while;
END
Answer 1

Getting a 12 character pseudorandom string is as easy as concatenating two six-character pseudorandom strings.

CONCAT(LOWER(lpad(conv(floor(rand()*pow(36,6)),10, 36), 6, 0)),
       LOWER(lpad(conv(floor(rand()*pow(36,6)),10, 36), 6, 0)))

I guess you can figure out how to generate 18 or 24 random characters. :-)

In terms of cryptographic security, neither this nor a php string generator based on rand() qualify: These are pseudorandom numbers, not real random numbers, and a stubborn attacker has an unfair advantage trying to guess them. MySQL doesn't have a way to generate cryptographically secure random numbers. PHP 7 does: http://php.net/manual/en/function.random-bytes.php

(Cryptographically secure random numbers are numbers that are as hard as possible for an attacker to guess. Pseudorandom number generators use patterns, and true random numbers don't have any pattern. Therefore, pseudorandom numbers are easier to guess. What harm could it do to your business if a cybercriminal guessed one of your tracking codes? Only you know that. If guessing a tracking number can harm you, use more secure random numbers.)

In terms of convenience, generating these random strings either in php or MySQL is just fine. If the relevance of your random strings is short-lived (a week or two, for example) this approach will work sufficiently well.

READ ALSO
Mysql Workbench bug when using many sum() aggregation functions in same Sql statement

Mysql Workbench bug when using many sum() aggregation functions in same Sql statement

This is a part of a system's database I'm developing:

159
SQL-the average of same id in the past seven-days periods

SQL-the average of same id in the past seven-days periods

I have a table contains id and visit time, I want to find the average number of the same id appearance in each seven-days periodsthanks

141
Mysql: Share database from two different machine in two different nework with username “root”

Mysql: Share database from two different machine in two different nework with username “root”

I am doing a project and I created the database scheme for this project, but I have to send this to my teammate so they can use it to test the codeIs there a easy way to share this data with him so he can log in to his database and use it?

119
SQL top n inside group by time interval

SQL top n inside group by time interval

I have a table like this

196