在MySQL中交换列值

mysql database

76899 观看

19回复

7606 作者的声誉

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

这有可能以某种方式与UPDATE有关吗?UPDATE表SET X = Y,Y = X显然不会做我想要的。


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

作者: Liedman 的来源 发布者: 2008 年 9 月 1 日

回应 (19)


4

8095 作者的声誉

两种选择1.使用临时表2.研究XOR算法

作者: Unsliced 发布者: 01.09.2008 09:24

4

15792 作者的声誉

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)<p></p>

<p>mysql> update test set x=y, y=x;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0</p>

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

作者: fijter 发布者: 01.09.2008 09:25

10

700444 作者的声誉

UPDATE表SET X = Y,Y = X将完全按照您的要求进行操作(编辑:在PostgreSQL中,而不是MySQL,见下文)。这些值取自旧行并分配给同一行的新副本,然后替换旧行。您不必使用临时表,临时列或其他交换技巧。

@ D4V360:我明白了。这令人震惊和意外。我使用PostgreSQL,我的答案在那里正常工作(我试过)。请参阅PostgreSQL UPDATE文档(在Parameters,expression下),其中提到SET子句右侧的表达式显式使用列的旧值。我看到相应的MySQL UPDATE文档包含语句“单表UPDATE赋值通常从左到右进行评估”,这意味着您描述的行为。

很高兴知道。

作者: Greg Hewgill 发布者: 01.09.2008 09:31

5

12416 作者的声誉

好的,所以只是为了好玩,你可以做到这一点!(假设您正在交换字符串值)

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;
作者: mercutio 发布者: 01.09.2008 11:00

1

42208 作者的声誉

假设您已在列中签署了整数,则可能需要使用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是第一个查询的结果。

作者: Artelius 发布者: 22.12.2008 07:21

1

54616 作者的声誉

我没试过,但是

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

可能会这样做。

标记

作者: MarkR 发布者: 22.12.2008 07:36

2

0 作者的声誉

这肯定有效!我只是需要它来交换欧元和SKK价格列。:)

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

以上操作无效(ERROR 1064(42000):您的SQL语法有错误)

作者: thorn 发布者: 27.12.2008 01:47

182

14043 作者的声誉

决定

我只需处理相同的事情,我将总结我的发现。

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

  2. 这是一个使用临时变量的方法。感谢Antony在http://beerpla.net/2009/02/17/swapping-column-values-in-mysql/的评论中对“IS NOT NULL”的调整。没有它,查询工作无法预测。请参阅帖子末尾的表架构。如果其中一个为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);
作者: Artem Russakovskii 发布者: 18.02.2009 12:01

22

804 作者的声誉

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

UPDATE table swap_test
   SET x=(@temp:=x), x = y, y = @temp
作者: Dipin 发布者: 18.02.2009 06:15

1

10469 作者的声誉

可以更改列名称,但这更像是一个黑客。但要谨慎对待这些列上的任何索引

作者: SeanDowney 发布者: 27.08.2009 10:57

47

37869 作者的声誉

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

试试看 !!!

作者: RolandoMySQLDBA 发布者: 31.07.2012 09:44

0

149 作者的声誉

使用单个查询交换列值

UPDATE my_table SET a = @ tmp:= a,a = b,b = @ tmp;

干杯...!

作者: webizon 发布者: 10.06.2014 02:42

4

318 作者的声誉

我相信有一个中间交换变量是这样的最佳实践:

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

通常都是工作,顺便说一下,对于数字数据类型,你有责任防止溢出,你不能在签名和无符号之间使用XOR,你也不能使用sum来满足溢出的可能性。

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)
作者: workplaylifecycle 发布者: 22.08.2014 09:43

0

390 作者的声誉

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;
作者: Ashutosh SIngh 发布者: 09.02.2015 05:30

0

1 作者的声誉

我不得不将值从一列移动到另一列(如归档)并重置原始列的值。
以下(上面接受的答案#3的参考)对我有用。

Update MyTable set X= (@temp:= X), X = 0, Y = @temp WHERE ID= 999;
作者: Archer1974 发布者: 24.06.2016 07:01

0

41 作者的声誉

这个例子交换了start_dateend_date,用于记录日期错误的记录(当ETL进行重大改写时,我发现一些开始日期晚于结束日期。下来,糟糕的程序员!)。

在原地,我使用MEDIUMINTs是出于性能原因(比如朱利安时代,但是有一个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;
作者: Andrew Foster 发布者: 06.09.2018 09:33

0

11 作者的声誉

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

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

我检查过这个工作正常。

作者: Raman Singh 发布者: 03.01.2019 02:40

0

1 作者的声誉

在SQL Server中,您可以使用此查询:

update swaptable 
set col1 = t2.col2,
col2 = t2.col1
from swaptable t2
where id = t2.id
作者: SamK 发布者: 19.07.2019 09:32

0

31 作者的声誉

假设您要在tb_user中交换名字和姓氏的值。

最安全的是:1。复制tb_user。所以你将有2个表:tb_user和tb_user_copy 2.使用UPDATE INNER JOIN查询

UPDATE tb_user INNER JOIN tb_user_copy b ON a.id = b.id SET a.first_name = b.last_name,a.last_name = b.first_name

作者: Felix Labayen 发布者: 25.07.2019 02:59
32x32