我有一个表:
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;
就像一个符咒,但是-我需要测试如果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;
如果是的话——请贴一个例子——我会试试的。
Thx。
您可以使用INSERT IGNORE
将ID保留在原位,而不是使用REPLACE INTO
来增加ID。关于切换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
对CCD_ 5和CCD_。。。