mySQL MATCH跨多个表 [英] mySQL MATCH across multiple tables

查看：91
本文介绍了mySQL MATCH跨多个表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有一套我想要搜索的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 ; 
 
 $ b 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; 
 
 $ b 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更新时的CURRENT_TIMESTAMP 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类别
 categories.id AS category_id $ b $ host_types.name AS host_type $ b $ host_types.id AS host_type_id 
 hosts .name AS主机，
 hosts.id AS host_id，
 products.name作为名称，
 products.id AS product_id，
 products.keywords作为关键字，
产品.description AS描述，
 products.quantity AS数量，
 products.price AS价格，
 products.uid作为目录，
 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类别ON host_types.category_id = categories.id 
 WHERE MATCH（categories.name，host_types。名称，主机名称， 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结构和我使用上面的查询。

  SELECT 
 categories.name AS类别，
类别.id AS category_id，
 host_types.name AS host_type，
 host_types.id AS host_type_id，
 hosts.name AS主机，
 hosts.id AS host_id，
产品.name作为名称，
 products.id AS product_id，
 products.keywords作为关键字，
 products.description AS说明，
 products.quantity AS数量，
产品.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类别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得分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 de错误CURRENT_TIMESTAMP更新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 ; 
 
 $ b 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; 
 
 $ b 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更新时的CURRENT_TIMESTAMP CURRENT_TIMESTAMP，
 PRIMARY KEY（`id`），
 FULLTEXT KEY`full_name`（`name`，`keywords`，`description`，`uid`）
） ENGINE = MyISAM AUTO_INCREMENT = 14 DEFAULT CHARSET = latin1;


解决方案


  • 您无法在MySQL中的多个表中定义全文索引（或任何类型的索引）。每个索引定义只引用一个表。给定全文索引中的所有列都必须来自同一个表。

    • code>函数必须是单个全文索引的一部分。您不能对 MATCH（）使用单个调用来搜索数据库中所有全文索引的所有列。

  • 全文索引仅索引以 CHAR ， VARCHAR 和<$ c $定义的索引列c> TEXT 数据类型。


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




    • 例子：

    $ $ p $ CREATE TABLE类别（
    id序列主键，
    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产品（
    id序列主键，
    名称VARCHAR（100），
    关键字VARCHAR（100），
    uid VARCHAR（100） ，
    description VARCHAR（100），
    数量INTEGER，
    价格NUMERIC（9,2），
    host_id BIGINT UNSIGNED，
    FULLTEXT INDEX ftprod（name，keywords，描述，uid）
    ）;

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

      SELECT ... 
     MATCH（categories.name）AGAINST（'search term'）as cscore，
     MATCH（host_types.name ）作为hscore，
     MATCH（products.name，products.keywords，products.description，products）作为htscore，
     MATCH（hosts.name）AGAINST（'search term'） .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类别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（'搜索字词'）
     ORDER BY得分DES C;


    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;

    Here is my 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 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

    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;

    解决方案

    • 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.

    • 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.

    • Fulltext indexes only index columns defined with CHAR, VARCHAR, and TEXT datatypes.

    • You can define a fulltext index in each table.

    Example:

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

查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