mysql - 如何在MySQL中添加选择时不存在的行

Question

我创建了数据库表:

CREATE TABLE IF NOT EXISTS `highscores` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `account_id` int(11) NOT NULL DEFAULT '0',
  `vocation` varchar(255) NOT NULL,
  `kills` int(11) NOT NULL DEFAULT '0',
  `deaths` int(11) NOT NULL DEFAULT '0',
  `attempts` int(11) NOT NULL DEFAULT '0',
  `monster` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

现在我想使用 LUA 语言来插入和更新该表中的内容:

function highscore(accID, monster, vocation)
print("ERROR HERE?")
local results = db.storeQuery("SELECT 'id' FROM 'highscores' WHERE 'account_id' = "..accID.." AND 'vocation' = "..vocation.." AND 'monster' = "..monster)
print("OR ERROR HERE?")
    if not results then
        db.query("INSERT INTO highscores('account_id', 'attempts', 'kills', 'deaths', 'vocation', 'monster') VALUES ("..accID..","..(0)..","..(0)..","..(0)..","..vocation..","..monster..")")
    end
print()

该功能仍在继续，但我已经遇到了问题。我收到错误两次，尝试 SELECT 和尝试 INSERT。它说我的语法与MySQL版本不对应...

也尝试了这个而不是上面的。同样的问题，语法错误:

db.query("INSERT INTO 'highscores'('account_id', 'vocation', 'kills', 'deaths', 'attempts', 'monster') SELECT * FROM (SELECT "..accID..","..vocation..","..(0)..","..(0)..","..(0)..","..monster.." WHERE NOT EXISTS ( SELECT 'account_id' FROM 'highscores' WHERE 'account_id' = "..accID.." AND 'vocation' = "..vocation.." AND 'monster' = "..monster..") LIMIT 1;")

即使我手动插入正确的值，它也应该自动插入。然后我尝试选择它，它给了我语法错误。

我使用任何其他表都没有这个问题。

Answer 1

我认为你的引用位置不对；而不是

"SELECT 'id' FROM 'highscores' WHERE 'account_id' = "..accID.." AND 'vocation' = "..vocation
.." AND 'monster' = "..monster

你可能应该有:

([[SELECT id FROM highscores WHERE account_id = %s AND vocation = "%s" AND monster = "%s"]])
:format(accID, vocation, monster)

在您的情况下，您将有 WHERE 'account_id' = 123 AND ' Vocation' = Something 而它可能应该是 WHERE account_id = 123 AND Vocation = 'something' >(假设 accID 只有数字值)。