Automatically trimming CHAR data on insert into VARCHAR column

Question

We recently moved our MySQL databases (~v3.5) from a locally hosted server to GoDaddy.

The transfer went fine, but there is an old automated process that runs which transfers/syncs data from an old FoxPro database to a MySQL database. The issue that is happening is the CHAR columns are adding white space to the VARCHAR columns. Which gives fun results on the web page.

While I plan to make it so the source data gets trimmed down prior to the transfer. In the meantime, is there an option I can enable in my.ini (or elsewhere) that will automatically trim down string data that has white space at the end?

Answer 1

You may be able to get this to work by using MySQL triggers. Here's some documentation: https://dev.mysql.com/doc/refman/5.5/en/create-trigger.html

And an examaple:

DROP TABLE IF EXISTS tmp;
CREATE TABLE tmp (`txt` varchar(50));
DROP TRIGGER IF EXISTS insert_tmp;
CREATE TRIGGER insert_tmp BEFORE INSERT ON tmp FOR EACH ROW SET NEW.txt=TRIM(NEW.txt);
INSERT INTO tmp VALUES ("        abc   "), ("efg      ");
SELECT txt, LENGTH(txt) FROM tmp;

Output:

|abc|3|
|efg|3|

Answer 2

Not an answer, but at least you will know why this is happening:

11.4.1 The CHAR and VARCHAR Types

VARCHAR values are not padded when they are stored. Handling of trailing spaces is version-dependent. As of MySQL 5.0.3, trailing spaces are retained when values are stored and retrieved, in conformance with standard SQL. Before MySQL 5.0.3, trailing spaces are removed from values when they are stored into a VARCHAR column; this means that the spaces also are absent from retrieved values.

If the "old automated process that runs which transfers/syncs data from an old FoxPro database to a MySQL database" is actually written in Visual Foxpro, the change is trivial, just add an RTRIM(field) to each varchar field in the (I guess) sqlexec command string.

For "something that can be done on the target side for the time being" check the link comments:

Post mySQL 5.0.3, if you are stuck with trailing whitespace in a VARCHAR column, you can remove it through a two step process:

1) alter column type to char

2) alter column type back to varchar.