本文介绍了跨多个表的 mySQL MATCH的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着跟版网的小编来一起学习吧！
问题描述
我有一组 4 个表，我想在其中进行搜索.每个都有一个全文索引.查询可以使用每个索引吗?
I have a set of 4 tables that I want to search across. Each has a full text index. Can a query make use of every index?
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 == FULLTEXT - 1
- host_types.name == FULLTEXT - 2
- hosts.name == FULLTEXT - 3
- products.name、products.keywords、products.description、products.uid == FULLTEXT - 4
这是我的 SQL 结构，我使用了上面的查询.
Here is my SQL structure, and I used the above Query.
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;
推荐答案
您不能在 MySQL 中跨多个表定义全文索引(或任何类型的索引).每个索引定义只引用一个表.给定全文索引中的所有列必须来自同一个表.
You can't define fulltext indexes (or any kind of index) across multiple tables in MySQL. Each index definition references exactly one table. All columns in a given fulltext index must be from the same table.
命名为
MATCH()函数参数的列必须是单个全文索引的一部分.您不能使用一次对MATCH()的调用来搜索属于数据库中所有全文索引的所有列.The columns named as arguments to the
MATCH()function must be part of a single fulltext index. You can't use a single call toMATCH()to search all columns that are part of all fulltext indexes in your database.全文索引仅索引用
CHAR、VARCHAR和TEXT数据类型定义的列.Fulltext indexes only index columns defined with
CHAR,VARCHAR, andTEXTdatatypes.您可以在每个表中定义全文索引.
You can define a fulltext index in each table.
示例:
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) );然后您可以编写一个使用每个全文索引的查询:
And then you can write a query that uses each respective fulltext index:
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;这篇关于跨多个表的 mySQL MATCH的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！
本站部分内容来源互联网,如果有图片或者内容侵犯了您的权益，请联系我们，我们会在确认后第一时间进行删除！