【例子】输入多个条件或者一个条件或者不输入条件均可以查询
【实现】
根据接收的参数是否为空,进行动态拼接sql语句。
// 多条件查询,动态拼接sql
var sql = 'select * from record_uncontacted where 1=1'
if (req.query.recordNum !== '') sql = sql + " and recordNum= '" + req.query.recordNum + "'"
if (req.query.grade !== '') sql = sql + " and grade= '" + req.query.grade + "'"
if (req.query.subject !== '') sql = sql + " and subject= '" + req.query.subject + "'"
if (req.query.studentsex !== '') sql = sql + " and studentsex= '" + req.query.studentsex + "'"
两个注意点:
1、SQL语句拼接加 where 1=1 的原因:避免在所有条件参数为空时,sql语句语法错误,例如 select * from record_uncontacted where and.....
2、sql语句直接拼接字符串变量会报错
例如,一开始写的是
sql = sql + " and grade= " + req.query.grade
报错:Error: ER_BAD_FIELD_ERROR: Unknown column '一年级' in 'where clause'
然后改成下面这样就可以了
sql = sql + " and grade= '" + req.query.grade + "'"