如何在symfony3中使用“json\u array”类型的列上的原则添加复合索引?

rkkpypqq  于 2021-06-24  发布在  Mysql
关注(0)|答案(1)|浏览(371)

我有一个表,我想添加如下索引:

@Index(columns={"child_id", "abilities"}),
 @Index(columns={"parent_id", "abilities"}),

...


* @ORM\Column(type="json_array", length=256)

 */
protected $abilities;

但是,即使在迁移中添加了适当的限制:

$this->addSql('CREATE INDEX IDX_1088BF61DD62C21BB8388DA4 ON xxx (child_id, abilities(512))');
$this->addSql('CREATE INDEX IDX_1088BF61727ACA70B8388DA4 ON xxx (parent_id, abilities(512))');

在运行重新创建数据库的测试时,会出现如下错误:
执行“”时发生异常

CREATE TABLE xxx (
    id INT AUTO_INCREMENT NOT NULL, 
    parent_id INT DEFAULT NULL, 
    child_id INT DEFAULT NULL, 
    type VARCHAR(125) NOT NULL, 
    abilities LONGTEXT NOT NULL COMMENT '(DC2Type:json_array)', 
    created_at DATETIME NOT NULL, 
    modified_at DATETIME NOT NULL, 
    INDEX IDX_1088BF61727ACA70 (parent_id), 
    INDEX IDX_1088BF61DD62C21B (child_id), 
    INDEX IDX_1088BF61DD62C21B8CDE5729 (child_id, type), 
    INDEX IDX_1088BF61727ACA708CDE5729 (parent_id, type), 
    INDEX IDX_1088BF618CDE5729 (type), 
    INDEX IDX_1088BF61DD62C21BB8388DA4 (child_id, abilities), 
    INDEX IDX_1088BF61727ACA7 0B8388DA4 (parent_id, abilities), 
    INDEX IDX_1088BF61B8388DA4 (abilities), 
    UNIQUE INDEX UNIQ_1088BF61727ACA70DD62C21B (parent_id, child_id), 
    PRIMARY KEY(id)
) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB

':
sqlstate[42000]:语法错误或访问冲突:1170 blob/text列“abilities”在没有键长度的键规范中使用
要在这些列上成功创建索引,我需要做什么?

lstz6jyr

lstz6jyr1#

索引长字符串有一些限制。
因为所讨论的字符串包含“json”,所以在该列上有一个常规索引是没有用的。
解决方案:去掉这3个索引:

INDEX IDX_1088BF61DD62C21BB8388DA4 (child_id, abilities), 
INDEX IDX_1088BF61727ACA7 0B8388DA4 (parent_id, abilities), 
INDEX IDX_1088BF61B8388DA4 (abilities),

相关问题