MySQL 未定义索引 : column_info when creating a view

Question

我已经定义了这个工作正常的查询:

    SELECT `a`.`id`,
        `a`.`fld_tribunal` AS `fld_tribunal`,
        `a`.`fld_date` AS `fld_date`,
        `a`.`fld_juge` AS `fld_juge`,
        `a`.`fld_representant_prefecture` AS `fld_representant_prefecture`,
        `a`.`fld_interpretes` AS `fld_interpretes`,
        `a`.`fld_nombre_policiers` AS `fld_nombre_policiers`,
        `a`.`fld_retenus_menottes` AS `fld_retenus_menottes`,
        `a`.`fld_representants_cdv` AS `fld_representants_cdv`,
        `a`.`fld_public` AS `fld_public`,
        `a`.`fld_duree_audience` AS `fld_duree_audience`,
        `a`.`fld_duree_delibere` AS `fld_duree_delibere`
    FROM `cr_tribunaux` `a`
    INNER JOIN `list_records` `r`
        ON ((`r`.`form_id` = 8) AND (`r`.`record_id` = `a`.`id`))
    INNER JOIN `list_states` `s`
        ON ((`s`.`id` = `r`.`state_id`) AND (`s`.`form_id` = `r`.`form_id`) )
    WHERE `s`.`id`=74 OR `s`.`id`=75
    ORDER BY `a`.`fld_date` ASC

但是当我尝试使用 phpMyadmin 从中创建 View 时出现错误:

Notice in ./libraries/SystemDatabase.php#52
Undefined index: column_info

Backtrace

./view_create.php#140: PMA\libraries\SystemDatabase->getExistingTransformationData(string 'maindb')

当我删除 INNER JOIN 语句时， View 创建得很好，因此我正在使用的语法中或(我希望没有)JOIN 所针对的表中必须有某些内容。我尝试了不同的括号组合，但没有任何帮助。

三个关联表的建表代码为:

CREATE TABLE `cr_tribunaux` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `storage_id` int(11) NOT NULL DEFAULT '4',
 `user_id` int(11) NOT NULL DEFAULT '0',
 `created` datetime NOT NULL DEFAULT '2018-07-10 09:51:32',
 `created_by` varchar(255) NOT NULL DEFAULT '',
 `modified_user_id` int(11) NOT NULL DEFAULT '0',
 `modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 `modified_by` varchar(255) NOT NULL DEFAULT '',
 `fld_tribunal` text NOT NULL,
 `fld_juge` text NOT NULL,
 `fld_date` text NOT NULL,
 `fld_representant_prefecture` text NOT NULL,
 `fld_nombre_policiers` text NOT NULL,
 `fld_retenus_menottes` text,
 `fld_public` text NOT NULL,
 `fld_representants_cdv` text NOT NULL,
 `fld_duree_delibere` text,
 `fld_compte_rendu` text NOT NULL,
 `fld_remarques` text NOT NULL,
 `fld_interpretes` text NOT NULL,
 `fld_duree_audience` text NOT NULL,
 `fld_retenus_menottes_detail` text NOT NULL,
 PRIMARY KEY (`id`),
 KEY `storage_id` (`storage_id`),
 KEY `user_id` (`user_id`),
 KEY `created` (`created`),
 KEY `modified_user_id` (`modified_user_id`),
 KEY `modified` (`modified`)
) ENGINE=MyISAM AUTO_INCREMENT=747 DEFAULT CHARSET=utf8```

CREATE TABLE `list_states` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `form_id` int(11) NOT NULL DEFAULT '0',
 `title` varchar(255) NOT NULL DEFAULT '',
 `color` varchar(255) NOT NULL DEFAULT '',
 `action` varchar(255) NOT NULL DEFAULT '',
 `published` tinyint(4) NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=221 DEFAULT CHARSET=utf8


CREATE TABLE `list_records` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `form_id` int(11) NOT NULL DEFAULT '0',
 `record_id` bigint(20) NOT NULL DEFAULT '0',
 `state_id` int(11) NOT NULL DEFAULT '0',
 `reference_id` int(11) NOT NULL DEFAULT '0',
 `published` tinyint(1) NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`),
 KEY `form_id` (`form_id`,`record_id`,`state_id`)
) ENGINE=MyISAM AUTO_INCREMENT=811 DEFAULT CHARSET=utf8

Answer 1

要创建 View ，您可以使用以下代码

CREATE VIEW sample_test AS
SELECT `a`.`id`,
        `a`.`fld_tribunal` AS `fld_tribunal`,
        `a`.`fld_date` AS `fld_date`,
        `a`.`fld_juge` AS `fld_juge`,
        `a`.`fld_representant_prefecture` AS `fld_representant_prefecture`,
        `a`.`fld_interpretes` AS `fld_interpretes`,
        `a`.`fld_nombre_policiers` AS `fld_nombre_policiers`,
        `a`.`fld_retenus_menottes` AS `fld_retenus_menottes`,
        `a`.`fld_representants_cdv` AS `fld_representants_cdv`,
        `a`.`fld_public` AS `fld_public`,
        `a`.`fld_duree_audience` AS `fld_duree_audience`,
        `a`.`fld_duree_delibere` AS `fld_duree_delibere`
    FROM `cr_tribunaux` `a`
    INNER JOIN `list_records` `r`
        ON ((`r`.`form_id` = 8) AND (`r`.`record_id` = `a`.`id`))
    INNER JOIN `list_states` `s`
        ON ((`s`.`id` = `r`.`state_id`) AND (`s`.`form_id` = `r`.`form_id`) )
    WHERE `s`.`id`=74 OR `s`.`id`=75
    ORDER BY `a`.`fld_date` ASC;

即使在尝试此操作之后，如果出现错误，那一定是您的 PHPMyadmin 有问题。您可以根据您的操作系统尝试使用任何其他客户端(如 mysqlworkbench、sqlyog、HeidiSql)来创建此 View 。