MySQL 自动修剪提交给MySQL的字符串长度
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3186343/
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
Automatically trimming length of string submitted to MySQL
提问by Saul
When I submit a form field to a mySQL database is there a way to set the database to automatically discard any data in excess of the data field length?
当我向 mySQL 数据库提交表单字段时，有没有办法将数据库设置为自动丢弃超过数据字段长度的任何数据？
I know I can do it programatically, but can the database be set to discard the excess without throwning an error?
我知道我可以通过编程来完成，但是可以将数据库设置为丢弃多余的而不抛出错误吗？
EDIT for clarity
为清楚起见进行编辑
heres my insert statement
这是我的插入语句
<cfquery datasource='#arguments.dsn#' name="addPatient">
INSERT INTO patients(patientFirstname
,patientLastname
,nhsNumber
,patientDOB
,patientTitle
,address1
,address2
,address3
,address4
,postcode
,patientPhone1
)
VALUES (<cfqueryparam value="#arguments.patientFirstname#" cfsqltype="CF_SQL_LONGVARCHAR"/>
,<cfqueryparam value="#arguments.patientLastname#" cfsqltype="CF_SQL_LONGVARCHAR"/>
,<cfqueryparam value="#arguments.nhsNumber#" cfsqltype="CF_SQL_LONGVARCHAR"/>
,<cfqueryparam value="#arguments.patientDOB#" cfsqltype="CF_SQL_TIMESTAMP"/>
,<cfqueryparam value="#arguments.patientTitle#" cfsqltype="CF_SQL_LONGVARCHAR"/>
,<cfqueryparam value="#arguments.address1#" cfsqltype="CF_SQL_VARCHAR"/>
,<cfqueryparam value="#arguments.address2#" cfsqltype="CF_SQL_LONGVARCHAR"/>
,<cfqueryparam value="#arguments.address3#" cfsqltype="CF_SQL_LONGVARCHAR"/>
,<cfqueryparam value="#arguments.address4#" cfsqltype="CF_SQL_LONGVARCHAR"/>
,<cfqueryparam value="#arguments.postcode#" cfsqltype="CF_SQL_LONGVARCHAR"/>
,<cfqueryparam value="#arguments.patientPhone1#" cfsqltype="CF_SQL_VARCHAR"/>
)
</cfquery>
the table field patientPhone is VARCHAR(20)
表字段患者电话是 VARCHAR(20)
If I dont validate the submission progamatically and just bang in a form value 30 characters long I error out (when what i thought it would do is simply store the first 20 characters)
如果我不以编程方式验证提交，而只是输入 30 个字符长的表单值，我会出错（当我认为它会做的只是存储前 20 个字符时）
Data truncation: Data too long for column 'patientPhone1' at row 8
When I set up the db with the wizard I remember selecting innodb and transactional and traditional emulation (which was recommended if i remember correctly)
当我使用向导设置数据库时，我记得选择了 innodb 和事务和传统模拟（如果我没记错的话，推荐使用）
采纳答案by Peter Hanneman
Edit: This should not be the accepted answer.
编辑：这不应该是公认的答案。
Incorrect answer: MySQL will truncate any insert value that exceeds the specified column width.
错误答案：MySQL 将截断任何超过指定列宽的插入值。
This behavior should never be relied upon.
永远不应依赖这种行为。
回答by John P
You mean you want to set this in a data definition script for example when creating a table? According to the documentation you can't do this. Perhaps you could use a trigger to handle data before it is inserted? Something like this (I can not garantuee that the code below actually works):
你的意思是你想在数据定义脚本中设置它，例如在创建表时？根据文档，你不能这样做。也许您可以在插入数据之前使用触发器来处理数据？像这样的东西（我不能保证下面的代码确实有效）：
CREATE TRIGGER chopdata BEFORE INSERT ON mytable
FOR EACH ROW BEGIN
NEW.myfield = SELECT SUBSTRING(NEW.myfield,1,20)
END;
Else you can chop out the maximum length by using substring straight in your query/procedure:
否则，您可以通过在查询/过程中直接使用子字符串来削减最大长度：
SELECT SUBSTRING('your very long string goes here',1,20);
回答by Hammerite
MySQL by default does this already! It shouldn't in my view, but it does.
MySQL 默认已经这样做了！在我看来，这不应该，但确实如此。
If it is giving you error messages instead of just truncating the data, then perhaps traditional mode is on. Run SELECT @@SESSION.sql_modeand see what it says. If it spits out
如果它给您错误消息而不仅仅是截断数据，那么传统模式可能已开启。运行SELECT @@SESSION.sql_mode看看它说了什么。如果吐出来
STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER
then it is in traditional mode; issue SET SESSION sql_mode=''every time you connect, orconnect using an account with SUPER privileges and issue SET GLOBAL sql_mode='modes'.
然后是传统模式；SET SESSION sql_mode=''每次连接时发出，或使用具有 SUPER 权限的帐户连接并发出SET GLOBAL sql_mode='modes'.
回答by Itay Moav -Malimovka
Just set the DB field size to the max size you want to accept.
if you want max 20 chars, then set it to varchar(20) or char(20).
Just be careful with the utf8 inputs, some of them characters takes 2 places instead of one.
But, as you can read in comments. Choosing a utf8 charset will solve this.
只需将 DB 字段大小设置为您想要接受的最大大小。
如果您想要最多 20 个字符，请将其设置为 varchar(20) 或 char(20)。
请注意 utf8 输入，其中一些字符需要 2 个位置而不是一个。
但是，正如您可以在评论中阅读的那样。选择 utf8 字符集将解决这个问题。
If you are speaking about white spaces...Then usually you are suppose to use only one place in your code to do the insert/update/delete queries. In this place you can easily apply programmaticlly any filter you want, and be sure it will be applied to all of your inserts.
如果你说的是空格......那么通常你会假设在你的代码中只使用一个地方来执行插入/更新/删除查询。在这个地方，您可以轻松地以编程方式应用您想要的任何过滤器，并确保它将应用于您的所有插入。
