How to make a random query in MySQL without repeating data?

A simple solution would be to make ORDER BY RAND in the entire table before starting paging, storing only the image IDs. It's an extra job, but storing only the IDs at the first "last".

Having the list of IDs in an array, you use the following in the pages:

SELECT campos FROM tabela WHERE id IN ( lista ) ORDER BY RAND();

Being lista is a subset of the IDs from page start to end of page (from $id[iniciodapagina] to $id[fimdapagina] ).

Mathematical Alternative

You can change the place of the rand to a previous, before pagination, and use it as

ORDER BY MOD(id * $valor, $quantidade_registros + 1) ;

Being $valor must be a prime greater than $quantidade_registros

See working in SQL Fiddle .

This works fine because being a prime number will never be a multiple of the quantity, and will always go through all the IDs if you get the total amount of records.

As whole numbers usually take up little space in a DB, you can get a table with a lot of primes here:

  

link

Taking up 13kbytes of space you can store the first 6500 prime numbers, which fit 2 bytes each, giving a nice range to use in the query above.

Use a fixed value as a parameter for RAND()

One way to make paging and keep random order using RAND() is to pass a fixed number as a parameter because in this way the order will be the same in all calls and will remain in pagination. And to generate the randomness you need, you make the draw of that number that will pass when the user loads the first page.

To control this, one way is to store this value in the user's session. If your system does not use sessions, you may have to find some mechanism like returning the number generated along with the return of the first page, saving it on the browser side, and when asking the next page to pass that number ... There are several ways do this, choose the one that works best for you.

Using the session, it would look something like:

session_start();
if ($offset == 0) {
   $seed = rand(1, 1000);
   $_SESSION["seed"] = $seed;
} else {
   $seed = $_SESSION["seed"];
}

$return = $database->query("

    SELECT 
        lk_post_pic.*, 
        tb_post.head, 
        tb_post.created_datetime, 
        tb_pic.album, 
        tb_pic.file, 
        tb_pic.thumbnail

    FROM lk_post_pic
    JOIN tb_post ON lk_post_pic.fk_post = tb_post.id_post
    JOIN tb_pic  ON lk_post_pic.fk_pic  = tb_pic.id_pic

    WHERE tb_pic.thumbnail = 'default'
    ORDER BY RAND($seed)
    LIMIT 20
    OFFSET $offset

")->fetchAll(PDO::FETCH_ASSOC);