mysql交换两行或两列的值

参考：

mysql left join中where和on条件的区别

一、交换两列的值（列名不变）

新建数据库和表，并插入数据

CREATE DATABASE test;

USE test;

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');

查询：

SELECT *
FROM product;

按别名查询：

#
SELECT *
FROM product AS a, product AS b
WHERE a.id = b.id;

交换两列的值（列名不变）：

# 交换mysql的两列的值（列名不变）
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不变）

新建数据库和表，并插入数据

CREATE DATABASE test;

USE test;

CREATE TABLE `rules` (
  `rule_id`   INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `rule_name` VARCHAR(255)     NOT NULL,
  `priority`  INT(11)                   DEFAULT NULL,
  PRIMARY KEY (`rule_id`)
);

INSERT INTO rules
(rule_id, rule_name, priority)
VALUES
  (1, 'Take one bread', 10),
  (2, 'Drink water', 20),
  (3, 'Eat candy', 30),
  (4, 'Wash hand', 40),
  (5, 'Give charity', 50);

查询：

SELECT * FROM rules;

按照别名和id查询：

SELECT *
FROM
  rules AS rule1
  JOIN rules AS rule2 ON
                        (rule1.rule_id = 1 AND rule2.rule_id = 4)
                        OR (rule1.rule_id = 4 AND rule2.rule_id = 1);

交换两行的值（除了id）

UPDATE
    rules AS rule1
    JOIN rules AS rule2 ON
                          (rule1.rule_id = 1 AND rule2.rule_id = 4)
                          OR (rule1.rule_id = 4 AND rule2.rule_id = 1)
SET
  rule1.priority  = rule2.priority,
  rule2.priority  = rule1.priority,
  rule1.rule_name = rule2.rule_name,
  rule2.rule_name = rule1.rule_name;

再次查询：

SELECT * FROM rules;

可看到两行已完成了交换。