mysql - MariaDB 字符编码

Question

我刚刚开始将旧的 MySQL/Spring/Eclipselink 项目移植到 MariaDB。我在创建表时遇到问题，可以如下所示:

MariaDB [spasm]> CREATE TABLE Configuration (ID BIGINT NOT NULL, Attribute VARCHAR(190) NOT NULL UNIQUE, Value VARCHAR(255) NOT NULL, PRIMARY KEY (ID));
Query OK, 0 rows affected (0.07 sec)

MariaDB [spasm]> drop table Configuration;
Query OK, 0 rows affected (0.06 sec)

MariaDB [spasm]> CREATE TABLE Configuration (ID BIGINT NOT NULL, Attribute VARCHAR(255) NOT NULL UNIQUE, Value VARCHAR(255) NOT NULL, PRIMARY KEY (ID));
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
MariaDB [spasm]> 

MariaDB [spasm]> show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

我知道这与字符编码有关，但我不知道如何管理/纠正它？

Answer 1

新的默认字符集是utf8mb4。它提示 UNIQUE 索引:

Attribute VARCHAR(255) NOT NULL UNIQUE

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

⚈  Upgrade to 5.7.7 (MariaDB 10.2.2?) 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 (unlikely?); 
⚈  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)