在 MySQL 中交换列值

我有一个带坐标的 MySQL 表，列名是 X 和 Y。现在我想交换这个表中的列值，这样 X 变成 Y，Y 变成 X。最明显的解决方案是重命名列，但是我不想进行结构更改，因为我不一定有这样做的权限。

这是否可能与 更新在某种程度上做? 更新表 SET X = Y，Y = X显然不会做我想要的。

编辑: 请注意，上面提到的对权限的限制有效地防止了 ALTER TABLE 或其他更改表/数据库结构的命令的使用。遗憾的是，重命名列或添加新列不是选项。

115533 次浏览
小开

两个选择 1. 使用临时表 2、调查 XOR 算法

小开 

表添加列 tmp;
更新表 SET tmp = X;
更新表 SET X = Y;
更新表 SET Y = tmp;
表删除列 tmp;
像这样吗？

编辑: 关于格雷格的评论: 不，这样不行:

Mysql > select * from test;
+------+------+
谢谢
+------+------+
我不知道
我不知道你在说什么
+------+------+
设置2行(0.00秒)




更新测试集 x = y，y = x;
查询 OK，2行受影响(0.00秒)
匹配的行: 2 Changed: 2 Warnings: 0




Mysql > select * from test;
+------+------+
谢谢
+------+------+
| 2 | 2 | 
| 4 | 4 | 
+------+------+
设置2行(0.00秒)

小开

UPDATE 表 SET X = Y，Y = X 将精确地执行您想要的操作(编辑: 在 PostgreSQL 中，而不是在 MySQL 中，见下文)。从旧行获取值并将其分配给同一行的新副本，然后替换旧行。您不必使用临时表、临时列或其他交换技巧。

@ D4V360: 我明白了。这是令人震惊和意想不到的。我使用 PostgreSQL，我的答案在那里可以正确工作(我试过了)。请参阅 PostgreSQL UPDATE 文档(在“参数，表达式”下) ，其中提到 SET 子句右侧的表达式显式使用列的旧值。我看到相应的 MySQL UPDATE 文档包含语句“从左到右计算单表 UPDATE 赋值”，这暗示了您所描述的行为。

很高兴知道。

小开

好吧，只是为了好玩，你可以这样做! (假设你正在交换字符串值)

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;
小开

假设在列中有符号整数，可能需要使用 CAST (a ^ b AS SIGNED) ，因为 ^ 运算符的结果是 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

也许可以。

马克

小开

这当然有用! 我只是需要它来交换欧元和 SKK 的价格栏。 :)

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

以上方法不起作用(ERROR 1064(42000) : 您的 SQL 语法有错误)

小开
最佳答案

我也要处理同样的问题，我会总结一下我的发现。

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

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

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

  3. 这个方法是由 Dipin 在 http://beerpla.net/2009/02/17/swapping-column-values-in-mysql/的注释中提供的。我认为这是最优雅干净的解决方案。它同时使用 NULL 和非 NULL 值。

    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);
小开

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

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

您可以更改列名称，但这更像是一种黑客行为。但是要小心这些列上的任何索引

小开

可以使用 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>

试试看! ! ！

小开

使用单个查询交换列值

更新 my _ table SET a =@tmp: = a，a = b，b =@tmp;

干杯！

小开

我认为，设置一个中间汇率变量是这方面的最佳做法:

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或者 NULL 或者长度为零的字符串或者只是空格。

我们需要把它改成

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)
小开 
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;
小开

我只需要将值从一列移动到另一列(如归档)并重置原始列的值。
下面的内容(参考上面已接受的答案 # 3)对我很有用。

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

这个示例将 Start _ date和 结束日期交换为日期方向错误的记录(在执行 ETL 进行重大重写时，我发现一些 开始日期晚于它们的 结束日期。下来，差劲的程序员!).

在原地，我使用 MEDIUMINT 是出于性能方面的原因(比如 Julian days，但是有一个1900-01-01的0根) ，所以我可以使用 WHERE mdu.start _ date > mdu.end _ date条件。

PK 分别位于所有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; 。

更新客户 SET a = (@temp: = a) ，a = b，b =@temp

我检查过这个是否正常工作。

小开

在 SQLServer 中，可以使用此查询:

update swaptable
set col1 = t2.col2,
col2 = t2.col1
from swaptable t2
where id = t2.id
小开

假设您想在 tb _ user 中交换名字和姓氏的值。

最安全的方法是:

  1. 复制 tb _ user。因此您将有两个表: tb _ user 和 tb _ user _ Copy
  2. 使用更新内连接查询
UPDATE tb_user a
INNER JOIN tb_user_copy b
ON a.id = b.id
SET a.first_name = b.last_name, a.last_name = b.first_name
小开

您可以申请以下查询，它为我工作完美。

Table name: studentname
only single column available: name




update studentnames
set names = case names
when "Tanu" then "dipan"
when "dipan" then "Tanu"
end;


or


update studentnames
set names = case names
when "Tanu" then "dipan"
else "Tanu"
end;
小开

如果希望将 x 到 y 和 y 到 x 的所有列交换，请使用以下查询。

UPDATE table_name SET column_name = CASE column_name WHERE 'value of col is x' THEN 'swap it to y' ELSE 'swap it to x' END;

小开

让我们想象一下这个表，并尝试从“ sex”表中交换 m 和 f:

身份证 姓名 做爱 薪水
1 A 我 2500
2 B F 1500
3 C 我 5500
4 D F 五百 
UPDATE sex
SET sex = CASE sex
WHEN 'm' THEN 'f'
ELSE 'm'
END;

因此，更新后的表格变成:

身份证 姓名 做爱 薪水
1 A F 2500
2 B 我 1500
3 C F 5500
4 D 我 五百
小开

正如其他答案所指出的，简单的交换对 MySQL 不起作用是因为它在处理列2之前缓存了列1 马上的值，导致两个列都被设置为列2的值。

考虑到 MySQL 中的操作顺序不能得到保证，使用临时变量也是不可靠的。

在不修改表结构的情况下交换两列的唯一安全方法是使用内部连接，这需要一个主键(本例中为 id)。

UPDATE mytable t1, mytable t2
SET t1.column1 = t1.column2,
t1.column2 = t2.column1
WHERE t1.id = t2.id;

这将工作没有任何问题。


提交答案