随机化查询中每行两列的顺序(Randomize order of two columns per row in a query)

随机化查询中每行两列的顺序(Randomize order of two columns per row in a query)

我希望将行从一个MySQL表复制到另一个，同时随机化两个字段的顺序，即我想从table1获取带有字段id ， a ， b行并将它们插入到table2 ，但是在随机行上交换a和b 。

这是我试过的：

INSERT INTO table2 (id, a, b) SELECT id, IF(@r=RAND() < 0.5, a, b), IF(@r < 0.5, b, a) FROM table1 WHERE table1.filter = 42;

我希望这会产生每行一个随机数，但它实际上每个查询只生成一个随机数，所以它是a，b表示所有行或b，a表示所有行。 我需要改变什么？

---

PS：我正在使用变量，因为在两个IF中调用rand()将生成两个单独的数字，我可以两次获得相同的字段。 有没有变量的方法吗？

I want to copy rows from one MySQL table to another while randomizing the order of two fields, i.e. I want to take rows with the fields id,a,b from table1 and insert them into table2, but swap a and b on random rows.

This is what I tried:

INSERT INTO table2 (id, a, b) SELECT id, IF(@r=RAND() < 0.5, a, b), IF(@r < 0.5, b, a) FROM table1 WHERE table1.filter = 42;

I expected that this will generate one random number per row, but it actually just generates one random number per query, so it's either a,b for all rows or b,a for all rows. What do I need to change?

---

P.S: I'm using a variable because calling rand() in both IFs will generate two seperate numbers and I could get the same field twice. Is there a way to to this without a variable?

最满意答案

使用@variables似乎可以工作，但是MySQL 不鼓励为同一语句中的变量分配和读取值，因为：

...未定义涉及用户变量的表达式的评估顺序。

更好的解决方案如下：

-- INSERT INTO ... SELECT id, IF(r < 0.5, a, b), IF(r < 0.5, b, a) FROM ( SELECT id, a, b, RAND() AS r FROM table1 WHERE table1.filter = 42 ) x

Using @variables seems to work but MySQL discourages assigning and reading values to variables inside same statement because:

... the order of evaluation for expressions involving user variables is undefined.

A better solution is as follows:

-- INSERT INTO ... SELECT id, IF(r < 0.5, a, b), IF(r < 0.5, b, a) FROM ( SELECT id, a, b, RAND() AS r FROM table1 WHERE table1.filter = 42 ) x