显示表FULLTEXT索引列 [英] Show a tables FULLTEXT indexed columns

问题描述

我正在寻找一个查询，该查询将返回表中已被FULLTEXT索引的列的列表.该表为MyISAM格式，我将使用php构造查询.

I'm looking to run a query that will return a list of columns in a table that are FULLTEXT indexed. The table is in MyISAM format and i'll be using php to construct the query.

理想情况下，我将运行查询并返回信息，因此我可以构造以逗号分隔的列字符串.

Ideally i would run the query and it would return the information so i could construct a comma separated string of the columns.

例如名字，姓氏，电子邮件"

e.g. "first_name, last_name, email"

在MySQL中这可能吗?

Is this possible in MySQL?

推荐答案

您可以从information_schema.STATISTICS表中获取该信息.

You can get that information from the information_schema.STATISTICS table.

我将为您提供查询以获取表中一个或多个FULLTEXT索引中的所有列，因为我认为这就是您要的.请记住，每个FULLTEXT索引中列的特定组合非常重要.除非存在包含所有这些列的单个FULLTEXT索引，否则MySQL不能使用FULLTEXT索引来搜索多个列.

I'll give you the query to get all columns in the table that are in one or more FULLTEXT indexes, since I think that's what you are asking for. Bear in mind that the specific combinations of columns in each FULLTEXT index are very important. MySQL can't use a FULLTEXT index to search multiple columns unless there is a single FULLTEXT index that includes all of those column.

这是第一个查询，提供您要求的输出:

Here's the first query that gives the output you asked for:

select group_concat(distinct column_name)
from information_schema.STATISTICS 
where table_schema = 'your_db' 
and table_name = 'your_table' 
and index_type = 'FULLTEXT';

这是一个显示FULLTEXT索引中的列的各种组合的表，如果表上的数目超过1:

And here's one that shows the various combinations of columns in FULLTEXT indexe if there is more than 1 on the table:

select index_name, group_concat(column_name) as columns
from information_Schema.STATISTICS 
where table_schema = 'your_db' 
and table_name = 'your_table' 
and index_type = 'FULLTEXT'
group by index_name;

这篇关于显示表FULLTEXT索引列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！