国产精品日韩欧美久久综合,久久久久久亚洲精品成人,久久久久国产一区二区

1.創建表及記錄用于測試

CREATE?TABLE?`product`?(?`id`?int(10)?unsigned?NOT?NULL?AUTO_INCREMENT?COMMENT?'產品id',?`name`?varchar(50)?NOT?NULL?COMMENT?'產品名稱',?`original_price`?decimal(5,2)?unsigned?NOT?NULL?COMMENT?'原價',?`price`?decimal(5,2)?unsigned?NOT?NULL?COMMENT?'現價',?PRIMARY?KEY?(`id`) )?ENGINE=InnoDB?DEFAULT?CHARSET=utf8;INSERT?INTO?`product`?(`id`,?`name`,?`original_price`,?`price`)?VALUES?(NULL,?'雪糕',?'5',?'3.5'),? (NULL,?'鮮花',?'18',?'15'),? (NULL,?'甜點',?'25',?'12.5'),? (NULL,?'玩具',?'55',?'45'),? (NULL,?'錢包',?'285',?'195');

mysql&gt;?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?| +----+--------+----------------+--------+5?rows?in?set?(0.00?sec)

2.互換original_price與price的值

新手可能會使用以下方法進行互換

update?product?set?original_price=price,price=original_price;

但這樣執行的結果只會使original_price與price的值都是price的值，因為update有順序的，
先執行original_price=price , original_price的值已經更新為price，
然后執行price=original_price，這里相當于沒有更新。

執行結果：

mysql&gt;?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?| +----+--------+----------------+--------+5?rows?in?set?(0.00?sec) mysql>?update?product?set?original_price=price,price=original_price; Query?OK,?5?rows?affected?(0.00?sec) Rows?matched:?5??Changed:?5??Warnings:?0mysql>?select?*?from?product; +----+--------+----------------+--------+|?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?||??5?|?錢包???|?????????195.00?|?195.00?| +----+--------+----------------+--------+5?rows?in?set?(0.00?sec)

正確的互換方法如下：

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

執行結果：

mysql&gt;?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?| +----+--------+----------------+--------+5?rows?in?set?(0.00?sec) mysql>?update?product?as?a,?product?as?b?set?a.original_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:?0mysql>?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?| +----+--------+----------------+--------+5?rows?in?set?(0.00?sec)

本文講解了關于mysql互換表中兩列數據方法，更多先關內容請關注php’中文網。