MYSQL'IN'和'COUNT'问题

Question

I Have 2 SQL queries that I might not use correct:

SELECT 
    `contract_id`,
    `contract_title`,
    `contract_description`,
    `contract_postalcode`,
FROM
    `gmw_contracts` 
WHERE contract_title COLLATE UTF8_GENERAL_CI REGEXP "test" 
    OR contract_description COLLATE UTF8_GENERAL_CI REGEXP "test" 
    AND contract_postalcode IN (
        53111,53113,53129,53175,53225,53227,53229,53757
    ) ;

SELECT 
    COUNT(`contract_postalcode`) 
FROM
    `gmw_contracts` 
WHERE `contract_title` COLLATE UTF8_GENERAL_CI REGEXP "test" 
    OR `contract_description` COLLATE UTF8_GENERAL_CI REGEXP "test" 
    AND `contract_postalcode` IN (
        53111,53113,53129,53175,53225,53227,53229,53757
    ) ;

The first query selects rows with given parameters like keyword and postalcode etc, but i don't get the desired result. Seems like the postalcode thing is completely ignored.

The second query should count it so i can paginate the result in PHP (BUT I don't want to count PHP)

THE SOLUTION for the first query: I can use HAVING instead of AND. But I need to do it with the counting query too. COUNT and HAVING dont work together in this exapmle.

U got any hints or solutions for me?

Answer 1

Your first problem is caused by the combination of OR and AND:

... WHERE ... OR ... AND ...

Here the AND clause will probably not do what you want / expect as the conditions return true if any of the OR conditions is met.

You probably want:

... WHERE (... OR ...) AND ...