mysql - 如何混合列数据

Question

我有一张 table table_1

 id     contact_id
 1         500
 5         89
 8         35
 15        458
 ...       ...
 555       38

如何混合 contact_id 和 table_1 的结果

id     contact_id
 1         35
 5         458
 8         35
 15        89
 ...       ...
 555       45

Answer 1

您可以使用变量或row_number()(在 MySQL 8+ 中)随机分配联系人 ID:

select t1.id, tt1.contact_id
from (select t1.*, row_number() over (order by id) as seqnum
      from table_1 t1
     ) t1 join
     (select t1.*, row_number() over (order by rand()) as seqnum
      from table_1 t1
     ) tt1
     on t1.seqnum = tt1.seqnum;

没有太大困难(但需要更多输入)，可以将其转换为使用早期版本中的变量。

如果您想永久打乱值，您还可以将其合并到 update 语句中。

编辑:

我想你想要:

update table1 t1 join
       (select t1.id, tt1.contact_id
        from (select t1.*, (@rn1 := @rn1 + 1) as seqnum
              from (select * table_1 order by id) t1 cross join
                   (select @rn1 := 0) params
             ) t1 join
             (select t1.*, (@rn2 := @rn2 + 1) as seqnum
              from (select * from table_1 order by rand()) t1 cross join
                   (select @rn2 := 0) params
             ) tt1
             on t1.seqnum = tt1.seqnum
       ) tt1
       on tt1.id = t1.id
    set t1.contact_id = tt1.contact_id;