互换数据表中两列数据方法

1.创建表及记录用于测试

<code class="hljs sql has-numbering"><span class="hljs-operator"><span class="hljs-keyword">CREATE <span class="hljs-keyword">TABLE <span class="hljs-string">`product` (
 <span class="hljs-string">`id` <span class="hljs-keyword">int(<span class="hljs-number">10) unsigned <span class="hljs-keyword">NOT <span class="hljs-keyword">NULL AUTO_INCREMENT COMMENT <span class="hljs-string">'产品id',
 <span class="hljs-string">`name` <span class="hljs-keyword">varchar(<span class="hljs-number">50) <span class="hljs-keyword">NOT <span class="hljs-keyword">NULL COMMENT <span class="hljs-string">'产品名称',
 <span class="hljs-string">`original_price` <span class="hljs-keyword">decimal(<span class="hljs-number">5,<span class="hljs-number">2) unsigned <span class="hljs-keyword">NOT <span class="hljs-keyword">NULL COMMENT <span class="hljs-string">'原价',
 <span class="hljs-string">`price` <span class="hljs-keyword">decimal(<span class="hljs-number">5,<span class="hljs-number">2) unsigned <span class="hljs-keyword">NOT <span class="hljs-keyword">NULL COMMENT <span class="hljs-string">'现价',
 <span class="hljs-keyword">PRIMARY <span class="hljs-keyword">KEY (<span class="hljs-string">`id`)
) ENGINE=InnoDB <span class="hljs-keyword">DEFAULT CHARSET=utf8;

<span class="hljs-operator"><span class="hljs-keyword">INSERT <span class="hljs-keyword">INTO <span class="hljs-string">`product` (<span class="hljs-string">`id`, <span class="hljs-string">`name`, <span class="hljs-string">`original_price`, <span class="hljs-string">`price`) <span class="hljs-keyword">VALUES 
(<span class="hljs-keyword">NULL, <span class="hljs-string">'雪糕', <span class="hljs-string">'5', <span class="hljs-string">'3.5'), 
(<span class="hljs-keyword">NULL, <span class="hljs-string">'鲜花', <span class="hljs-string">'18', <span class="hljs-string">'15'), 
(<span class="hljs-keyword">NULL, <span class="hljs-string">'甜点', <span class="hljs-string">'25', <span class="hljs-string">'12.5'), 
(<span class="hljs-keyword">NULL, <span class="hljs-string">'玩具', <span class="hljs-string">'55', <span class="hljs-string">'45'), 
(<span class="hljs-keyword">NULL, <span class="hljs-string">'钱包', <span class="hljs-string">'285', <span class="hljs-string">'195');</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code>

<code class="hljs asciidoc has-numbering"><span class="hljs-header">mysql> select * from product;
+----+--------+----------------+--------+
<span class="hljs-header">| id | name   | original_price | price  |
+----+--------+----------------+--------+
|  1 | 雪糕   |           5.00 |   3.50 |
|  2 | 鲜花   |          18.00 |  15.00 |
|  3 | 甜点   |          25.00 |  12.50 |
|  4 | 玩具   |          55.00 |  45.00 |
<span class="hljs-header">|  5 | 钱包   |         285.00 | 195.00 |
+----+--------+----------------+--------+
5 rows in set (0.00 sec)</span></span></span></code> 

2.互换original_price与price的值

新手可能会使用以下方法进行互换

<code class="hljs sql has-numbering"><span class="hljs-operator"><span class="hljs-keyword">update product <span class="hljs-keyword">set original_price=price,price=original_price;</span></span></span></code>

• 1

• 1

但这样执行的结果只会使original_price与price的值都是price的值，因为update有顺序的，
先执行original_price=price , original_price的值已经更新为price，
然后执行price=original_price，这里相当于没有更新。

执行结果：

<code class="hljs asciidoc has-numbering"><span class="hljs-header">mysql> select * from product;
+----+--------+----------------+--------+
<span class="hljs-header">| id | name   | original_price | price  |
+----+--------+----------------+--------+
|  1 | 雪糕   |           5.00 |   3.50 |
|  2 | 鲜花   |          18.00 |  15.00 |
|  3 | 甜点   |          25.00 |  12.50 |
|  4 | 玩具   |          55.00 |  45.00 |
<span class="hljs-header">|  5 | 钱包   |         285.00 | 195.00 |
+----+--------+----------------+--------+
5 rows in set (0.00 sec)

mysql> update product set original<span class="hljs-emphasis">_price=price,price=original_price;
Query OK, 5 rows affected (0.00 sec)
Rows matched: 5  Changed: 5  Warnings: 0

<span class="hljs-header">mysql> select * from product;
+----+--------+----------------+--------+
<span class="hljs-header">| id | name   | original_price | price  |
+----+--------+----------------+--------+
|  1 | 雪糕   |           3.50 |   3.50 |
|  2 | 鲜花   |          15.00 |  15.00 |
|  3 | 甜点   |          12.50 |  12.50 |
|  4 | 玩具   |          45.00 |  45.00 |
<span class="hljs-header">|  5 | 钱包   |         195.00 | 195.00 |
+----+--------+----------------+--------+
5 rows in set (0.00 sec)</span></span></span></span></span></span></span></code>

正确的互换方法如下：

<code class="hljs avrasm has-numbering">update product as a, product as b <span class="hljs-keyword">set a<span class="hljs-preprocessor">.original_price=b<span class="hljs-preprocessor">.price, a<span class="hljs-preprocessor">.price=b<span class="hljs-preprocessor">.original_price where a<span class="hljs-preprocessor">.id=b<span class="hljs-preprocessor">.id<span class="hljs-comment">;</span></span></span></span></span></span></span></span></code>

执行结果：

<code class="hljs asciidoc has-numbering"><span class="hljs-header">mysql> select * from product;
+----+--------+----------------+--------+
<span class="hljs-header">| id | name   | original_price | price  |
+----+--------+----------------+--------+
|  1 | 雪糕   |           5.00 |   3.50 |
|  2 | 鲜花   |          18.00 |  15.00 |
|  3 | 甜点   |          25.00 |  12.50 |
|  4 | 玩具   |          55.00 |  45.00 |
<span class="hljs-header">|  5 | 钱包   |         285.00 | 195.00 |
+----+--------+----------------+--------+
5 rows in set (0.00 sec)

mysql> update product as a, product as b set a.original<span class="hljs-emphasis">_price=b.price, a.price=b.original_price where a.id=b.id;
Query OK, 5 rows affected (0.01 sec)
Rows matched: 5  Changed: 5  Warnings: 0

<span class="hljs-header">mysql> select * from product;
+----+--------+----------------+--------+
<span class="hljs-header">| id | name   | original_price | price  |
+----+--------+----------------+--------+
|  1 | 雪糕   |           3.50 |   5.00 |
|  2 | 鲜花   |          15.00 |  18.00 |
|  3 | 甜点   |          12.50 |  25.00 |
|  4 | 玩具   |          45.00 |  55.00 |
<span class="hljs-header">|  5 | 钱包   |         195.00 | 285.00 |
+----+--------+----------------+--------+
5 rows in set (0.00 sec)</span></span></span></span></span></span></span></code>