Question

我对我的SQL.1有以下图谋:

CREATE TABLE  `mytest` (
  `category` varchar(32) ,
  `item_name` varchar(255)
  KEY `key1` (`category`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

第3类

[:parent_parent_cat_id][:parent_cat_id][:leaf_cat_id]

10000200003000

如果你能够搜寻所有以下类别: 父母—— 类别_id

SELECT * FROM mytest WHERE category LIKE "10000%";

它使用指数关键1;

but How to use index when I wanna search :parent_cat_id?

SELECT * FROM mytest WHERE category LIKE "%20000%";

你们是否有更好的解决办法?

Answer 1

This model is called materialized path.

仅需在单独的表格中打上<条码>。反之,不提:

SELECT  i.*
FROM    categories c
JOIN    items i
ON      i.category = c.id
WHERE   c.path BETWEEN
        (
        SELECT  path
        FROM    categories ci
        WHERE   ci.id =  20000 
        )
        AND
        CONCAT(
        (
        SELECT  path
        FROM    categories ci
        WHERE   ci.id =  20000 
        ),  : )

Answer 2

第二点不能使用指数。

You didn t make it clear in your question whether you are able to make changes to the schema, but you should try to change it if at all possible. To store heirarchical data in MySQL I suggest you first look at the presentation Models for hierarchical data by Bill Karwin.

您也不妨阅读以下系列:MySQL中的等级问题,。

友情链接