java - MySQL 查询错误。检查行是否存在，更新或插入

Question

这个问题已经有答案了:

When to use single quotes, double quotes, and backticks in MySQL (13 个回答)

已关闭 2 年前。

以下查询应检查该行是否存在，如果存在，则更新，如果不存在，则插入。

String sql = "IF EXISTS (SELECT * FROM 'mocha' WHERE uuid = " + uuid + ")" +
        "BEGIN" +
            "UPDATE 'mocha'" +
            "SET join_message = " + message +
            "WHERE uuid ="  + uuid +
        "END" +
        "ELSE" +
        "BEGIN" +
            "INSERT INTO 'mocha' (uuid, join_message, quit_message)" +
            "VALUES ('"  + uuid + ", " + message + " + "" + "')" +
        "END";

我希望能够在一个查询而不是两个查询中完成此操作，这是整个方法:

public void setMessage(UUID uuid, String message) {
    String sql = "IF EXISTS (SELECT * FROM 'mocha' WHERE uuid = " + uuid + ")" +
            "BEGIN" +
                "UPDATE 'mocha'" +
                "SET join_message = " + message +
                "WHERE uuid ="  + uuid +
            "END" +
            "ELSE" +
            "BEGIN" +
                "INSERT INTO 'mocha' (uuid, join_message, quit_message)" +
                "VALUES ('"  + uuid + ", " + message + ", ''" + "')" +
            "END";
    BukkitRunnable r = new BukkitRunnable() {
        @Override
        public void run() {
            System.out.println("Join message was set.");
            try {
                Statement statement = connection.createStatement();
                statement.executeUpdate(sql);
            } catch(SQLException e) {
                e.printStackTrace();
            }
        }
    };

    r.runTaskAsynchronously(instance);
}

但是我收到以下错误:

    com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''mocha' WHERE uuid = 0f9f822c-d7e6-4000-ac05-6ebc17b82f1d)BEGINUPDATE 'mocha'SET' at line 1
[10:18:52 WARN]:        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
[10:18:52 WARN]:        at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
[10:18:52 WARN]:        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
[10:18:52 WARN]:        at java.lang.reflect.Constructor.newInstance(Unknown Source)
[10:18:52 WARN]:        at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
[10:18:52 WARN]:        at com.mysql.jdbc.Util.getInstance(Util.java:408)
[10:18:52 WARN]:        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:943)
[10:18:52 WARN]:        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3970)
[10:18:52 WARN]:        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3906)
[10:18:52 WARN]:        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2524)
[10:18:52 WARN]:        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2677)
[10:18:52 WARN]:        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2545)
[10:18:52 WARN]:        at com.mysql.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1540)
[10:18:52 WARN]:        at com.mysql.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2595)
[10:18:52 WARN]:        at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1468)
[10:18:52 WARN]:        at me.Latte.Models.MochaModel$2.run(MochaModel.java:68)
[10:18:52 WARN]:        at org.bukkit.craftbukkit.v1_11_R1.scheduler.CraftTask.run(CraftTask.java:71)
[10:18:52 WARN]:        at org.bukkit.craftbukkit.v1_11_R1.scheduler.CraftAsyncTask.run(CraftAsyncTask.java:52)
[10:18:52 WARN]:        at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
[10:18:52 WARN]:        at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
[10:18:52 WARN]:        at java.lang.Thread.run(Unknown Source)

我做错了什么？

Answer 1

是的，您在表名 mocha 周围使用单引号而不是反引号。所以你的查询

INSERT INTO 'mocha'

应该是

INSERT INTO `mocha`