MySQL Forums
Forum List  »  Full-Text Search

Confusion following crossover from PGSQL
Posted by: Alex Moen
Date: December 16, 2008 09:37AM

I'm gonna try posting here, even tho it looks like only about 50% of the posts ever get answered...

I am moving a database and website from PGSQL to MySQL. I am concerned with some searching anomalies that I have encountered with MySQL.

In the Postgres version, any and all searches of the database were performed by basically the same line:
select * from {table} where {colunm} ~* {data};

In MySQL, that would be:
select * from {table} where {column} LIKE {data};

This works for some but not all searches, and trying out the MATCH AGAINST words are also showing inconsistency. For instance:

Given a row with the following:
|oid | network | ip_address |asgnmt|comments|cont|hcd |
+-----------+------------------+---------------+------+--------+----+----+
|00000001999|192.168.000.000/24|192.168.000.001| NULL | modems |NULL|NULL|

select * from detail where match oid against ('00000001*' IN BOOLEAN MODE);
~snip~
1000 rows in set (0.04 sec)

select * from detail where oid like '00000001*';
Empty set (0.03 sec)

HOWEVER, given the same row structure:

select * from detail where match network against ('192.168.000.000/24' IN BOOLEAN MODE);
Empty set (0.02 sec)

select * from detail where network like '192.168.000.000/24';
~snip~
256 rows in set (0.02 sec)

All fields are varchar in this database.

Can someone please explain the glaring mutually-exclusive difference here? And why can't I use just one, as in Postgres?

Thanks,

Alex

Options: ReplyQuote


Subject
Views
Written By
Posted
Confusion following crossover from PGSQL
3488
December 16, 2008 09:37AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.