mysql - Select Exists 在 mysql 中总是返回 true

Question

我正在尝试制作一个登录表单，在此之前我必须注册用户。但这段代码总是说用户已经存在，即使事实并非如此。我做错了什么？

connection.query("select exists (select * from demoApp where username = '" + username + " ')", function (error, result) {
    if (error) {
        LOG.error(error);
        return;
    }
    else {
        LOG.info(result);
        if (result) {
            LOG.info("Username already exists!");
        }

        else {

            connection.query("INSERT INTO demoApp (username, password) VALUES( '" + username + "', '" + password + "')", function (error, result) {
                if (error) {
                    LOG.error(error);

                    return;
                }
                else {
                    LOG.info("Username added.");

                }
            });
        }
    }
});

};

Answer 1

您在问题标题“MySQL 总是返回 true...”中所说的内容不正确；您稍后在评论中说 MySQL 返回 0 或 1

您的 JavaScript 在逻辑上有缺陷。你说的是 if(result) 但 result 远不仅仅是一个简单的整数 0 或 1，所以说 if(result) 将有效地测试它是否为空，而这从来都不是，因此“永远正确”

你的代码应该看起来更像这样(我还修复了你的旧代码容易受到注入(inject)黑客攻击的问题:

//alias the result column using AS xxx to make it easier to refer to in js
//ALWAYS use parameters (WHERE username = ?) to pass values from the user into the db
let sql = "select exists (select * from demoApp where username = ?) as userExists";
connection.query(sql, [username],
function (error, result) {
    if (error) {
        LOG.error(error);
        return;
    }
    else {
        LOG.info(result);
        if (result[0].userExists == 1) { //no harm in being explicit. Result is a collection of rows with names of columns, index it to get the first and specify the column (which is really hard to do with name of you don't alias your query result
            LOG.info("Username already exists!");
        }
...