mysql - LIKE SQL语法中的错误

我正在使用nodeJs和mysql包。

我想在varibale中使用LIKE sql语句。

这是源代码：

var likemobile = '%'+mobile;
var query = "SELECT vtiger_contactaddress.contactaddressid as 'leadid',
                    vtiger_contactaddress.mobile,
                    vtiger_contactaddress.phone
             FROM `vtiger_contactaddress`
             INNER JOIN `vtiger_crmentity`
                 ON vtiger_crmentity.crmid=vtiger_contactaddress.contactaddressid AND
                    vtiger_crmentity.deleted=0 AND
                    vtiger_contactaddress.mobile LIKE "+likemobile+" OR
                    vtiger_contactaddress.phone LIKE "+likemobile;

这是返回的错误：

Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual th
at corresponds to your MySQL server version for the right syntax to use near '%8
8436500 OR vtiger_leadaddress.phone LIKE %88436500' at line 1

最佳答案

如果确实使用node-mysql，则应按照the documentation中的说明运行查询。假设您已经有一个connection对象，则使用绑定变量进行查询变得很简单：

connection.query({
  sql : "SELECT vtiger_contactaddress.contactaddressid as leadid, "
                   + " vtiger_contactaddress.mobile, "
                   + " vtiger_contactaddress.phone "
             + "FROM `vtiger_contactaddress` "
             + "INNER JOIN `vtiger_crmentity` "
             + "  ON vtiger_crmentity.crmid=vtiger_contactaddress.contactaddressid"
             + " AND vtiger_crmentity.deleted=0 AND "
             + "       (vtiger_contactaddress.mobile LIKE concat('%', ?) OR "
             + "       vtiger_contactaddress.phone LIKE concat('%', ?))",
  values: [mobile, mobile]
}, function (error, results, fields) {
  // error will be an Error if one occurred during the query
  // results will contain the results of the query
  // fields will contain information about the returned results fields (if any)
});

这里要注意的事情：


使用绑定变量，防止SQL Injection
concat函数用于在通配符（%）之前为（经过过滤的）输入加上前缀
现在将OR组合在一起的两个查询条件用vtiger_crmentity.deleted=0括起来，这可能是您想要的
您需要在回调函数中编写结果集处理代码，并通过results和fields变量访问数据

关于mysql - LIKE SQL语法中的错误，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/33367805/