MySQL - 按多个关键字排序[关闭]

Writing an eCommerce for a local business. Got it all sorted and such, just working out the kinks.

Now they have a lot of products that are the same, but just differ by colour. So i need some help on sorting by said colour. They are named by the following.

[Manufacturer] - [Colour] - [Series]

Example: Art Spectrum - Primrose Yellow - Series 1

I'd need them to be sorted by Yellow but sometimes they don't have a certain type of colour. Like just Yellow, instead of Primrose Yellow. Also, there are multiple colours in the database - red, green, blue etc and all have variants.

Anyway, how would i sort by this?

Thanks

I think you need to use Full TEXT index search here.

But Full-text searches are supported for MyISAM tables only.

Add FULLTEXT index to your column, then use the following query:

Query:

SELECT * FROM table_name
WHERE MATCH (column_name)
AGAINST ('Yellow' IN BOOLEAN MODE)

If I understand you correctly you can try

SELECT *
  FROM products
 ORDER BY TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(prod_name, '-', 2),'-', -1)

That will obviously let you sort only by exact match of color names

SQLFiddle

UPDATE Now, you can easily create a color matching table like this

CREATE TABLE colors
    (`color` varchar(64), `color2` varchar(64))
;

Fill it with matching colors

|           COLOR | COLOR2 |
----------------------------
| Primrose Yellow | Yellow |

You can initially fill the color field in this table with a query like this

INSERT INTO colors (color)
SELECT TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(prod_name, '-', 2),'-', -1))
  FROM products

Then you can use this query to do what you want

SELECT p.*
FROM products p LEFT JOIN
     colors c ON TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(p.prod_name, '-', 2),'-', -1)) = c.color
ORDER BY COALESCE(c.color2, TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(p.prod_name, '-', 2),'-', -1)))

If there are no entries for particular color in a color matching table result set will be sorted anyway based on an actual color value found in a product name That means that you can fill it gradually.

Output

| ID |                                 PROD_NAME |
--------------------------------------------------
|  2 |                    Art 2 - Red - Series 1 |
|  1 | Art Spectrum - Primrose Yellow - Series 1 |
|  3 |         Art Spectrum2 - Yellow - Series 2 |

SQLFiddle for that