MySQL NOT REGEXP different result

Question

I am very confused by this question. As I know the | means or in MySQL regexp, but I got two different result in these two queries.

SELECT distinct city from station
where city not regexp '^[aeiou]' or city not regexp '[aeiou]$';

and

SELECT distinct city from station
where city not regexp '^[aeiou]|[aeiou]$';

Does anyone know what happened to my code?

Answer 1

Your two WHERE clauses are doing different things.

where city not regexp '^[aeiou]' or city not regexp '[aeiou]$';

This one says "city does not match a vowel at the beginning of the string, or city does not match a vowel at the end of the string".

where city not regexp '^[aeiou]|[aeiou]$';

The one says "city begins with a vowel or a vowel, and then there's the end of the string"

What you're trying to do is:

where city not regexp '(^[aeiou])|([aeiou]$)'

I would suggest that the original version is the most readable of all.

If you are combining the two expressions because you think that one regexp match will perform faster than two, that's probably not the case. Don't optimize unless you have measured the speed of both and have proven that one performs faster than the other.