动态生成MySQL多个where子句

Question

因此用户可以选择他想要过滤列表的类别

当只有一项被选中时一切正常:

        SELECT SQL_CALC_FOUND_ROWS a.id, a.PAnr, b.family, b.articlenr, b.ratio, a.oiltype, a.oiltemp
        FROM testdata_test a, testdata_gear b
        WHERE a.id = b.test_id  AND b.family = 'R660' 
        ORDER BY a.PAnr desc
        LIMIT 0, 10

但是当用户添加更多时:

        SELECT SQL_CALC_FOUND_ROWS a.id, a.PAnr, b.family, b.articlenr, b.ratio, a.oiltype, a.oiltemp
        FROM testdata_test a, testdata_gear b
        WHERE a.id = b.test_id  AND b.family = 'R540' OR  b.family = 'R660' 
        ORDER BY a.PAnr desc
        LIMIT 0, 10

当没有 AND 部分的表格大约有 4000 次时，我得到大约 400 万次点击。如果用户选择另一个，则大约有 800 万次点击。我错过了什么？

Answer 1

将您的查询更改为此，您的查询将不会按预期运行 OR 语句，

SELECT SQL_CALC_FOUND_ROWS a.id, a.PAnr, b.family, b.articlenr, 
        b.ratio, a.oiltype, a.oiltemp
        FROM testdata_test a, testdata_gear b
        WHERE a.id = b.test_id  AND (b.family = 'R540' OR  b.family = 'R660')
        ORDER BY a.PAnr desc
        LIMIT 0, 10

或者

SELECT SQL_CALC_FOUND_ROWS a.id, a.PAnr, b.family, b.articlenr, 
        b.ratio, a.oiltype, a.oiltemp
        FROM testdata_test a, testdata_gear b
        WHERE a.id = b.test_id  AND b.family IN ('R540','R660')
        ORDER BY a.PAnr desc
        LIMIT 0, 10