Shrinking VARCHAR with MODIFY: data alignment and potential for data loss I'm using MODIFY to change (shrink) a number of VARCHAR fields of various lengths in various tables. In each case, the field is called ip and is a textual representation of an IPv4 or IPv6 address. To that end, I want to set the fields to VARCHAR(45), which permits an IPv6 address using IPv4 tunnelling. My syntax, for completeness:

MODIFY ip VARCHAR(45) NOT NULL default ''

My question is this: if a field has previously been set as VARCHAR(100) or VARCHAR(254) and has content (one IP address per line), is there likely to be data loss? My understanding of VARCHAR is that the data is stored at the start (leftmost) and changes in size take place at the end (rightmost), so MODIFY would make its changes from the end and leave the data intact, being at the start. As far as versions are concerned it's various versions of MySQL from 4.1 to current.

I'm pretty confident that I can shrink ip to VARCHAR(45) without zapping existing data. All that said, I'm still pretty new to this and would appreciate an additional pair of eyes.

Thank you in advance.
You should properly examine the data metrics
Lengths of ip values greater than 45
SELECT IFNULL(LENGTH(ip),'Total'),COUNT(1) rowcount FROM mytable
GROUP BY LENGTH(ip) HAVING LENGTH(ip) &gt; 45 WITH ROLLUP;

Suggested data type for ip using PROCEDURE ANALYSE()
SELECT ip FROM mytable PROCEDURE ANALYSE();

Whatever SELECT ip FROM mytable PROCEDURE ANALYSE(); reports is what you should use instead of just picking VARCHAR(45).
GIVE IT A TRY !!!