MySQL太长的varchar截断/错误设置

2021-11-20 00:00:00 settings mysql varchar

我有两个 MySQL 实例.当数据太长时,第一个在插入时截断字符串.第二个引发错误:

I have two MySQL instances. The 1st one truncates strings on insert when data is too long. The 2nd one raises an error:

ERROR 1406 (22001): 第 1 行 'xxx' 列的数据太长

我希望第二个也截断数据.是否有任何 MySQL 设置来管理此行为?

I want the 2nd one to truncate the data as well. Is there any MySQL setting to manage this behavior?

推荐答案

您可以禁用 STRICT_TRANS_TABLESSTRICT_ALL_TABLES.这允许自动截断插入的字符串.

You can disable STRICT_TRANS_TABLES and STRICT_ALL_TABLES. This allows the automatic truncation of the inserted string.

引用自 MySQL 文档.

严格模式控制 MySQL 如何处理无效或缺失值数据更改语句,例如 INSERT 或 UPDATE.一个值可以是由于多种原因无效.例如,它可能有错误的数据列的类型,否则可能超出范围.缺少一个值当要插入的新行不包含非 NULL 值时在其定义中没有显式 DEFAULT 子句的列.(为一个NULL 列,如果缺少值则插入 NULL.)

Strict mode controls how MySQL handles invalid or missing values in data-change statements such as INSERT or UPDATE. A value can be invalid for several reasons. For example, it might have the wrong data type for the column, or it might be out of range. A value is missing when a new row to be inserted does not contain a value for a non-NULL column that has no explicit DEFAULT clause in its definition. (For a NULL column, NULL is inserted if the value is missing.)

参考:MySQL Server SQL模式

相关文章