MySQL “ERROR 1406: 1406: Data too long for column”，但它不应该是？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/34418870/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
"ERROR 1406: 1406: Data too long for column" but it shouldn't be?
提问by Vrankela
I have the following table structure:
我有以下表结构：
DROP TABLE IF EXISTS `tblusers`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `tblusers` (
`UserID` int(5) NOT NULL AUTO_INCREMENT,
`ContactPersonID` int(5) NOT NULL,
`NameOfUser` varchar(70) NOT NULL,
`LegalForm` varchar(70) DEFAULT NULL,
`Address` varchar(70) DEFAULT NULL,
`City` varchar(50) DEFAULT NULL,
`Postal` int(8) DEFAULT NULL,
`Country` varchar(50) DEFAULT NULL,
`VatNum` int(10) DEFAULT NULL,
`Username` varchar(30) NOT NULL,
`Password` varchar(20) NOT NULL,
`Email` varchar(40) NOT NULL,
`Website` varchar(40) DEFAULT NULL,
`IsSeller` bit(1) DEFAULT NULL,
`IsBuyer` bit(1) DEFAULT NULL,
`IsAdmin` bit(1) DEFAULT NULL,
`Description` text,
PRIMARY KEY (`UserID`),
KEY `ContactPersonID` (`ContactPersonID`),
CONSTRAINT `tblusers_tblpersons` FOREIGN KEY (`ContactPersonID`) REFERENCES `tblpersons` (`PersonID`)
) ENGINE=InnoDB AUTO_INCREMENT=87 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
Then once I create a user from the UI of my application, I have to manually set the very first admin, and this is the only time I am doing this directly from the DB, all the rest is envisioned to be done from the UI (granting admin privileges):
然后，一旦我从我的应用程序的 UI 创建了一个用户，我就必须手动设置第一个管理员，这是我唯一一次直接从数据库执行此操作，其余的都设想从 UI 完成（授予管理员权限）：
UPDATE `tblusers` SET `IsAdmin`='1' WHERE `UserID`='79';
but then I get:
但后来我得到：
Operation failed: There was an error while applying the SQL script to the database.
Executing:
UPDATE `trace`.`tblusers` SET `IsAdmin`='1' WHERE `UserID`='79';
ERROR 1406: 1406: Data too long for column 'IsAdmin' at row 1
SQL Statement:
UPDATE `trace`.`tblusers` SET `IsAdmin`='1' WHERE `UserID`='79'
Which doesn't make sense because I am doing the exact same thing on other machines and it works like a charm. The only difference is that in this scenario I have mysql 5.7 server whereas I have 5.6 versions on the machines that this does work.
这是没有意义的，因为我在其他机器上做着完全相同的事情，而且它的作用就像一个魅力。唯一的区别是，在这种情况下，我有 mysql 5.7 服务器，而我的机器上有 5.6 版本，这确实有效。
I tried the followingsolution but it didn't work for me. Besides that, the my.ini file is unchanged in the 5.6 machine where it does work.
我尝试了以下解决方案，但对我不起作用。除此之外，my.ini 文件在它工作的 5.6 机器中没有改变。
Downgrading to 5.6 is out of the question.I need a real solution here please.
降级到 5.6 是不可能的。我需要一个真正的解决方案。
回答by Akshey Bhat
isadmin is a column of type bitand you are storing a value of type varcharin it which is of larger size than bit. modify query as follows:-
isadmin 是一个 type 列，bit您正在其中存储一个 type 的值，该值的varchar大小大于bit. 修改查询如下：-
UPDATE `tblusers` SET `IsAdmin`=b'1' WHERE `UserID`='79';
回答by Shadow
IsAdmin has the datatype of bit(1), yet you are assigning the string '1' to it. Indicate that you are assigning a bit valueto it by preceeding the '1' with b or use 0b format:
IsAdmin 的数据类型为 bit(1)，但您要为其分配字符串“1”。通过在 '1' 前面加上 b 或使用 0b 格式来表示您正在为其分配一个位值：
UPDATE `tblusers` SET `IsAdmin`=b'1' WHERE `UserID`='79';
or
或者
UPDATE `tblusers` SET `IsAdmin`=0b1 WHERE `UserID`='79';
The reason for this behaviour is probably that strict_all_tables or strict_trans_tablessetting is enabled on the v5.7 mysql server:
这种行为的原因可能是在 v5.7 mysql 服务器上启用了strict_all_tables 或 strict_trans_tables设置：
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.) Strict mode also affects DDL statements such as CREATE TABLE.
严格模式控制 MySQL 如何处理数据更改语句（如 INSERT 或 UPDATE）中的无效或缺失值。由于多种原因，值可能无效。例如，它可能具有错误的列数据类型，或者可能超出范围。当要插入的新行不包含在其定义中没有显式 DEFAULT 子句的非 NULL 列的值时，缺少值。（对于 NULL 列，如果缺少值，则插入 NULL。）严格模式也会影响 DDL 语句，例如 CREATE TABLE。
回答by Prem Kumar
The BIT data type is used to store bit values. A type of BIT(M) enables storage of M-bit values. M can range from 1 to 64.
BIT 数据类型用于存储位值。一种类型的 BIT(M) 能够存储 M 位值。M 的范围是 1 到 64。
UPDATE tblusersSET IsAdmin=b'1' WHERE UserID='012';
更新tblusersSET IsAdmin=b'1' WHERE UserID='012';
UPDATE tblusersSET IsAdmin=b'0' WHERE UserID='012';
更新tblusersSET IsAdmin=b'0' WHERE UserID='012';
