mysql - REPLACE INTO 与 SELECT 条件

Question

我有表:

mysql> desc dialog;
+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| id        | int(11)          | NO   | PRI | NULL    | auto_increment |
| uid1      | int(11)          | NO   | MUL | NULL    |                |
| uid2      | int(11)          | NO   | MUL | NULL    |                |
| mid       | int(11)          | NO   |     | NULL    |                |
| anonym_id | int(10) unsigned | NO   |     | NULL    |                |
+-----------+------------------+------+-----+---------+----------------+

和同一个:

mysql> desc dialogs;
+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| id        | int(11)          | NO   | PRI | NULL    | auto_increment |
| uid1      | int(11)          | NO   | MUL | NULL    |                |
| uid2      | int(11)          | NO   | MUL | NULL    |                |
| mid       | int(11)          | NO   |     | NULL    |                |
| anonym_id | int(10) unsigned | NO   | MUL | NULL    |                |
+-----------+------------------+------+-----+---------+----------------+

第一个 - 对话框有一个重复的值，我在第二个 - 对话框中不需要，结构是相同的，除了对话框表中的唯一索引:

mysql> show index from dialogs;
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| dialogs |          0 | PRIMARY   |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| dialogs |          0 | uid1_uid2 |            1 | uid1        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| dialogs |          0 | uid1_uid2 |            2 | uid2        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| dialogs |          1 | uid2      |            1 | uid2        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| dialogs |          1 | anonym_id |            1 | anonym_id   | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.00 sec)

我找到了从对话框中插入对话框而无需重复键的方法，但是一个:

REPLACE INTO dialogs SELECT * FROM dialog;

工作起来很有魅力，但是 - 我需要测试 IF uid2>uid1 然后将它们切换反之亦然并插入 uid1=uid2 和 uid2=uid1。有没有一种解决方案可以做几乎相同的事情，但是这个子句有 1 个查询？我试过类似的东西，但是这个查询执行失败:

REPLACE INTO dialogs 
    SELECT IF uid1<uid2 THEN * 
           ELSE id, uid2, uid1, mid, anonym_id 
END FROM dialog;

如果是这样 - 请发布一个示例 - 我会试试看。

谢谢。

Answer 1

代替 REPLACE INTO 这将增加您的 ID，您可以 use INSERT IGNORE将它们留在原地(如果已经存在)。至于uid1,uid2的取值切换，在CASE中设置。

INSERT IGNORE INTO dialogs (id, uid1, uid2, mid, anonym_id)
  SELECT
    id,
    /* First prefer uid1, then the opposite for uid2 */
    CASE WHEN uid1 < uid2 THEN uid1 ELSE uid2 END,
    CASE WHEN uid1 < uid2 THEN uid2 ELSE uid1 END,
    mid,
    anonym_id
  FROM dialog

A little more on the difference between REPLACE and INSERT IGNORE ...