Generate numbers in order for mysql

Question

I'm working on creating a forum (just to test) and i've reached the point where i sync the thread lists and the posts inside. I've relied on the AUTO INCREMENT in mysql to sync them but i understand that it won't be useful in the future.

My question is now, how would i generate a random number stacking just like the mysql auto_increment ?

For viewing the thread list, it's currently

$sql = "SELECT * FROM threads WHERE th_unique='$section'; $result = mysqli_query($db,$sql);

and then i just fetch the data and output the threads in the list.

Basicly, how would i generate a number just like Auto increment when a insert query is sent?

I am aware of rand() but i don't find it effective in the end due to the fact that it might overlap and use the same number that already exists.

[`uniqid()`](http://php.net/uniqid), but what exactly is your problem with `AUTO INCREMENT`? — NDM, Aug 10 '16 at 09:38
If you're using InnoDB as your engine for the table, why not just do this as a transaction? — Ultimater, Aug 10 '16 at 09:44
Also, what's wrong with `auto_increment` and why can't you use it in the future and how does the random number help you solve the issue? There's also a MySQL function called `UUID()`. If you worry about exposing numeric identifiers to the public, you can always encrypt them or encode them with `Hashids` or similar. — Mjh, Aug 10 '16 at 11:45

score 0 · Accepted Answer · edited May 23 '17 at 12:22

Actually, you can use AUTO_INCREMENT with replication under certain conditions.

Statement-based replication of AUTO_INCREMENT, LAST_INSERT_ID(), and TIMESTAMP values is done correctly, subject to the following exceptions:

When using statement-based replication prior to MySQL 5.7.1, AUTO_INCREMENT columns in tables on the slave must match the same columns on the master; that is, AUTO_INCREMENT columns must be replicated to AUTO_INCREMENT columns. ...

And the list goes on. If your situation is one of the conditions where AUTO_INCREMENT doesn't work UUID is an option.

Also take a look at this answer: https://stackoverflow.com/a/37605582/267540 it's for python/django Here's what it looks like when translated to PHP

define('START_TIME',1470825057000);

function make_id() {
    /**
     * inspired by http://instagram-engineering.tumblr.com/post/10853187575/sharding-ids-at-instagram
     * Generates a unique identifier that isn't too complex and has a sequential nature (sort of)
     */

    $t = microtime(True)*1000 - START_TIME;

    $rnd = random_int(0,8388607);
    return ($t << 23) | $rnd;

}

function reverse_id($id) {
    $id = ($id >> 23) + START_TIME;
    return $id;
}

for ($counter=0; $counter<100; $counter++) {
    $id = make_id() ;
    $time = reverse_id($id);
    print "$id  $time \n"; 
}
print 'Ending time     ' . microtime(True)*1000;

As you can see the number look sequential but they are still safe for replication.

Yea, that looks like something i'd want to use, if you can covert it, you're the best — Synyster, Aug 10 '16 at 09:57

Generate numbers in order for mysql

1 Answers1