主页 / dba / 问题 / 4328
Accepted
Mokus
Asked: 2011-08-05 08:39:16 +0800 CST

将数据库数据转移到另一列

  • 772

我有下表

id  |   ev_id   | ev_loc_id |   a   |   b   | prev_a    |   prev_b

如果表中不存在 (ev_id AND ev_loc_id)，那么我就插入它们，否则我将把 a 放到 prev_a，将 b 放到 prev_b。

我通过选择解决了这个问题：

SELECT a, b FROM tb WHERE ev_id=XX AND ev_loc_id=XXX

如果数组为空而不是我插入的新数据，否则我将 a、b 和选定的值替换为 prev_a、prev_b

有最省时的解决方案吗？

mysql select

1 个回答

  1. Best Answer

    您可以执行以下操作：

    插入 ... 在重复密钥更新时

    您可以执行以下操作

    • 使 prev_a 假定 a 的值
    • 使 prev_b 假定为 b 的值
    • 让 a 假设新 a 的值
    • 使 b 取新 b 的值

    以下是为该测试创建示例数据的语句：

    use test
DROP TABLE IF EXISTS ev;
CREATE TABLE ev
(
    id INT NOT NULL AUTO_INCREMENT,
    ev_id INT NOT NULL,
    ev_loc_id INT NOT NULL,
    a FLOAT DEFAULT 0.0,
    b FLOAT DEFAULT 0.0,
    prev_a FLOAT DEFAULT 0.0,
    prev_b FLOAT DEFAULT 0.0,
    PRIMARY KEY (id),
    UNIQUE KEY (ev_id,ev_loc_id)
);
INSERT INTO ev (ev_id,ev_loc_id,a,b) VALUES
(1,2,5.0,7.0),
(1,3,9.0,3.0),
(2,2,7.0,9.0),
(2,3,3.0,9.0);
SELECT * FROM ev;
INSERT INTO ev (ev_id,ev_loc_id,a,b) VALUES
(1,2,8.0,4.0) ON DUPLICATE KEY
UPDATE prev_a=a,prev_b=b,a=VALUES(a),b=VALUES(b);
SELECT * FROM ev WHERE ev_id=1 AND ev_loc_id=2;

    这是输出：

    mysql> use test
DROP TABLE IF EXISTS ev;
Database changed
mysql> DROP TABLE IF EXISTS ev;
(
    id INT NOT NULL AUTO_INCREMENT,
    ev_id INT NOT NULL,
    ev_loc_id INT NOT NULL,
    a FLOAT DEFAULT 0.0,
    b FLOAT DEFAULT 0.0,
    prev_a FLOAT DEFAULT 0.0,
    prev_b FLOAT DEFAULT 0.0,
    PRIMARY KEY (id),
    UNIQUE KEY (ev_id,ev_loc_id)
);
INSERT INTO ev (ev_id,ev_loc_id,a,b) VALUES
(1,2,5.0,7.0),
(1,3,9.0,3.0),
(2,2,7.0,9.0),
(2,3,3.0,9.0);
SELECT * FROM ev;
INSERT INTO ev (ev_id,ev_loc_id,a,b) VALUES
(1,2,8.0,4.0) ON DUPLICATE KEY
UPDATE prev_a=a,prev_b=b,a=VALUES(a),b=VALUES(b);
SELECT * FROM ev WHERE ev_id=1 AND ev_loc_id=2;

Query OK, 0 rows affected (0.07 sec)

mysql> CREATE TABLE ev
    -> (
    ->     id INT NOT NULL AUTO_INCREMENT,
    ->     ev_id INT NOT NULL,
    ->     ev_loc_id INT NOT NULL,
    ->     a FLOAT DEFAULT 0.0,
    ->     b FLOAT DEFAULT 0.0,
    ->     prev_a FLOAT DEFAULT 0.0,
    ->     prev_b FLOAT DEFAULT 0.0,
    ->     PRIMARY KEY (id),
    ->     UNIQUE KEY (ev_id,ev_loc_id)
    -> );
Query OK, 0 rows affected (0.07 sec)

mysql> INSERT INTO ev (ev_id,ev_loc_id,a,b) VALUES
    -> (1,2,5.0,7.0),
    -> (1,3,9.0,3.0),
    -> (2,2,7.0,9.0),
    -> (2,3,3.0,9.0);
Query OK, 4 rows affected (0.07 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM ev;
+----+-------+-----------+------+------+--------+--------+
| id | ev_id | ev_loc_id | a    | b    | prev_a | prev_b |
+----+-------+-----------+------+------+--------+--------+
|  1 |     1 |         2 |    5 |    7 |      0 |      0 |
|  2 |     1 |         3 |    9 |    3 |      0 |      0 |
|  3 |     2 |         2 |    7 |    9 |      0 |      0 |
|  4 |     2 |         3 |    3 |    9 |      0 |      0 |
+----+-------+-----------+------+------+--------+--------+
4 rows in set (0.00 sec)

mysql> INSERT INTO ev (ev_id,ev_loc_id,a,b) VALUES
    -> (1,2,8.0,4.0) ON DUPLICATE KEY
    -> UPDATE prev_a=a,prev_b=b,a=VALUES(a),b=VALUES(b);
Query OK, 2 rows affected (0.07 sec)

mysql> SELECT * FROM ev WHERE ev_id=1 AND ev_loc_id=2;
+----+-------+-----------+------+------+--------+--------+
| id | ev_id | ev_loc_id | a    | b    | prev_a | prev_b |
+----+-------+-----------+------+------+--------+--------+
|  1 |     1 |         2 |    8 |    4 |      5 |      7 |
+----+-------+-----------+------+------+--------+--------+
1 row in set (0.00 sec)

    试试看 ！！！

    • 1

相关问题