mysql互換表中兩列資料

標籤：header   cannot   update   例子   nta   特殊   針對   ted   特殊情況   

在開發過程中，有時由於業務等需要把一個表中的兩列資料進行交換。

解決方案

使用update命令，這完全得益於MySQL SQL命令功能的強大支援。

表格中原來資料類似如下：

select * from product; +----+--------+| 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 ||  5 | 錢包   |         285.00 | 195.00 |

現在要求互換original_price與price的值，使用如下方法：

 update product as a, product as b set a.original_price=b.price, a.price=b.original_price where a.id=b.id;

 

驗證一下：

select * from product; +----+--------+--------+| 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 ||  5 | 錢包   |         195.00 | 285.00 |

擴充問題

上面直接使用update交換一個表中兩列（自然是同一資料類型）的例子有一個特殊情況，其實也正是我要解決的真正問題。具體地說，我們的使用者在初期準備的部分資料中對於表中的兩列資料弄反了。但是，對於以後輸入的資料卻沒有弄反。這種情況下，我們需要找出前面弄反的記錄範圍，然後針對這部分記錄實施上面的操作。但遺憾的是，MySQL中的update語句是不支援limit子句的。官方正規的描述如下：

For the multiple-table syntax, UPDATE updates rows in each table named in table_references that satisfy the conditions. Each matching row is updated once, even if it matches the onditions multiple times. For multiple-table syntax, ORDER BY and LIMIT cannot be used.

我實驗了一下，的確如此。

無奈我們只能使用變通的辦法。其中一個方法是，先把上面指定範圍的記錄選擇出來並產生到一個暫存資料表中，然後針對原表和這個暫存資料表使用update語句實現交換上面範圍記錄中各個對應欄位值的目的。

但這裡有一個小插曲是：MySQL不直接支援類似於MS SQL Server的select into newtable這樣的語句。不過，我們可以使用變通的其他語句實現，如下：

Create table tmp(Select * from mv_person2 limit 0,10);

 

上面語句的含義是，產生一個新表tmp，該表中的欄位及值是另一個表mv_person2中從第1到第10條記錄資料。這樣便實現了上面產生一個暫存資料表之目的。

有了上面的暫存資料表tmp，再使用update語句實現交換表格mv_person2中指定範圍（例如從第1到第10條記錄資料）兩列便輕鬆了。答案如下：

update mv_person2 as a, tmp as b set a.original_price=b.price, a.price=b.original_price where a.id=b.id;

 

從而間接實現了交換一個表中某指定範圍內兩列資料值之目的。

mysql互換表中兩列資料