gpt4 book ai didi

mysql - 将 MySQL 数据库从 latin1 更新为 utf8mb4 : max key length is 767 bytes

转载 作者:行者123 更新时间:2023-11-30 22:45:57 25 4
gpt4 key购买 nike

我正在尝试将 MySQL 数据库(Rails 3 应用程序的一部分)中的字符编码从 latin1 升级到 utfmb8。我已经完成了 some reading但我被困在最后两个表上:userstrigrams

尝试更新 users 表会得到可怕的 ERROR 1071:

mysql> ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

正在关注 this answer ,我可以通过在有问题的 VARCHAR(255) 列上删除并重新创建索引来解决这个问题:

mysql> DROP INDEX index_users_on_remember_token ON users;
mysql> CREATE INDEX index_users_on_remember_token ON users (remember_token(191));

我担心这会把事情搞砸,因为我不太了解索引。但是暂时把它放在一边......

我的主要问题是如何为我的最后一张表做类似的事情:trigrams。以下是有关 trigrams 表的一些信息:

mysql> SHOW CREATE TABLE trigrams;
CREATE TABLE `trigrams` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`trigram` varchar(3) DEFAULT NULL,
`score` smallint(6) DEFAULT NULL,
`owner_id` int(11) DEFAULT NULL,
`owner_type` varchar(255) DEFAULT NULL,
`fuzzy_field` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `index_for_match` (`owner_id`,`owner_type`,`fuzzy_field`,`trigram`,`score`),
KEY `index_by_owner` (`owner_id`,`owner_type`)
) ENGINE=InnoDB AUTO_INCREMENT=41531 DEFAULT CHARSET=latin1

mysql> SHOW INDEXES IN trigrams;
+----------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| trigrams | 0 | PRIMARY | 1 | id | A | 35502 | NULL | NULL | | BTREE | | |
| trigrams | 1 | index_for_match | 1 | owner_id | A | 35502 | NULL | NULL | YES | BTREE | | |
| trigrams | 1 | index_for_match | 2 | owner_type | A | 35502 | NULL | NULL | YES | BTREE | | |
| trigrams | 1 | index_for_match | 3 | fuzzy_field | A | 35502 | NULL | NULL | YES | BTREE | | |
| trigrams | 1 | index_for_match | 4 | trigram | A | 35502 | NULL | NULL | YES | BTREE | | |
| trigrams | 1 | index_for_match | 5 | score | A | 35502 | NULL | NULL | YES | BTREE | | |
| trigrams | 1 | index_by_owner | 1 | owner_id | A | 35502 | NULL | NULL | YES | BTREE | | |
| trigrams | 1 | index_by_owner | 2 | owner_type | A | 35502 | NULL | NULL | YES | BTREE | | |
+----------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

我的问题是:如何解决以下错误?

mysql> ALTER TABLE trigrams CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

我对 trigrams 做了哪些更改?进行这些更改的风险是什么?

(如果相关:trigrams 表是由某些第三方代码自动创建的,fuzzily gem。)

最佳答案

尝试

CREATE TABLE tmp LIKE triggrams;
SET GLOBAL innodb_large_prefix=1;
ALTER TABLE tmp CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
LOCK TABLE trigrams, tmp WRITE;
INSERT INTO tmp SELECT * FROM trigrams;
RENAME TABLE trigrams to trigrams_old, tmp to trigrams;
UNLOCK TABLES;

第二个选项是减少 varchar 列的索引前缀。

关于mysql - 将 MySQL 数据库从 latin1 更新为 utf8mb4 : max key length is 767 bytes,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/29611653/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com