MySQL MySQL太长varchar截断/错误设置
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18459184/
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
MySQL too long varchar truncation/error setting
提问by Maksym Polshcha
I have two MySQL instances. The 1st one truncates strings on insert when data is too long. The 2nd one raises an error:
我有两个 MySQL 实例。当数据太长时，第一个在插入时截断字符串。第二个引发错误：
ERROR 1406 (22001): Data too long for column 'xxx' at row 1
ERROR 1406 (22001): Data too long for column 'xxx' at row 1
I want the 2nd one to truncate the data as well. Is there any MySQL setting to manage this behavior?
我希望第二个也截断数据。是否有任何 MySQL 设置来管理此行为？
回答by bansi
You can disable STRICT_TRANS_TABLESand STRICT_ALL_TABLES. This allows the automatic truncation of the inserted string.
您可以禁用STRICT_TRANS_TABLES和STRICT_ALL_TABLES。这允许自动截断插入的字符串。
Quote from MySQL Documantation.
引自 MySQL 文档。
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 如何处理数据更改语句（如 INSERT 或 UPDATE）中的无效或缺失值。由于多种原因，值可能无效。例如，它可能具有错误的列数据类型，或者可能超出范围。当要插入的新行不包含在其定义中没有显式 DEFAULT 子句的非 NULL 列的值时，缺少值。（对于 NULL 列，如果缺少值，则插入 NULL。）
Reference: MySQL Server SQL Modes
回答by metalfight - user868766
If strict SQL mode is not enabled and you assign a value to a CHAR or VARCHAR column that exceeds the column's maximum length, the value is truncated to fit and a warning is generated. For truncation of nonspace characters, you can cause an error to occur (rather than a warning) and suppress insertion of the value by using strict SQL mode. See Section 6.1.7, “Server SQL Modes”.
如果未启用严格 SQL 模式，并且您为 CHAR 或 VARCHAR 列分配的值超过该列的最大长度，则该值将被截断以适合并生成警告。对于非空格字符的截断，您可以使用严格的 SQL 模式导致发生错误（而不是警告）并禁止插入值。请参见第 6.1.7 节，“服务器 SQL 模式”。
How you can change it: http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html
如何更改它：http: //dev.mysql.com/doc/refman/5.7/en/sql-mode.html
Found two ways to disable strict mode:
找到了两种禁用严格模式的方法：
add below to my.cnf
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
way is using mysql console.
SET @@global.sql_mode= '';
将下面添加到 my.cnf
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
方法是使用mysql控制台。
SET @@global.sql_mode='';
Please test them before running on production environment.
请在生产环境中运行之前对其进行测试。
回答by Mikel Tawfik
if you use cpanel ,
如果您使用 cpanel ，
replace
代替
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
into /usr/my.cnf
进入/usr/my.cnf
to
到
sql-mode=""
run
跑
/etc/init.d/mysql restart
