How to make shuffle in mysql database?

Question

Let's suppose I have a mysql database with sensitive information and I would like to make a shuffle on some data such as swapping values from the lastname column by values from the same column only from another user.

Note: the database will be used by the developers and I do not want them to have access to the true information so those suggestions using select would not solve, I saw that Soft DataVeil does this but not in mysql

Example User1 receives lastname from user4

Orginal data

Fristname    | Lastname
user1        |   lastname1
user2        |   lastname2
user3        |   lastname 3
user4        |   lastname4
user5        |   lastname5

Shuffle mode

Fristname    |  Lastname
user1        |  lastname4
user2        |  lastname5 
user3        |  lastname2
user4        |  lastname1
user5        |  lastname3

Note: I tried to use the dataveil for this but apparently it does not do shuffle in mysql

Answer 1

If you dont mind duplicates in the Lastname column
You can do it with a simple subquery with a random order and limit.

Query

SELECT
   Table1.Fristname
 , (SELECT Table11.Lastname FROM Table1 AS Table11 ORDER BY RAND() LIMIT 1) AS  Lastname
FROM 
 Table1

Possible result

| Fristname | Lastname   |
| --------- | ---------- |
| user1     | lastname 3 |
| user2     | lastname 3 |
| user3     | lastname 3 |
| user4     | lastname5  |
| user5     | lastname4  |

View on DB Fiddle

Answer 2

• You can use session variables to get row number.
• In two separate derived tables, get Fristname, and Lastname (latter one being randomly ordered).
• Join them both on the row number. Since Lastname is randomly ordered, you will get different Lastname for a Fristname.
• This approach will ensure No Duplicates in the Lastname column

All versions of MySQL: Try something like this (SQL Fiddle DEMO):

SELECT t3.Fristname,
       t4.Lastname 
    FROM ( 
           SELECT @rn1 := @rn1 + 1 AS rn1, 
                  t1.Fristname  
           FROM your_table AS t1 
           CROSS JOIN ( SELECT @rn1 := 0) AS rnt1 
         ) AS t3 
    INNER JOIN (
                 SELECT @rn2 := @rn2 + 1 AS rn2, 
                        t5.Lastname 
                 FROM 
                 ( 
                   SELECT t2.Lastname 
                   FROM your_table AS t2 
                   ORDER BY rand()
                 ) AS t5
                  CROSS JOIN ( SELECT @rn2 := 0) AS rnt2
                ) AS t4 ON t4.rn2 = t3.rn1