<small id='CR2C0'></small><noframes id='CR2C0'>

      <bdo id='CR2C0'></bdo><ul id='CR2C0'></ul>
    <i id='CR2C0'><tr id='CR2C0'><dt id='CR2C0'><q id='CR2C0'><span id='CR2C0'><b id='CR2C0'><form id='CR2C0'><ins id='CR2C0'></ins><ul id='CR2C0'></ul><sub id='CR2C0'></sub></form><legend id='CR2C0'></legend><bdo id='CR2C0'><pre id='CR2C0'><center id='CR2C0'></center></pre></bdo></b><th id='CR2C0'></th></span></q></dt></tr></i><div id='CR2C0'><tfoot id='CR2C0'></tfoot><dl id='CR2C0'><fieldset id='CR2C0'></fieldset></dl></div>
  1. <tfoot id='CR2C0'></tfoot>

      <legend id='CR2C0'><style id='CR2C0'><dir id='CR2C0'><q id='CR2C0'></q></dir></style></legend>
    1. 跨多个表的 mySQL MATCH

      时间:2023-10-08
        <bdo id='SRS0o'></bdo><ul id='SRS0o'></ul>
        <legend id='SRS0o'><style id='SRS0o'><dir id='SRS0o'><q id='SRS0o'></q></dir></style></legend>
              <tbody id='SRS0o'></tbody>

            <i id='SRS0o'><tr id='SRS0o'><dt id='SRS0o'><q id='SRS0o'><span id='SRS0o'><b id='SRS0o'><form id='SRS0o'><ins id='SRS0o'></ins><ul id='SRS0o'></ul><sub id='SRS0o'></sub></form><legend id='SRS0o'></legend><bdo id='SRS0o'><pre id='SRS0o'><center id='SRS0o'></center></pre></bdo></b><th id='SRS0o'></th></span></q></dt></tr></i><div id='SRS0o'><tfoot id='SRS0o'></tfoot><dl id='SRS0o'><fieldset id='SRS0o'></fieldset></dl></div>
          • <tfoot id='SRS0o'></tfoot>

                <small id='SRS0o'></small><noframes id='SRS0o'>

              1. 本文介绍了跨多个表的 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.

                      全文索引仅索引用CHARVARCHARTEXT 数据类型定义的列.

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

                上一篇:从 SQL 表中删除重复行(基于多列的值) 下一篇:MySQL:“SELECT 将检查超过 MAX_JOIN_SIZE 行";

                相关文章

              2. <small id='5zORg'></small><noframes id='5zORg'>

                <i id='5zORg'><tr id='5zORg'><dt id='5zORg'><q id='5zORg'><span id='5zORg'><b id='5zORg'><form id='5zORg'><ins id='5zORg'></ins><ul id='5zORg'></ul><sub id='5zORg'></sub></form><legend id='5zORg'></legend><bdo id='5zORg'><pre id='5zORg'><center id='5zORg'></center></pre></bdo></b><th id='5zORg'></th></span></q></dt></tr></i><div id='5zORg'><tfoot id='5zORg'></tfoot><dl id='5zORg'><fieldset id='5zORg'></fieldset></dl></div>

                    • <bdo id='5zORg'></bdo><ul id='5zORg'></ul>
                    <legend id='5zORg'><style id='5zORg'><dir id='5zORg'><q id='5zORg'></q></dir></style></legend>
                  1. <tfoot id='5zORg'></tfoot>