List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:January 6 2000 6:23am
Subject:Re: SELECT ... LIKE "%" on NULL column
View as plain text  
The LIKE operator doesn't match NULL column values.  You'd probably
be better off not putting "col_name LIKE '%'" in your query at all
when no specific value for col_name is to be searched for.  Your
query will run more quickly, too, because there will be fewer
conditions in the WHERE clause.


At 2:34 AM +0000 2000-01-06, hypnos wrote:
>I've run into a problem here, but I'm sure I'm
>overlooking something rather similar.
>
>First off, my table declaration:
>
>mysql> DESC headers;
>+-------------+-----------------------+------+-----+---------+------- 
>---------+
>| Field       | Type                  | Null | Key | Default | Extra
>|
>+-------------+-----------------------+------+-----+---------+------- 
>---------+
>| msg_id      | mediumint(8) unsigned |      | PRI | 0       |
>auto_increment |
>| msg_from    | char(255)             |      | MUL |         |
>|
>| msg_to      | char(255)             |      | MUL |         |
>|
>| msg_cc      | char(255)             | YES  |     | NULL    |
>|
>| msg_subject | char(255)             |      | MUL |         |
>|
>+-------------+-----------------------+------+-----+---------+------- 
>---------+
>5 rows in set (0.01 sec)
>
>The problem is the "msg_cc" column, defined as NULL.
>
>This database archives e-mail messages, and everything
>is taken care of automatically.  Mail comes in thru
>sendmail, a copy is made, a cronjob puts it into a
>temporary database, and my custom scripts pull out
>the headers I want (see above) and the message body.
>The headers go in one table (the one above) and
>the message bodies go into another table.
>
>My problem has to do, I believe, with trying to
>use a wildcard on the msg_cc column in a SELECT
>query.  I have PHP scripts to search this database.
>Every field is searchable, and it is possible to
>have search criteria for only one field or for all
>of them, so for any column that doesn't have search
>criteria associated with it is searched with "%".
>
>Okay, here's an example query created from PHP to
>search only on the msg_to field:
>
>mysql> SELECT p1.msg_id,p1.msg_from,p1.msg_to,
>     ->    p1.msg_cc,p1.msg_subject
>     -> FROM headers AS p1, messages AS p2
>     -> WHERE p1.msg_id LIKE '%' AND
>     ->    p1.msg_from LIKE '%' AND
>     ->    p1.msg_to LIKE '%eroticaism%' AND
>     ->    p1.msg_cc LIKE '%' AND
>     ->    p1.msg_subject LIKE '%' AND
>     ->    p2.msg_body LIKE '%' AND
>     ->    p1.msg_id = p2.msg_id
>     -> ORDER BY p1.msg_id;
>Empty set (4.00 sec)
>
>However, if I remove the "p1.msg_cc LIKE '%' AND"
>part from the query, I get 9 rows returned.  I'll
>just paste the first to save space:
>
>+--------+-------------------------------------+--------+-------------+
>| msg_id | msg_to                              | msg_cc | msg_subject |
>+--------+-------------------------------------+--------+-------------+
>|   2124 |                                     | NULL   | ...         |
>+--------+-------------------------------------+--------+-------------+
>
>(I removed the msg_from field above and deleted the
>name/email address to protect the innocent :-)
>
>Anyways, all the fields returned have NULL in the
>msg_cc field.
>
>I want to be able to search the msg_cc field also,
>using the "%" wildcard if no specific search
>criteria are specified.  I know this problem has
>to do with the column being defined as NULL, but it
>isn't reasonable to change it to NOT NULL at this
>point.
>
>As I said, I'm sure I'm overlooking something simple
>here, regarding using wildcard fields on NULL columns.
>I could change my query used in the PHP script, but
>sometimes msg_cc has a value, sometimes it's NULL.
>
>Any suggestions/pointers/etc appreciated.
>
>Thanks
>
>------------------------------------------------------
>hypnos              <mailto:hypnos@stripped>
>
>
>
>--
>---------------------------------------------------------------------
>Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
>posting. To request this thread, e-mail mysql-thread23235@stripped
>
>To unsubscribe, send a message to:
>     <mysql-unsubscribe-paul=snake.net@stripped>


-- 
Paul DuBois, paul@stripped
Thread
SELECT ... LIKE "%" on NULL columnhypnos6 Jan
  • Re: SELECT ... LIKE "%" on NULL columnPaul DuBois6 Jan
    • Re: SELECT ... LIKE "%" on NULL columnhypnos6 Jan