Automatically trimming length of string submitted to MySQL

Question

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?

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)

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

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)

Answer 1

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.

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.

Answer 2

MySQL by default does this already! It shouldn't in my view, but it does.

If it is giving you error messages instead of just truncating the data, then perhaps traditional mode is on. Run SELECT @@SESSION.sql_mode and see what it says. If it spits out

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, or connect using an account with SUPER privileges and issue SET GLOBAL sql_mode='modes'.

Answer 3

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

Answer 4

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