CODEWITHSUNDEEP
phpmysqlauto-incrementunique-key
user3109875
user3109875

Reputation: 828

Create a longer unique auto-increment column in mysql with more 6 chars at first row

A Auto Increment coloumn can be created in mysql, can i create an auto-generated number on a column that will be unique to all rows and that random number be more than 6 charecter and be alphanumeric?

Currently im using PHP.

function randomCode(){
    $alphabet = "8ABCDEFGIJKLMmNPQjhgkhjg";
    $Code = array();
    $alphaLength = strlen($alphabet) - 1;
    for ($i = 0; $i < 8; $i++){
        $n = rand(0, $alphaLength);
        $Code[] = $alphabet[$n];
        }
    return implode($Code);
    }

But the problem with this is i can't control the uniqueness of this codes being stored in the database.

Upvotes: 1

Views: 2105

Answers (3)

serakfalcon
serakfalcon

Reputation: 3531

MySQL auto-increment column's can't be alphanumeric. For the love of all that is sacred don't use a UUID unless you really know what you are doing. Sequential keys are way faster for queries etc. and it's a real pain to do maintenance on a db with a randomized key. For simplicity's sake I assume you can handle the integer to alphanumeric conversion code and proceed accordingly:

These are the two options I would recommend for you.

First option:

Assuming that you want to DISPLAY the key as alphanumeric you can always cheat: just have some conversion code on display that converts the INT to an alphanumeric value (basically changing it from base 10 to base 36 plus more if you want special characters) I would prefer this since it'll be by far the fastest and cause the least amount of headaches. If you're worried about running out of values for the database use an unsigned BIGINT which is basically half the size of a UUID but should be faster for queries.

Second option:

Create your own autonumber system: make another table with two columns, the table name and 'last key entered'. You can query that table on every insert to find the 'last key entered' and then increment the 'last key entered', and update the autonumber table. This could be slow with a large amount of inserts but gives you full control of the autonumber. You can also have the column filled out by a trigger that runs the procedure to get the autonumber. If programmed correctly it'll work almost as well as the built-in autonumbers, the issue is, do you have the time and do you want to spend the energy to write that code :)

EDIT: just noticed the 'display 6 characters' -> again, that's a display issue, you can always use lpad (http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_lpad) to make it 6 characters, or change the autoincrement start value (just send an INSERT statement with the ID = some value)

Upvotes: 1

Dmitriy.Net
Dmitriy.Net

Reputation: 1500

Try to use mysq UUID()

Insertion exapmle

  INSERT INTO table (id, name) VALUES (UUID(), 'hello')

Or without dashes

  INSERT INTO table (id, name) VALUES (REPLACE(UUID(), '-', ''), 'hello')

Upvotes: 1

Alma Do
Alma Do

Reputation: 37365

Actually, you can not do that natively in MySQL. This DBMS doesn't support sequences. The only supported automatic option is normal auto_increment - which is in general consecutive (but - yes, you can do some minor adjusting to it, which, however, will not help in your case). So you can not achieve your goal with MySQL and you'll have to deal with this in your application.

If you want your sequence to be random and unique - you may want to look to uniqid() function. It's guaranteed that values will be unique - and as for length, it definitely will be more than 6 characters. For more random values, you can combine in with mt_rand() like:

$long_value  = uniqid(mt_rand(),1); //long string, length > 30
$short_value = uniqid(); //short string, length < 20

Upvotes: 1

Related Questions