I have a table with the following structure:
+-----------------+--------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+-------------------+-----------------------------+
| imovel_id | int(11) | NO | PRI | NULL | auto_increment |
| user_id | int(11) | NO | | NULL | |
| img_dest | varchar(111) | YES | | NULL | |
| data_imob | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| finalidade_imob | varchar(51) | NO | MUL | NULL | |
| status_imob | varchar(51) | NO | | NULL | |
| tipo_imob | varchar(255) | YES | | NULL | |
| uf_imob | varchar(51) | NO | MUL | NULL | |
| cidade_imob | varchar(255) | NO | | NULL | |
| bairro_imob | varchar(255) | YES | | NULL | |
| rua_imob | varchar(255) | YES | | NULL | |
| vaga_imob | varchar(255) | YES | | NULL | |
| dorms_imob | char(2) | YES | | NULL | |
| tamanho_imob | varchar(222) | YES | | NULL | |
| valor_imob | varchar(255) | YES | | 0 | |
| titulo_imob | varchar(255) | YES | | NULL | |
| descricao_imob | longtext | YES | | NULL | |
| carac_imob | varchar(255) | YES | | NULL | |
+-----------------+--------------+------+-----+-------------------+-----------------------------+
If I run the query:
SELECT * FROM form_imovel LEFT JOIN (form_user) ON (form_user.user_id = form_imovel.user_id) WHERE MATCH(finalidade_imob,status_imob,tipo_imob,uf_imob,cidade_imob,bairro_imob,dorms_imob,valor_imob) AGAINST ('Comprar' IN BOOLEAN MODE);
It works with the value contained in any column except with the uf_imob column that contains values like: SP or MG , etc, acronym of states.
Then for example if I try the following query:
SELECT * FROM form_imovel LEFT JOIN (form_user) ON (form_user.user_id = form_imovel.user_id) WHERE MATCH(finalidade_imob,status_imob,tipo_imob,uf_imob,cidade_imob,bairro_imob,dorms_imob,valor_imob) AGAINST ('SP' IN BOOLEAN MODE);
Even though I have multiple records containing SP it returns empty.
.