php select from multi dual

Question

我想插入一些数据到数据库中。在插入过程中，我首先要做一个判断:如果标题在table1和table2中不存在，则插入。如何写出这个双重句呢？是这样的吗？或者..。谢谢。

mysql_query("
INSERT INTO table1 title 
SELECT '".$title."' 
FROM dual 
WHERE not exists (
    SELECT title 
    FROM table1 
    WHERE table1.title = '".$title."'
) AND (
    SELECT title 
    FROM table2
    WHERE table2.title = '".$title."'
) ");

Answer 1

首先，MySQL不使用dual表。只需直接选择该值即可。通常，要插入到表中，只需使用

INSERT INTO table1(title) SELECT 'something'; # or
INSERT INTO table1(title) VALUES ('something');

但是，下面使用了一个虚构的表(别名为TT)，以便能够对其他表使用左连接。

mysql_query("
    INSERT INTO table1(title)
    SELECT theTitle
    FROM (SELECT '".$title."' theTitle) TT
    LEFT JOIN table1 ON table1.title = TT.theTitle
    LEFT JOIN table2 ON table2.title = TT.theTitle
    WHERE table1.title is null and table2.title is null
");

您也可以只填写NOT EXISTS子句

mysql_query("
    INSERT INTO table1(title)
    SELECT '".$title."'
    WHERE NOT EXISTS (SELECT * FROM table1 WHERE title = '".$title."')
      AND NOT EXISTS (SELECT * FROM table2 WHERE title = '".$title."')
");

但我更喜欢前者，因为这需要你使用$title 3次。

Answer 2

mysql_query("INSERT INTO table1 title 
               SELECT '".$title."' FROM dual 
               WHERE not exists (SELECT title FROM table1 WHERE table1.title = '".$title."') AND 
                     not exists (SELECT title FROM table2 WHERE table2.title = '".$title."') ");

查询中缺少第二个not exists。

Answer 3

有联盟的人吗？

mysql_query("
INSERT INTO table1 title 
SELECT '".addSlashes($title)."' 
FROM dual 
WHERE not exists (
   SELECT title 
   FROM table1 
   WHERE title = '".addSlashes($title)."'
   UNION
   SELECT title 
   FROM table2 
   WHERE title = '".addSlashes($title)."'
) ");