mysql - Mysql information_schema 可以不对齐show create table吗？

Question

information_schema 中似乎缺少数据。 show create table 与 information_schema 不同。

当我运行时:SHOW CREATE TABLE devicoserver.email_templates;

我有 4 个键:

CREATE TABLE `email_templates` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `subject` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email_type` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `language_id` int(10) unsigned NOT NULL,

  PRIMARY KEY (`id`),
  KEY `email_templates_language_id_foreign` (`language_id`),
  KEY `email_templates_email_type_unique` (`email_type`),
  KEY `email_templates_subject_unique` (`subject`),
  CONSTRAINT `email_templates_language_id_foreign` FOREIGN KEY (`language_id`) REFERENCES `languages` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

但是，当我查询信息模式时，它表明只有 2 个键

    select 
         CONSTRAINT_NAME,COLUMN_NAME FROM
         INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
WHERE TABLE_NAME = 'email_templates' and CONSTRAINT_SCHEMA = 'devicoserver';

PRIMARY, id
email_templates_language_id_foreign,language_id

这是否有意义，information_schema 可以与实际表不同吗？我知道它是真实的，因为我试图创建一个 key ，但我发现它已经存在了。

谢谢

Answer 1

你应该查询information_schema。statistics

例如

SELECT INDEX_NAME, GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) 
FROM `information_schema`.`statistics` 
WHERE table_schema = 'dbname' 
AND TABLE_NAME = 'tablename' 
GROUP BY INDEX_NAME 
ORDER BY INDEX_NAME ASC;