自动修剪提交给MySQL的字符串的长度 [英] Automatically trimming length of string submitted to MySQL

问题描述

当我向MySQL数据库提交表单字段时，是否可以将数据库设置为自动丢弃超出数据字段长度的任何数据?

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?

我知道我可以通过编程方式做到这一点，但是可以将数据库设置为丢弃多余的空间而不会引发错误吗?

I know I can do it programatically, but can the database be set to discard the excess without throwning an error?

为清楚起见而编辑

这是我的插入语句

<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>

表字段PatientPhone是VARCHAR(20)

the table field patientPhone is VARCHAR(20)

如果我不按时验证提交内容，而只敲了30个字符的表单值，我就会出错(当我认为这样做只是存储前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)

Data truncation: Data too long for column 'patientPhone1' at row 8

当我使用向导设置数据库时，我记得选择了innodb以及事务性和传统仿真(如果我没记错的话，建议您这样做)

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)

推荐答案

这不应被接受.

错误答案:MySQL将截断任何超出指定列宽的插入值.

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

这种行为永远都不能依靠.

This behavior should never be relied upon.

这篇关于自动修剪提交给MySQL的字符串的长度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！