Single ID generation with Mysql

Question

I am using the code below to test with a login system in PHP and Mysql. But the field nivel_usuario generates a number from 0 to 2, and I would like it to generate a unique 6-digit ID containing numbers and letters (both upper and lower case)

CREATE TABLE usuarios(
    usuario_id int(5) NOT NULL auto_increment,
    nome varchar(50) NOT NULL default '',
    sobrenome varchar(50) NOT NULL default '',
    email varchar(100) NOT NULL default '',
    usuario varchar(32) NOT NULL default '',
    senha varchar(32) NOT NULL default '',
    info text NOT NULL,
    nivel_usuario enum('0','1','2') NOT NULL default '0',
    data_cadastro datetime NOT NULL default '0000-00-00 00:00:00',
    data_ultimo_login datetime NOT NULL default '0000-00-00 00:00:00',
    ativado enum('0','1') NOT NULL default '0',
    PRIMARY KEY  (usuario_id)
) ENGINE = MYISAM CHARACTER SET latin1 COLLATE latin1_general_ci COMMENT = '';

Answer 1

You can use unique identifier generation methods based on timestamp, are they:

PHP uniqid():

printf("uniqid(): %s", substr(uniqid(), -6));

MySQL UUID():

SELECT LEFT(uuid(), 6);

Like you just want 6 digits, the functions substr and LEFT were used to aid in the treatment of string obtaining the amount of characters desired.

Answer 2

If you want it to be unique attribute the UNIQUE in column, this will prevent duplicates, Mysql itself will prevent the insertion of two equal values:

ALTER TABLE sua_tabela ADD UNIQUE (nome_da_coluna);

---

To generate such random data you could use the random_bytes, it is cryptographically secure.

As you do not want to store in bytes you can use BASE64, which includes A-Za-z0-9+/ and has a padding of =. _{Remembering that it includes the / and + in addition to letters and numbers.}

At the end you can use:

$aleatorio = substr( base64_encode( random_bytes(4) ), 0, -2);

This will generate 4 bytes (or 4294967296 possibilities), resulting in 6 digits due to BASE64. The substr is used to remove the =, unnecessary in this case.

---

_{The base64_encode native is not in constant-time, if you really care about it can use this implementation.}

Answer 3

Could do that :

$aleatorio = rand(6,6); // 6 CARACTERES
$valor = substr(str_shuffle("AaBbCcDdEeFfGgHhIiJjKkLlMmNnPpQqRrSsTtUuVvYyXxWwZz0123456789"), 0, $aleatorio);

Then check if the value already exists in the database, otherwise it uses the value as key in the database and if it generates another hash.

All the answers are valid and good, however I answered following the question of the colleague, he wants with six digits, capital letters and lowercase. My answer is correct because it does what was requested as shown below:

So I don’t understand why the negative score on the answer.