网站首页
精品模板
会员模板
建站资源
站长素材
建站教程
编程笔记
编程问答
编程问答
前端开发问题 Java开发问题 C/C++开发问题 Python开发问题 C#/.NET开发问题 php开发问题 移动开发问题 数据库问题
沃梦达建站-编程问答-数据库问题-正文

跨多个表的 mySQL MATCH

2023-10-08数据库问题
0
本文介绍了跨多个表的 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 to MATCH() 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, and TEXT datatypes.

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

        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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！

        The End

相关推荐

Mysql目录里的ibtmp1文件过大造成磁盘占满的解决办法
ibtmp1是非压缩的innodb临时表的独立表空间,通过innodb_temp_data_file_path参数指定文件的路径，文件名和大小，默认配置为ibtmp1:12M:autoextend，也就是说在文件系统磁盘足够的情况下，这个文件大小是可以无限增长的。 为了避免ibtmp1文件无止境的暴涨导致...
2025-01-02 数据库问题
151
SQL 子句“GROUP BY 1"是什么意思?意思是?
What does SQL clause quot;GROUP BY 1quot; mean?(SQL 子句“GROUP BY 1是什么意思?意思是?)...
2024-04-16 数据库问题
62
MySQL groupwise MAX() 返回意外结果
MySQL groupwise MAX() returns unexpected results(MySQL groupwise MAX() 返回意外结果)...
2024-04-16 数据库问题
13
MySQL SELECT 按组最频繁
MySQL SELECT most frequent by group(MySQL SELECT 按组最频繁)...
2024-04-16 数据库问题
16
为什么 Mysql 的 Group By 和 Oracle 的 Group by 行为不同
Why Mysql#39;s Group By and Oracle#39;s Group by behaviours are different(为什么 Mysql 的 Group By 和 Oracle 的 Group by 行为不同)...
2024-04-16 数据库问题
13
MySQL GROUP BY DateTime +/- 3 秒
MySQL GROUP BY DateTime +/- 3 seconds(MySQL GROUP BY DateTime +/- 3 秒)...
2024-04-16 数据库问题
14

热门文章

1ORA-01747: 无效的 user.table.column、table.column 或列规范 2ORA-01461: 只能为插入到 LONG 列而绑定 LONG 值-查询时发生 3INSERT 语句与 FOREIGN KEY 约束冲突 4MySql 错误:无法更新存储函数/触发器中的表，因为它已被调用此存储函数/触发器的语句使用 5MySQL:将逗号分隔的列表拆分为多行 6sqlite3 在数据库中插入和读取 BLOB 数据 7ORA-01830:日期格式图片在转换整个输入字符串之前结束/选择日期查询的总和 8MySQL 错误:UPDATE 和 LIMIT 的错误使用

热门精品源码

最新VIP资源

1多功能实用站长工具箱html功能模板 2多风格简历在线生成程序网页模板 3论文相似度查询系统源码 4响应式旅游景点宣传推广页面模板 5在线起名宣传推广网站源码 6酷黑微信小程序网站开发宣传页模板 7房产销售交易中介网站模板 8小学作业自动生成程序