mysql - 来自 SHOW 命令的单列

Question

我正在使用它来获取我的 PRIMARY 索引列。

SHOW INDEX FROM `table` WHERE Key_name='PRIMARY'

它返回一个包含类型、column_name、Key_name 等的表。

我只想要 Column_name 列。

我试过:

SHOW INDEX.Column_name FROM `table` WHERE Key_name='PRIMARY'

但是没有用。有什么想法吗？

Answer 1

要从 mydb.mytable 的 PRIMARY KEY(按顺序)获取所有列，请运行以下命令:

SELECT column_name
FROM information_schema.statistics
WHERE table_schema='mydb'
AND table_name='mytable'
AND index_name='PRIMARY'
ORDER BY seq_in_index;

要从当前数据库中的 mytable 的 PRIMARY KEY(按顺序)获取所有列，请运行以下命令:

SELECT column_name
FROM information_schema.statistics
WHERE table_schema=DATABASE()
AND table_name='mytable'
AND index_name='PRIMARY'
ORDER BY seq_in_index;

这是一个示例表:

mysql> show create table cacti.host_snmp_cache\G
*************************** 1. row ***************************
       Table: host_snmp_cache
Create Table: CREATE TABLE `host_snmp_cache` (
  `host_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `snmp_query_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `field_name` varchar(50) NOT NULL DEFAULT '',
  `field_value` varchar(255) DEFAULT NULL,
  `snmp_index` varchar(255) NOT NULL DEFAULT '',
  `oid` text NOT NULL,
  PRIMARY KEY (`host_id`,`snmp_query_id`,`field_name`,`snmp_index`),
  KEY `host_id` (`host_id`,`field_name`),
  KEY `snmp_index` (`snmp_index`),
  KEY `field_name` (`field_name`),
  KEY `field_value` (`field_value`),
  KEY `snmp_query_id` (`snmp_query_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql>

注意 PRIMARY KEY 有 4 列

这是查询的执行:

mysql> SELECT column_name
    -> FROM information_schema.statistics
    -> WHERE table_schema='cacti'
    -> AND table_name='host_snmp_cache'
    -> AND index_name='PRIMARY'
    -> ORDER BY seq_in_index;
+---------------+
| column_name   |
+---------------+
| host_id       |
| snmp_query_id |
| field_name    |
| snmp_index    |
+---------------+
4 rows in set (0.00 sec)

mysql>

使用 GROUP_CONCAT ，您可以将列名收集到 CSV 列表中

mysql> SELECT GROUP_CONCAT(column_name) PrimaryKeyColumns
    -> FROM information_schema.statistics
    -> WHERE table_schema='cacti'
    -> AND table_name='host_snmp_cache'
    -> AND index_name='PRIMARY'
    -> ORDER BY seq_in_index;
+---------------------------------------------+
| PrimaryKeyColumns                           |
+---------------------------------------------+
| host_id,snmp_query_id,field_name,snmp_index |
+---------------------------------------------+
1 row in set (0.01 sec)

mysql>

试一试!!!