logo标签列表

多表情况下的mySQL MATCH查询

mysqljoinfull-text-search
12

我有一组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;
  • categories.name == 全文本 - 1
  • host_types.name == 全文本 - 2
  • hosts.name == 全文本 - 3
  • products.name、products.keywords、products.description、products.uid == 全文本 - 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;
2
回答这个问题的人将获得50美元的奖励。 - Dirk
一瞬间，他似乎在炫耀自己最长的 SQL 查询。 - Sinan Taifour
1
我编辑了它，希望找到一个问题...这是一个谜语吗？ - Paul Dixon
@Paul Dixon - 你应该展示一下改变前后的效果 :) - Dirk
谢谢，我没看到如何缩进代码，我所有的换行都在那里了。 - MrPHP
有没有人对我做错了什么有任何想法？ 搜索一个表是可以的... SELECT *, MATCH(products.name, products.keywords, products.description, products.uid) AGAINST('$term') as score FROM products WHERE MATCH(products.name, products.keywords, products.description, products.uid) AGAINST('$term') ORDER BY score DESC - MrPHP
2个回答
29

  • 在 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;
不错的答案。但是你只按分数排序，而不是其他分数（cscore、htscore和hscore）。将所有分数加起来然后排序是否更好，以便赋予其他表重量？ - Maciek Semik
取决于您想如何排序。 - Bill Karwin
2

仅是对上面答案的扩展。有一些要考虑的事情。如果您使用LEFT JOIN连接多行并使用GROUP将这些行分组，则需要获取MAX（）。此外，上述方法仅按照一个表的相关性排序，因此忽略了其他表的相关性。

SELECT ...
  MAX(MATCH(categories.name) AGAINST('search term'))+ 
  MAX(MATCH(host_types.name) AGAINST('search term'))+
  MAX(MATCH(hosts.name) AGAINST('search term'))+
  MAX(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;

这样可以将所有表的相关性添加起来。MAX()函数允许您对数据进行分组。当您LEFT连接多行时，它也会有所帮助。

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接