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

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