Can I make WHERE col LIKE '%' select NULL values too?

I am trying to make a selection from开发者_如何学运维 a MySQL database using the syntax WHERE col LIKE '%' but I want to select even NULL values with that query. Is there some command/operator that can select any data, including null?

Use the IFNULL operator

WHERE IFNULL(xxx, '') LIKE '%'

SELECT * FROM table WHERE (job LIKE '%' OR job IS NULL)

If you want column1 LIKE '%' and want column1 IS NULL, why not just drop the WHERE clause?

Try:

SELECT * FROM TABLE;

Here's what I tried:

mysql> create table foo (a char(30));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into foo values ( '' );
Query OK, 1 row affected (0.01 sec)

mysql> insert into foo values ( NULL );
Query OK, 1 row affected (0.00 sec)

mysql> select * from foo;
+------+
| a    |
+------+
|      |
| NULL |
+------+
2 rows in set (0.00 sec)

mysql> select * from foo where a like '%' or a is null;
+------+
| a    |
+------+
|      |
| NULL |
+------+
2 rows in set (0.00 sec)

mysql> select * from foo where ifnull (a, 1) like '%';
+------+
| a    |
+------+
|      |
| NULL |
+------+
2 rows in set (0.00 sec)

mysql> 

After all, IFNULL(column1, 1) LIKE '%' is effectively a NO-OP ...

Use COALESCE instead of IFNULL if you want to use it with java createQuery

WHERE COALESCE (xxx, '') LIKE '%'

This statement returns all rows EXCEPT rows which have NULL in column1

SELECT * FROM table WHERE column1 LIKE '%'

To get all rows including rows with NULL in column1, use this:

SELECT * FROM table WHERE IFNULL(column1,1) LIKE '%'

from the MySQL documentation:

IFNULL(expr1,expr2)

If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns expr2. IFNULL() returns a numeric or string value, depending on the context in which it is used.

There's one caveat however: If you have an index on column1, it won't be used in this query, so things could get slow in larger tables...