字符串数据，右截断:1406 Data too long for column [英] String data, right truncated: 1406 Data too long for column

问题描述

我遇到的问题是我收到以下错误，我进入 MySql 并运行:

The issue I am having is I am receiving the following error, I went into MySql and ran:

ALTER TABLE block_content__field_views_reference_book 
MODIFY COLUMN field_views_reference_book_target_id varchar(500);

要从 varchar(5) 更改它，但仍然收到以下错误，有什么建议吗?我错过了什么?:

To change it up from varchar(5) but still getting the error below, any suggestions? What am I missing?:

DrupalCoreEntityEntityStorageException:SQLSTATE[22001]:字符串数据，右截断:1406 第 1 行的field_views_reference_book_target_id"列的数据太长:插入 {block_content_r__e6f3906112}(entity_id、revision_id、bundle、delta、langcode, field_views_reference_book_target_id, field_views_reference_book_display_id, field_views_reference_book_data) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3, :db_insert_placeholder_4, :db_insert_placeholder_5, :db_insert_placeholder_6,数组 ( [:db_insert_placeholder_0] => 46 [:db_insert_placeholder_1] => 339 [:db_insert_placeholder_2] => hp_book [:db_insert_placeholder_3] => 0 [:db_insert_placeholder_4] => en [:db_insert_placeholder_5] => 书 [:db_insert_placeholder_6] =>block_1 [:db_insert_placeholder_7] => a:5:{s:6:"offset";N;s:5:"pager";N;s:5:"limit";N;s:5:"title";N;s:8:"argument";N;} ) 在 DrupalCoreEntitySqlSqlContentEntityStorage->save() (core/lib/Drupal/Core/Entity/Sql/SqlContentEntityStorage.php 的第 846 行).

DrupalCoreEntityEntityStorageException: SQLSTATE[22001]: String data, right truncated: 1406 Data too long for column 'field_views_reference_book_target_id' at row 1: INSERT INTO {block_content_r__e6f3906112} (entity_id, revision_id, bundle, delta, langcode, field_views_reference_book_target_id, field_views_reference_book_display_id, field_views_reference_book_data) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3, :db_insert_placeholder_4, :db_insert_placeholder_5, :db_insert_placeholder_6, :db_insert_placeholder_7); Array ( [:db_insert_placeholder_0] => 46 [:db_insert_placeholder_1] => 339 [:db_insert_placeholder_2] => hp_book [:db_insert_placeholder_3] => 0 [:db_insert_placeholder_4] => en [:db_insert_placeholder_5] => book [:db_insert_placeholder_6] => block_1 [:db_insert_placeholder_7] => a:5:{s:6:"offset";N;s:5:"pager";N;s:5:"limit";N;s:5:"title";N;s:8:"argument";N;} ) in DrupalCoreEntitySqlSqlContentEntityStorage->save() (line 846 of core/lib/Drupal/Core/Entity/Sql/SqlContentEntityStorage.php).

SHOW CREATE TABLE 的结果:

Results of SHOW CREATE TABLE:

CREATE TABLE `block_content__field_views_reference_book` (
  `bundle` varchar(128) CHARACTER SET ascii NOT NULL DEFAULT '' COMMENT 'The field instance bundle to which this row belongs, used when deleting a field instance',
  `deleted` tinyint(4) NOT NULL DEFAULT 0 COMMENT 'A boolean indicating whether this data item has been deleted',
  `entity_id` int(10) unsigned NOT NULL COMMENT 'The entity id this data is attached to',
  `revision_id` int(10) unsigned NOT NULL COMMENT 'The entity revision id this data is attached to',
  `langcode` varchar(32) CHARACTER SET ascii NOT NULL DEFAULT '' COMMENT 'The language code for this data item.',
  `delta` int(10) unsigned NOT NULL COMMENT 'The sequence number for this data item, used for multi-value fields',
  `field_views_reference_book_target_id` varchar(250) NOT NULL,
  `field_views_reference_book_display_id` varchar(250) CHARACTER SET ascii DEFAULT NULL COMMENT 'The ID of the display.',
  `field_views_reference_book_data` longtext DEFAULT NULL COMMENT 'Serialized data.',
  PRIMARY KEY (`entity_id`,`deleted`,`delta`,`langcode`),
  KEY `bundle` (`bundle`),
  KEY `revision_id` (`revision_id`),
  KEY `field_views_reference_book_display_id` (`field_views_reference_book_display_id`),
  KEY `field_views_reference_book_target_id` (`field_views_reference_book_target_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Data storage for block_content field field_views_reference…' |

推荐答案

什么版本?什么字符集?和其他问题.在 5.5 版本之前，有 CHARACTER SET utf8，每个字符最多需要 3 个字节，但没有 utf8mb4，最多需要 4 个.5.7(10.2?)，问题被解决"了.同时，索引被限制为 767 字节；不是 3072.

What version? What CHARACTER SET? And other issues. Before version 5.5, there was CHARACTER SET utf8, which needs up to 3 bytes per character, but not utf8mb4, which needs up to 4. With 5.7 (10.2?), the problem was 'solved'. Meanwhile, an index had been limited to 767 bytes; not it is 3072.

VARCHAR(500) 上的索引需要 1500 或 2000 个字节.

Your index on a VARCHAR(500) needs 1500 or 2000 bytes.

这是我可以做的 5 件事的清单:http://mysql.rjweb.org/doc.php/limits#767_limit_in_innodb_indexes

Here is my list of 5 things that could be done: http://mysql.rjweb.org/doc.php/limits#767_limit_in_innodb_indexes

您需要根据可以忍受的缺陷在其中进行选择.

You need to choose among them based on which flaw you can live with.

这篇关于字符串数据，右截断:1406 Data too long for column的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！