MySQL Forums
Forum List  »  PostgreSQL

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

I am cross-posting this from the full-text search forum because this forum seems to have more anwered posts than that, and it does fit here as well....

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
8962
December 16, 2008 09:44AM


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.