在MySQL中交换列值

我只需要处理同样的问题,我会总结我的发现。

1. 这个 UPDATE table SET X=Y, Y=X 方法显然不起作用,因为它只将两个值都设置为y。

2. 下面是一个使用临时变量的方法。感谢安东尼的评论 http://beerpla.net/2009/02/17/swapping-column-values-in-mysql/ 因为“不是空的”调整。如果没有它,查询将无法预测地工作。请参见文章末尾的表模式。如果其中一个值为空,则此方法不会交换这些值。使用没有此限制的方法3。

   UPDATE swap_test SET x=y, y=@temp WHERE (@temp:=x) IS NOT NULL;

3. 这种方法是由迪平提出的,但同样的,他的评论 http://beerpla.net/2009/02/17/swapping-column-values-in-mysql/ . 我认为这是最优雅、最干净的解决方案。它同时使用空值和非空值。

   UPDATE swap_test SET x=(@temp:=x), x = y, y = @temp;

4. 我提出的另一种方法似乎有效:

   UPDATE swap_test s1, swap_test s2 SET s1.x=s1.y, s1.y=s2.x WHERE s1.id=s2.id;

从本质上讲,第一个表是正在更新的表,第二个表用于从中提取旧数据。
注意,这种方法需要有一个主键。

这是我的测试模式:

