Re: Mapping output from a SEQUENCE into something non-repeating/colliding but random-looking?

On 2009-05-03, Erik Jones <ejones(at)engineyard(dot)com> wrote:

>> What I'm looking for is a function that, given an input within a
>> constrained range (say, a 32 bit integer) produces a different output
>> within the same range. For any given input, the output should be the
>> same each time, and for any given output there should only be one
>> input
>> that results in that output.
>
> I think you drop the idea of a repeatable mapping you may have some
> success with the Knuth (aka Fisher-Yates) shuffle algorithm: http://en.wikipedia.org/wiki/Fisher%E2%80%93Yates_shuffle
> Why does anything need to be repeatable when you only need to make
> sure that each number is only generated once?

That means storing a long list of numbers and doing queries similar to
the following to get ne next value for the sequence.

select id from idtable
order by id
limit 1
offset random(0, (select count (*) from idtable)

a ramdom-looking 1:1 mapping is potentially much more efficient.