错误代码:1406.数据对于列而言太长-MySQL [英] Error Code: 1406. Data too long for column - MySQL

问题描述

错误代码:1406.数据对于列而言太长了

Error Code: 1406. Data too long for column

CREATE  TABLE `TEST` 
(

  `idTEST` INT NOT NULL ,

  `TESTcol` VARCHAR(45) NULL ,

  PRIMARY KEY (`idTEST`) 
);

现在Insert一些值

INSERT INTO TEST
VALUES
(
    1,
    'Vikas'
)

select 

SELECT * FROM TEST;

插入记录多于length

INSERT INTO TEST
VALUES
(
    2,
    'Vikas Kumar Gupta Kratika Shukla Kritika Shukla'
)

如果我们select length

SELECT LENGTH('Vikas Kumar Gupta Kratika Shukla Kritika Shukla')

 '47'

它正在显示错误消息

错误代码:1406.数据对于列而言太长了

但是我的期望是，我想在表格中至少插入前45个字符

But what is my expectation is, I want to insert at least first 45 characters in Table

如果问题不清楚，请告诉我.

please let me know if the question is not clear.

我知道此错误的原因.我正在尝试插入比数据类型长度更多的值.

I know the cause of this error. I am trying to insert values more than the length of datatype.

我想要MySQL中的解决方案，因为在MS SQL中是可能的.因此，我希望它也会出现在 MySQL 中.

I want solution in MySQL as It is possible in MS SQL. So I hope it would also be in MySQL.

推荐答案

MySQL将截断任何超出指定列宽的插入值.

MySQL will truncate any insert value that exceeds the specified column width.

要使其无误，请尝试将您的SQL mode切换为不使用STRICT.

to make this without error try switch your SQL mode to not use STRICT.

MySQL参考手册

更改模式

这可以通过两种方式完成:

This can be done in two ways:

1. 在MySQL安装目录中打开您的my.ini(Windows)或my.cnf(Unix)文件，然后查找文本"sql-mode".

1. Open your my.ini (Windows) or my.cnf (Unix) file within the MySQL installation directory, and look for the text "sql-mode".

查找:

代码:

# Set the SQL mode to strict 
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

替换为:

代码:

# Set the SQL mode to strict 
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

或

1. 您可以在数据库管理工具(例如phpMyAdmin)中运行SQL查询:

代码:

SET @@global.sql_mode= '';

这篇关于错误代码:1406.数据对于列而言太长-MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！