mysql keyword search across multiple columns

Various incarnations of this question have been asked here before, but I thought I'd give it another shot.

I had a terrible database layout. A single entity (widget) was split into two tables:

CREATE TABLE widgets (widget_id int(10) NOT NULL auto_increment)

CREATE TABLE widget_data ( widget_id int(10), field ENUM('name','size','color','brand'), data TEXT)

this was less that ideal. if wanted to find widgets of a specific name, color and brand, i had to do a three-way join on the widget_data table. so I converted to the reasonable table type:

CREATE TABLE widgets (widget_id int(10) NOT NULL auto_increment, name VARCHAR(32),size INT(3),color VARCHAR(16), brand VARCHAR(32))

This makes most queries much better. But it makes searching harder. It used to be that if i wanted to search widgets for, say, '%black%', I would just SELECT * FROM widget_data WHERE data LIKE '%black%'. This would give me all instances of widgets that are black in color, or are made by blackwell industries, or whatever. I would even know exactly which field matched, and could show that to my user.

how do I execute a similar search using the new table layout? I could of course do WHERE name LIKE '%black%' OR size LIKE '%black%'... but that seems clunky, and I still don't know which fields matched. I could run a separate query for each column I want to match on, which would give me all matches and how they matched, but that would开发者_如何学JAVA be a performance hit. any ideas?

You can include part of WHERE expression into selecting columns. For example:

SELECT 
  *, 
  (name LIKE '%black%') AS name_matched,
  (size LIKE '%black%') AS size_matched
FROM widget_data 
WHERE name LIKE '%black%' OR size LIKE '%black%'...

Then check value of name_matched on side of the script.

Not sure how it will affect performance. Feal free to test it before going to production

You have two conflicting requirements. You want to search as if all your data is in a single field, but you also want to identify which specific field was matched.

There's nothing wrong with your WHERE name LIKE '%black%' OR size LIKE '%black%'... expression. It's a perfectly valid search on the table as you have defined it. Why not just check the results in code to see which one matched? It's a minimal overhead.

If you want a cleaner syntax for your SQL then you could create a view on the table, adding an extra field which consists of concatenating the other fields:

CREATE VIEW extra_widget_data AS
  SELECT (name, size, color, brand,
          CONCAT(name, size, color, brand) as all_fields)
  FROM widget_data;

Then you'd have to add an index on this field, which requires more space, CPU time to maintain etc. I don't think it's worth it.

You probably want to look into MySQL full text search capability, this enables you to match against multiple columns of varchar type.

http://dev.mysql.com/doc/refman/5.1/en/fulltext-search.html