MySQL：使用单个输入搜索多个列（GoogleMaps样式）

Question

I have a database loaded with geonames's data for the US. I want to perform a query on that table to find the closest match from a single input that has a more or less the same functionality as Google Maps (i.e. the user may input a zip code, a comma separated hierarchical address like 'Nevada, Las Vegas' or a combination of both).

So far I've added FULLTEXT indexes to the relevant fields of the table, and performing a query from the input like this:

SELECT * FROM places WHERE MATCH(country_code, postal_code, place_name, admin_name1, admin_code1, admin_name2, admin_code2) AGAINST (?);

Where ? is replaced by the input by a php script. Altough it kinda works, it does not perform as expected, it doesn't put the most relevant matches first and needs a full word to start finding matches (I tried with wildcards but got worse). I also tried splitting the input if it had commas but the logic of the query started to get messy and couldn't produce a better performing script.

Is there a way to search this database in such broad terms for matches?

I'm querying the database via AJAX so I need it to start searching even if the word is not completed yet (I'm using jQuery's typeahead).

Thanks in advance!

Answer 1

Replace string_to_be_searched text with your text.

SELECT * FROM places WHERE 
instr(concat_ws(" ",country_code, postal_code, place_name, admin_name1,
admin_code1, admin_name2, admin_code2),"string_to_be_searched")>0;