php - mariadb 上的学说行为可翻译索引标识符太长

Question

我正在使用 Symfony 4 和 flex 并尝试使用 KNP Labs - Doctrine Behaviors 制作一些翻译表但是 Symfony 自动生成的索引名称对于 MariaDb 来说太长了。至少我从错误中理解了这一点:

In AbstractMySQLDriver.php line 125:

An exception occurred while executing

CREATE TABLE app_menu_trans (
    id INT AUTO_INCREMENT NOT NULL, 
    translatable_id INT DEFAULT NULL, 
    name VARCHAR(255 ) NOT NULL, 
    locale VARCHAR(255) NOT NULL, 
    INDEX IDX_B79696A62C2AC5D3 (trans latable_id), 
    UNIQUE INDEX app_menu_trans_unique_translation (translatable_id, locale), 
    PRIMARY KEY(id)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_  

SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was t oo long; max key length is 767 bytes

In PDOConnection.php line 109:

SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was t oo long; max key length is 767 bytes

In PDOConnection.php line 107:

SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was t oo long; max key length is 767 bytes

这里有些可疑，因为 MariaDb 规范是这么说的:

Databases, tables, columns, indexes, constraints, stored routines, triggers, events, views, tablespaces, servers and log file groups have an maximum length of 64 characters.

我还不够先进，无法理解问题所在以及如何解决它。
我的翻译表类如下:

namespace App\Entity;


use Doctrine\ORM\Mapping as ORM;
use Knp\DoctrineBehaviors\Model as ORMBehaviors;

/**
 * @ORM\Table(name="app_menu_trans")
 * @ORM\Entity
 */
class MenuTranslation
{
    use ORMBehaviors\Translatable\Translation;

    /**
     * @var string
     *
     * @ORM\Column(type="string", length=255)
     */
    private $name;

    /**
     * @return integer
     */
    public function getId()
    {
        return $this->id;
    }

    /**
     * @return string
     */
    public function getName()
    {
        return $this->name;
    }

    /**
     * @param string $name
     *
     * @return MenuTranslationTranslation
     */
    public function setName($name)
    {
        $this->name = $name;

        return $this;
    }
}

Answer 1

767 错误的 5 个解决方法:http://mysql.rjweb.org/doc.php/limits#767_limit_in_innodb_indexes

如果您由于尝试使用字符集 utf8mb4 而达到限制。然后执行以下操作之一(每个操作都有一个缺点)以避免错误:

⚈  Upgrade to 5.7.7 for 3072 byte limit -- your cloud may not provide this; 
⚈  Change 255 to 191 on the VARCHAR -- you lose any values longer than 191 characters; 
⚈  ALTER .. CONVERT TO utf8 -- you lose Emoji and some of Chinese; 
⚈  Use a "prefix" index -- you lose some of the performance benefits. 

或者...保留 5.6/5.5/10.1，但执行 4 个步骤将限制提高到 3072 字节:

   SET GLOBAL innodb_file_format=Barracuda;
   SET GLOBAL innodb_file_per_table=1;
   SET GLOBAL innodb_large_prefix=1;
   logout & login (to get the global values);
   ALTER TABLE tbl ROW_FORMAT=DYNAMIC;  -- (or COMPRESSED)