CREATE TABLE `swap_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `x` varchar(255) DEFAULT NULL,
  `y` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

INSERT INTO `swap_test` VALUES ('1', 'a', '10');
INSERT INTO `swap_test` VALUES ('2', NULL, '20');
INSERT INTO `swap_test` VALUES ('3', 'c', NULL);

你可以取和,用x和y减去相反的值。

UPDATE swaptest SET X=X+Y,Y=X-Y,X=X-Y;

这是一个样本测试(它适用于负数)

mysql> use test
Database changed
mysql> drop table if exists swaptest;
Query OK, 0 rows affected (0.03 sec)

mysql> create table swaptest (X int,Y int);
Query OK, 0 rows affected (0.12 sec)

mysql> INSERT INTO swaptest VALUES (1,2),(3,4),(-5,-8),(-13,27);
Query OK, 4 rows affected (0.08 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM swaptest;
+------+------+
| X    | Y    |
+------+------+
|    1 |    2 |
|    3 |    4 |
|   -5 |   -8 |
|  -13 |   27 |
+------+------+
4 rows in set (0.00 sec)

mysql>

这是正在执行的交换

mysql> UPDATE swaptest SET X=X+Y,Y=X-Y,X=X-Y;
Query OK, 4 rows affected (0.07 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> SELECT * FROM swaptest;
+------+------+
| X    | Y    |
+------+------+
|    2 |    1 |
|    4 |    3 |
|   -8 |   -5 |
|   27 |  -13 |
+------+------+
4 rows in set (0.00 sec)

mysql>

试一试!!!!

以下代码适用于我的快速测试中的所有场景:

UPDATE table swap_test
   SET x=(@temp:=x), x = y, y = @temp

更新表格集x=y,y=x 会做你想要的(编辑:在PostgreSQL,而不是MySQL中,见下文)。这些值从旧行中获取并分配给同一行的新副本,然后替换旧行。您不必使用临时表、临时列或其他交换技巧。

@我明白了。这是令人震惊和意想不到的。我使用了PostgreSQL,我的答案在那里工作正常(我试过了)。见 PostgreSQL UPDATE docs (在参数、表达式下),其中提到set子句右侧的表达式显式使用列的旧值。我看到了相应的 MySQL UPDATE docs 包含语句“单表更新分配通常从左到右进行评估”,这意味着您描述的行为。

很高兴知道。

好吧,为了好玩,你可以这样做!(假设要交换字符串值)

mysql> select * from swapper;
+------+------+
| foo  | bar  |
+------+------+
| 6    | 1    | 
| 5    | 2    | 
| 4    | 3    | 
+------+------+
3 rows in set (0.00 sec)

mysql> update swapper set 
    -> foo = concat(foo, "###", bar),
    -> bar = replace(foo, concat("###", bar), ""),
    -> foo = replace(foo, concat(bar, "###"), "");

Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from swapper;
+------+------+
| foo  | bar  |
+------+------+
| 1    | 6    | 
| 2    | 5    | 
| 3    | 4    | 
+------+------+
3 rows in set (0.00 sec)

在MySQL中滥用从左到右的评估过程是很有趣的。

或者,如果它们是数字,就使用XOR。你提到坐标,那么你有可爱的整数值,还是复杂的字符串?

编辑:XOR的工作原理如下:

update swapper set foo = foo ^ bar, bar = foo ^ bar, foo = foo ^ bar;

两个备选方案 1。使用临时表 2。调查 这个 XOR algorithm

ALTER TABLE table ADD COLUMN tmp;
UPDATE table SET tmp = X;
UPDATE table SET X = Y;
UPDATE table SET Y = tmp;
ALTER TABLE table DROP COLUMN tmp;

编辑:关于格雷格的评论: 不,这不管用:

mysql> select * from test;
+------+------+
| x    | y    |
+------+------+
|    1 |    2 |
|    3 |    4 |
+------+------+
2 rows in set (0.00 sec)

mysql>更新测试集x=y,y=x; 查询正常,2行受影响(0.00秒) 匹配的行:2已更改:2个警告:0

mysql>从测试中选择* +----+----+ x y y +----+----+ (2×2) (4×4) +----+----+ 一组2行(0.00秒)

我认为,采用中间交换变量是最佳做法:

update z set c1 = @c := c1, c1 = c2, c2 = @c

首先,它总是工作的;其次,不管数据类型如何,它都工作。

尽管两者兼而有之

update z set c1 = c1 ^ c2, c2 = c1 ^ c2, c1 = c1 ^ c2

和

update z set c1 = c1 + c2, c2 = c1 - c2, c1 = c1 - c2

一般都是工作的,只对数字数据类型有一种方式,而且它是您的责任,防止溢出,您不能在有符号和无符号之间使用异或,您也不能用和来表示溢出的可能性。

和

update z set c1 = c2, c2 = @c where @c := c1

不工作 如果c1是0或空或零长度字符串或只是空格。

我们需要把它换成

update z set c1 = c2, c2 = @c where if((@c := c1), true, true)

以下是脚本:

mysql> create table z (c1 int, c2 int)
    -> ;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into z values(0, 1), (-1, 1), (pow(2, 31) - 1, pow(2, 31) - 2)
    -> ;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from z;
+------------+------------+
| c1         | c2         |
+------------+------------+
|          0 |          1 |
|         -1 |          1 |
| 2147483647 | 2147483646 |
+------------+------------+
3 rows in set (0.02 sec)

mysql> update z set c1 = c1 ^ c2, c2 = c1 ^ c2, c1 = c1 ^ c2;
ERROR 1264 (22003): Out of range value for column 'c1' at row 2
mysql> update z set c1 = c1 + c2, c2 = c1 - c2, c1 = c1 - c2;
ERROR 1264 (22003): Out of range value for column 'c1' at row 3

mysql> select * from z;
+------------+------------+
| c1         | c2         |
+------------+------------+
|          0 |          1 |
|          1 |         -1 |
| 2147483646 | 2147483647 |
+------------+------------+
3 rows in set (0.02 sec)

mysql> update z set c1 = c2, c2 = @c where @c := c1;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from z;
+------------+------------+
| c1         | c2         |
+------------+------------+
|          0 |          1 |
|         -1 |          1 |
| 2147483647 | 2147483646 |
+------------+------------+
3 rows in set (0.00 sec)

mysql> select * from z;
+------------+------------+
| c1         | c2         |
+------------+------------+
|          1 |          0 |
|          1 |         -1 |
| 2147483646 | 2147483647 |
+------------+------------+
3 rows in set (0.00 sec)

mysql> update z set c1 = @c := c1, c1 = c2, c2 = @c;
Query OK, 3 rows affected (0.02 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from z;
+------------+------------+
| c1         | c2         |
+------------+------------+
|          0 |          1 |
|         -1 |          1 |
| 2147483647 | 2147483646 |
+------------+------------+
3 rows in set (0.00 sec)

mysql>update z set c1 = c2, c2 = @c where if((@c := c1), true, true);
Query OK, 3 rows affected (0.02 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from z;
+------------+------------+
| c1         | c2         |
+------------+------------+
|          1 |          0 |
|          1 |         -1 |
| 2147483646 | 2147483647 |
+------------+------------+
3 rows in set (0.00 sec)

这当然行!我只是需要它来交换欧元和skk价格栏。:)

UPDATE tbl SET X=Y, Y=@temp where @temp:=X;

上述操作将不起作用(错误1064(42000):您的SQL语法中有错误)

假设列中有带符号整数,则可能需要使用cast(a^b表示带符号),因为^运算符的结果在MySQL中是一个无符号64位整数。

如果它对任何人都有帮助,下面是我在两行之间交换同一列的方法:

SELECT BIT_XOR(foo) FROM table WHERE key = $1 OR key = $2

UPDATE table SET foo = CAST(foo ^ $3 AS SIGNED) WHERE key = $1 OR key = $2

其中$1和$2是两行的键,$3是第一个查询的结果。

我没试过,但是

UPDATE tbl SET @temp=X, X=Y, Y=@temp

可能会这样。

作记号

你 能够 更改列名,但这更像是一种黑客行为。但是要小心这些列上的任何索引

使用单个查询交换列值

更新我的表格集a=@tmp:=a,a=b,b=@tmp;

干杯。。。!

我只需要将值从一列移动到另一列(如归档),然后重置原始列的值。
下面(上面接受的答案中提到的3)对我有用。

Update MyTable set X= (@temp:= X), X = 0, Y = @temp WHERE ID= 999;

CREATE TABLE Names
(
F_NAME VARCHAR(22),
L_NAME VARCHAR(22)
);

INSERT INTO Names VALUES('Ashutosh', 'Singh'),('Anshuman','Singh'),('Manu', 'Singh');

UPDATE Names N1 , Names N2 SET N1.F_NAME = N2.L_NAME , N1.L_NAME = N2.F_NAME 
WHERE N1.F_NAME = N2.F_NAME;

SELECT * FROM Names;

此示例交换 启动日期 和 末日 对于日期循环错误的记录(在将ETL执行到主要重写时,我发现了一些 开始 日期晚于 结束 日期。向下,糟糕的程序员!).

在现场,出于性能原因(比如朱利安·戴斯,但是有1900-01-01的0根),我使用的是医用材料,所以我可以在 其中mdu.start_date>mdu.end_date .

pks分别位于所有3列上(出于操作/索引原因)。

UPDATE monitor_date mdu
INNER JOIN monitor_date mdc
    ON mdu.register_id = mdc.register_id
    AND mdu.start_date = mdc.start_date
    AND mdu.end_date = mdc.end_date
SET mdu.start_date = mdu.end_date, mdu.end_date = mdc.start_date
WHERE mdu.start_date > mdu.end_date;

表名是客户。 字段是A和B,将值交换为B;。

更新客户设置a=(@temp:=a),a=b,b=@temp

我检查过这个工作正常。