主页 / dba / 问题 / 20667
Accepted
Mike Purcell
Asked: 2012-07-11 13:33:07 +0800 CST

MySQL - 插入...选择...联合

  • 772

我遇到了一个相当复杂的查询的问题，我需要将一个表中的 ID（基于繁重的标准）存储到一个狭窄的备用表中以供将来参考，但我一直遇到错误的 sql 构造错误。

如果我自己运行以下查询，它可以正常工作：

(SELECT DISTINCT a.id FROM accounts AS a
WHERE a.createdate >= date_sub(now(), INTERVAL 1 YEAR)
AND a.email NOT LIKE '%gmail.com')
UNION DISTINCT
(SELECT DISTINCT a.id FROM accounts AS a
WHERE a.createdate < date_sub(now(), INTERVAL 1 YEAR)
AND a.email NOT LIKE '%yahoo.com') AND a.email NOT IN
(SELECT email FROM inactive_clients));

但是，如果我尝试像这样运行相同的查询：

INSERT INTO my_backup_table (account_id) VALUES ($aboveQuery);

我收到以下错误：

MySQL server version for the right syntax to use near 'UNION DISTINCT (SELECT DISTINCT a.id FROM accounts at line 1

我尝试了任意数量的括号排列，但仍然收到相同的错误。

mysql insert

2 个回答

  1. Best Answer

    您不能使用INSERT INTO tblname (...) VALUES ...语法

    您必须使用INSERT INTO tblname (...) SELECT ...语法

    尝试以下操作：

    INSERT INTO my_backup_table (account_id)
SELECT id FROM
(
    (SELECT DISTINCT a.id FROM accounts AS a
    WHERE a.createdate >= date_sub(now(), INTERVAL 1 YEAR)
    AND a.email NOT LIKE '%gmail.com')
    UNION DISTINCT
    (SELECT DISTINCT a.id FROM accounts AS a
    WHERE a.createdate < date_sub(now(), INTERVAL 1 YEAR)
    AND a.email NOT LIKE '%yahoo.com') AND a.email NOT IN
    (SELECT email FROM inactive_clients))
) A;
    • 5

  2. Rolando 回答了您关于INSERT语法的问题，但我想就该查询的性能添加一些额外的评论。首先，你区分了太多次。正如暗示的那样，您实际上不需要DISTINCT此查询中的任何地方。我不确定优化器是否会优化掉不必要的 s，但请考虑一下：UNIONDISTINCTDISTINCT

    mysql> SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1;
+---+
| 1 |
+---+
| 1 |
| 1 |
| 1 |
+---+
3 rows in set (0.00 sec)

mysql> SELECT 1 UNION ALL SELECT 1 UNION SELECT 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

    此外，NOT IN (SELECT)将对边界查询中的每一行执行内部查询，效率非常低。我每周都会与团队中的开发人员遇到这种情况，每次都会出现问题。相反，将查询重写为LEFT JOIN. 您还将从 unindexable 中获得性能打击LIKE %domain.com。

    另一个考虑因素是，由于在 MySQL 的默认事务隔离级别 REPEATABLE READ 中此查询可能需要“一段时间”，因此SELECT语句中的表中的许多行将由于间隙锁而被锁定，因此无法更新。出于这个原因，我建议您在 READ-COMMITTED 结合基于 MIXED 或 ROW 的复制运行此查询。

    SET BINLOG_FORMAT=MIXED;
SET TRANSACTION ISOLATION LEVEL READ-COMMITTED;

INSERT INTO my_backup_table (account_id)
  SELECT a.id
  FROM accounts AS a
  WHERE a.createdate >= date_sub(now(), INTERVAL 1 YEAR)
  AND a.email NOT LIKE '%gmail.com')
  UNION 
  SELECT a.id 
  FROM accounts AS a
  LEFT JOIN inactive_clients ic USING(email)
  WHERE a.createdate < date_sub(now(), INTERVAL 1 YEAR)
    AND a.email NOT LIKE '%yahoo.com'
    AND ic.email IS NULL;

 SET TRANSACTION ISOLATION LEVEL (whatever it was before);
 SET BINLOG_FORMAT=(whatever it was before);

    我希望这能有所帮助。

    • 2

相关问题