mySQL MATCH 跨多个表

Question

我有一组要搜索的 4 个表。每个都有一个全文索引。查询可以利用每个索引吗？

CREATE TABLE `categories` (
  `id` int(5) unsigned NOT NULL auto_increment,
  `display_order` int(5) unsigned default NULL,
  `name` varchar(64) default NULL,
  `last_modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `order` (`display_order`),
  FULLTEXT KEY `full_name` (`name`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

CREATE TABLE `host_types` (
  `id` int(5) unsigned NOT NULL auto_increment,
  `category_id` int(5) unsigned default NULL,
  `display_order` int(5) unsigned default NULL,
  `name` varchar(64) default NULL,
  `last_modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `order` (`category_id`,`display_order`),
  FULLTEXT KEY `full_name` (`name`)
) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;


CREATE TABLE `hosts` (
  `id` int(5) unsigned NOT NULL auto_increment,
  `host_id` int(5) unsigned default NULL,
  `display_order` int(5) unsigned default NULL,
  `name` varchar(64) default NULL,
  `last_modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `order` (`host_id`,`display_order`),
  FULLTEXT KEY `full_name` (`name`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;


CREATE TABLE `products` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `host_id` int(5) unsigned default NULL,
  `display_order` int(5) unsigned default NULL,
  `uid` varchar(10) default NULL,
  `name` varchar(128) default NULL,
  `keywords` text,
  `description` text,
  `price` decimal(10,2) default NULL,
  `quantity` int(11) unsigned default NULL,
  `last_modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`),
  FULLTEXT KEY `full_name` (`name`,`keywords`,`description`,`uid`)
) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=latin1;

这是我的查询；

SELECT categories.name AS category, 
  categories.id AS category_id, 
  host_types.name AS host_type, 
  host_types.id AS host_type_id, 
  hosts.name AS host, 
  hosts.id AS host_id, 
  products.name as name, 
  products.id AS product_id, 
  products.keywords as keywords, 
  products.description AS description, 
  products.quantity AS quantity, 
  products.price AS price, 
  products.uid as catalogue, 
  MATCH(categories.name, host_types.name, hosts.name, products.name, 
      products.keywords, products.description, products.uid) 
      AGAINST('search term') as score 
FROM products 
LEFT JOIN hosts ON products.host_id = hosts.id 
LEFT JOIN host_types ON hosts.host_id = host_types.id 
LEFT JOIN categories ON host_types.category_id = categories.id 
WHERE MATCH(categories.name, host_types.name, hosts.name, products.name, 
            products.keywords, products.description, products.uid) 
      AGAINST('search term') 
ORDER BY score DESC;

• 类别名称 == 全文 - 1
• host_types.name == FULLTEXT - 2
• hosts.name == FULLTEXT - 3
• products.name, products.keywords, products.description, products.uid == FULLTEXT - 4

---

这是我的 SQL 结构，我使用了上面的查询。

SELECT 
    categories.name AS category, 
    categories.id AS category_id, 
    host_types.name AS host_type, 
    host_types.id AS host_type_id, 
    hosts.name AS host, 
    hosts.id AS host_id, 
    products.name as name, 
    products.id AS product_id, 
    products.keywords as keywords, 
    products.description AS description, 
    products.quantity AS quantity, 
    products.price AS price, 
    products.uid as catalgue 
  MATCH(categories.name) AGAINST('search term') as cscore, 
  MATCH(host_types.name) AGAINST('search term') as htscore,
  MATCH(hosts.name) AGAINST('search term') as hscore,
  MATCH(products.name, products.keywords, products.description, products.uid)
    AGAINST('search term') as score
FROM products
LEFT JOIN hosts ON products.host_id = hosts.id
LEFT JOIN host_types ON hosts.host_id = host_types.id
LEFT JOIN categories ON host_types.category_id = categories.id
WHERE
  MATCH(categories.name) AGAINST('search term') OR
  MATCH(host_types.name) AGAINST('search term') OR
  MATCH(hosts.name) AGAINST('search term') OR
  MATCH(products.name, products.keywords, products.description, products.uid)
    AGAINST('search term')
ORDER BY score DESC



        CREATE TABLE `categories` (
  `id` int(5) unsigned NOT NULL auto_increment,
  `display_order` int(5) unsigned default NULL,
  `name` varchar(64) default NULL,
  `last_modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `order` (`display_order`),
  FULLTEXT KEY `full_name` (`name`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

CREATE TABLE `host_types` (
  `id` int(5) unsigned NOT NULL auto_increment,
  `category_id` int(5) unsigned default NULL,
  `display_order` int(5) unsigned default NULL,
  `name` varchar(64) default NULL,
  `last_modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `order` (`category_id`,`display_order`),
  FULLTEXT KEY `full_name` (`name`)
) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;


CREATE TABLE `hosts` (
  `id` int(5) unsigned NOT NULL auto_increment,
  `host_id` int(5) unsigned default NULL,
  `display_order` int(5) unsigned default NULL,
  `name` varchar(64) default NULL,
  `last_modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `order` (`host_id`,`display_order`),
  FULLTEXT KEY `full_name` (`name`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;


CREATE TABLE `products` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `host_id` int(5) unsigned default NULL,
  `display_order` int(5) unsigned default NULL,
  `uid` varchar(10) default NULL,
  `name` varchar(128) default NULL,
  `keywords` text,
  `description` text,
  `price` decimal(10,2) default NULL,
  `quantity` int(11) unsigned default NULL,
  `last_modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`),
  FULLTEXT KEY `full_name` (`name`,`keywords`,`description`,`uid`)
) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=latin1;

Answer 1

• 您不能在 MySQL 的多个表中定义全文索引(或任何类型的索引)。每个索引定义只引用一个表。给定全文索引中的所有列必须来自同一个表。

• 作为 MATCH() 函数的参数命名的列必须是单个全文索引的一部分。您不能使用对 MATCH() 的单个调用来搜索属于数据库中所有全文索引的所有列。

• 全文索引仅索引使用 CHAR、VARCHAR 和 TEXT 数据类型定义的列。

• 您可以在每个表中定义全文索引。

例子:

CREATE TABLE categories (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  FULLTEXT INDEX ftcat (name)
);

CREATE TABLE host_types (
  id SERIAL PRIMARY KEY,
  category_id BIGINT UNSIGNED,
  name VARCHAR(100),
  FULLTEXT INDEX ftht (name)
);

CREATE TABLE hosts (
  id SERIAL PRIMARY KEY,
  host_id BIGINT UNSIGNED,
  category_id BIGINT UNSIGNED,
  name VARCHAR(100),
  FULLTEXT INDEX fthost (name)
);

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  keywords VARCHAR(100),
  uid VARCHAR(100),
  description VARCHAR(100),
  quantity INTEGER,
  price NUMERIC(9,2),
  host_id BIGINT UNSIGNED,
  FULLTEXT INDEX ftprod (name, keywords, description, uid)
);

然后您可以编写一个使用每个相应全文索引的查询:

SELECT ...
  MATCH(categories.name) AGAINST('search term') as cscore, 
  MATCH(host_types.name) AGAINST('search term') as htscore,
  MATCH(hosts.name) AGAINST('search term') as hscore,
  MATCH(products.name, products.keywords, products.description, products.uid)
    AGAINST('search term') as score
FROM products
LEFT JOIN hosts ON products.host_id = hosts.id
LEFT JOIN host_types ON hosts.host_id = host_types.id
LEFT JOIN categories ON host_types.category_id = categories.id
WHERE
  MATCH(categories.name) AGAINST('search term') OR
  MATCH(host_types.name) AGAINST('search term') OR
  MATCH(hosts.name) AGAINST('search term') OR
  MATCH(products.name, products.keywords, products.description, products.uid)
    AGAINST('search term')
ORDER BY score DESC;