java - 尝试将数据插入 MySQL 时出错

Question

我使用下面的代码在数据插入 MySQL 之前进行检查。但是，我收到如下错误

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE NOT EXISTS (SELECT * FROM movie_title WHERE title = 'ABC')' at line 1
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)

这是我的代码

public void checkAndAdd(String movieTitle) throws Exception {
        // TODO Auto-generated method stub
        DatabaseConnection db=new DatabaseConnection();
        Connection connect=db.getConnection();
        String sql="INSERT INTO movie_title(title) VALUES (?) WHERE NOT EXISTS (SELECT * FROM movie_title WHERE title = ?)";
        PreparedStatement ps=connect.prepareStatement(sql);
        ps.setString(1,movieTitle);
        ps.setString(2,movieTitle);
        ps.executeUpdate();
        connect.close();
        ps.close(); 

    }   

已编辑

String sql=" IF NOT EXISTS(SELECT * FROM movie_title WHERE title = ?) INSERT INTO movie_title (title) VALUES (?) ";

Answer 1

当您使用 VALUES 关键字时，您不能在 SQL 中放置 WHERE 子句。尝试这样的事情:

IF NOT EXISTS(SELECT 1 FROM movie_title WHERE title = ?)
    INSERT INTO movie_title (title)
    VALUES (?)

我主要使用 SQL Server，所以我不确定这是否对 MySQL 完全有效，但它会是类似的东西。

好的，对于 MySQL，它必须有点不同:

INSERT INTO movie_title (title)
SELECT mt.title
FROM
    (SELECT ? title) mt
WHERE NOT EXISTS (
    SELECT 1 FROM movie_title WHERE title = ?)