首页 / 知识

关于数据库：在MySQL中交换列值

2023-04-13 19:35:00

Swapping column values in MySQL

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

这可能以某种方式与UPDATE有关吗？更新表SET X = Y，Y = X显然不会满足我的要求。

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

wmh

I just had to deal with the same and I'll summarize my findings.

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

这是使用临时变量的方法。感谢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;

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;

我想出的另一种方法似乎可行：

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

本质上，第一个表是要更新的表，而第二个表是用来从中提取旧数据的。
请注意，此方法要求显示主键。

这是我的测试架构：

1
2
3
4
5
6
7
8
9
10

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求和并减去相反的值

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

这是一个示例测试(它适用于负数)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

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>

这是正在进行的交换

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

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>

试试看！！！

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

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

wmh

UPDATE table SET X=Y, Y=X will do precisely what you want (edit: in PostgreSQL, not MySQL, see below). The values are taken from the old row and assigned to a new copy of the same row, then the old row is replaced. You do not have to resort to using a temporary table, a temporary column, or other swap tricks.

@D4V360: I see. That is shocking and unexpected. I use PostgreSQL and my answer works correctly there (I tried it). See the PostgreSQL UPDATE docs (under Parameters, expression), where it mentions that expressions on the right hand side of SET clauses explicitly use the old values of columns. I see that the corresponding MySQL UPDATE docs contain the statement"Single-table UPDATE assignments are generally evaluated from left to right" which implies the behaviour you describe.

Good to know.

wmh

Ok, so just for fun, you could do this! (assuming you're swapping string values)

A nice bit of fun abusing the left-to-right evaluation process in MySQL.

Alternatively, just use XOR if they're numbers. You mentioned coordinates, so do you have lovely integer values, or complex strings?

Edit: The XOR stuff works like this by the way:

wmh

我相信以这种方式，最好将中间变量作为交换变量：

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

首先，它始终有效；第二，不管数据类型如何，它都能工作。

尽管两者

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

和

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

正常工作，顺便说一下，仅用于数字数据类型，这是防止溢出的责任，您不能在有符号和无符号之间使用XOR，也不能使用sum来产生溢出的可能性。

和

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

不管用
如果c1为0或NULL或长度为零的字符串或仅空格。

我们需要将其更改为

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

这是脚本：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85

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)

Something like this?

Edit: About Greg's comment:
No, this doesn't work:

wmh

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

wmh

This surely works! I've just needed it to swap Euro and SKK price columns. :)

The above will not work (ERROR 1064 (42000): You have an error in your SQL syntax)

wmh

Assuming you have signed integers in your columns, you may need to use CAST(a ^ b AS SIGNED), since the result of the ^ operator is an unsigned 64-bit integer in MySQL.

In case it helps anyone, here's the method I used to swap the same column between two given rows:

where $1 and $2 are the keys of two rows and $3 is the result of the first query.

wmh

I've not tried it but

Might do it.

Mark

wmh

您可以更改列名，但这更像是一个技巧。但请注意这些列上可能包含的任何索引

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

更新客户设置a =(@ temp：= a)，a = b，b = @temp

我检查了这工作正常。

在SQL Server中，可以使用以下查询：

1
2
3
4
5

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

如果要交换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;

1
2
3
4
5
6
7
8
9
10
11
12

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参考)对我有用。

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

此示例将start_date和end_date交换为日期错误的记录(当执行ETL重大重写时，我发现一些开始日期晚于其结束日期。糟糕的程序员！)。

在原位，出于性能原因，我正在使用MEDIUMINT(例如朱利安天数，但0根为1900-01-01)，所以我可以在WHERE条件下执行mdu.start_date> mdu.end_date。

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

1
2
3
4
5
6
7

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;

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

最安全的是：

复制tb_user。因此，您将有2个表：tb_user和tb_user_copy

使用UPDATE INNER JOIN查询

1
2
3
4

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

您可以在下面的查询中应用它，它对我来说非常理想。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

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;

使用单个查询交换列值

更新my_table SET a = @ tmp：= a，a = b，b = @ tmp;

干杯...！

关于数据库重命名解决方案结构