mysql - 使用nodejs检查电子邮件是否存在于mysql数据库中

Question

当用户输入表单时，需要检查 mysql 中是否存在电子邮件。从表单 requser.email 中，用户将通过电子邮件输入表单中。如何将 requuser.email 与数据库进行比较以找到匹配项。

 router.post('/signup', function(req, res, next) {
    var requser = req.body.data.user;
  var hash = bcrypt.hashSync(req.body.data.user.password)
  var str = "";

  /*email check start*/

  /*email check end*/
  switch (requser.role) {
    case "approver":
      str = 'INSERT INTO users (name, email, password,role) values("' + requser.name + '","' + requser.email + '","' + hash + '","approver")';
      break;
    case "user":
      str = 'INSERT INTO users (name, email, password,role) values("' + requser.name + '","' + requser.email + '","' + hash + '","user")';
      break;
    case "admin":
      str = 'INSERT INTO users (name, email, password,role) values("' + requser.name + '","' + requser.email + '","' + hash + '","admin")';
      break;
  }
  if (!str) {
    return res.json({
      "status": "error",
      "data": {
        "redirect": "/"
      }
    });
  }
  connection.query(str, function(error, rows) {
    if (error) {
      console.log("error ocurred", error);
    }
    //console.log(rows);
    res.json({
      "status": error ? "error" : "success",
      "data": rows || ""
    })
  });
});

Answer 1

您可以编写 SELECT 1 查询来获取包含该电子邮件地址的记录，也可以在电子邮件字段上定义 UNIQUE CONSTRAINT，这样插入就会失败。

Here's UNIQUE CONSTRAINT 的示例以及 SELECT 1 的示例:

SELECT 1 FROM users WHERE email = <email>;