Início / dba / Perguntas / 20667
Accepted
Mike Purcell
Asked: 2012-07-11 13:33:07 +0800 CST

MySQL - INSERT INTO ... SELECT ... UNION

  • 772

Estou tendo um problema com uma consulta bastante complexa em que preciso armazenar os IDs de uma tabela (com base em critérios pesados) em uma tabela alternativa estreita para referência futura, mas continuo encontrando um erro de construção de sql ruim.

Se eu executar a seguinte consulta sozinha, ela funcionará bem:

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

No entanto, se eu tentar executar a mesma consulta assim:

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

Estou tendo o erro a seguir:

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

Eu tentei qualquer número de permutações entre parênteses e ainda recebi o mesmo erro.

mysql insert

2 respostas

  1. Best Answer

    Você não pode usar a INSERT INTO tblname (...) VALUES ...sintaxe

    Você deve usar a INSERT INTO tblname (...) SELECT ...sintaxe

    Tente o seguinte:

    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 respondeu sua pergunta sobre a INSERTsintaxe, mas gostaria de acrescentar alguns comentários adicionais sobre o desempenho desta consulta. Primeiro, você está DISTINTO muitas vezes. Na verdade, você não precisa DISTINCTde nenhum lugar nesta consulta, como UNIONimplica DISTINCT. Não tenho certeza se o otimizador eliminará os DISTINCTs desnecessários, mas considere o seguinte:

    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)

    Além disso, NOT IN (SELECT)executará a consulta interna para cada linha na consulta delimitadora e é altamente ineficiente. Eu me deparo com isso semanalmente com desenvolvedores da minha equipe e sempre causa problemas. Em vez disso, reescreva a consulta como um arquivo LEFT JOIN. Você também obterá um impacto no desempenho do unindexable LIKE %domain.com.

    Outra consideração é que, como essa consulta provavelmente levará um "tempo", no nível de isolamento de transação padrão do MySQL de REPEATABLE READ, muitas das linhas nas tabelas na SELECTinstrução serão bloqueadas devido a bloqueios de lacunas, portanto, não serão atualizáveis. Por esse motivo, sugiro que você execute essa consulta em READ-COMMITTED combinada com replicação baseada em MIXED ou 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);

    Espero que isso ajude um pouco.

    • 2

relate perguntas