How does MySQL evaluate Text and VarChar fields when compared to booleans?

Question

Like select * from table where text_field;

What would get pulled?

Answer 1

An error.

My best guess is that you want select * from table where text_field is not null.

Answer 2

If you do select * from atable where text_field; , you will get the rows where text_field='1'.

Similar to select * from atable where text_field='1';.

I'm not sure I understand the question.

Answer 3

ceteras is right, WHERE text_field returns true where text_field = '1' (for some obscure reason).

However, pay attention : NULL is not equal to an empty string (NULL is not even equal to NULL !). If you want to select every row where the column text_field is not empty, you would have to do something like this :

SELECT * FROM table WHERE LENGTH(text_field) > 0;
-- or
SELECT * FROM table WHERE text_field <> '';

The last query can use an index if wanted, I am not so sure about the first.