How will changing the mysql field type from INT to VARCHAR affect data previously stored as an INT
Solution 1:
Get your MySQL server into strict mode before you change the column type and make sure that your varchar(n)
column has a large enough n
to hold all of the integers when they're converted to strings. If you're not in strict mode then MySQL will silently truncate your data to fit your string size:
If strict SQL mode is not enabled and you assign a value to a
CHAR
orVARCHAR
column that exceeds the column's maximum length, the value is truncated to fit and a warning is generated. For truncation of nonspace characters, you can cause an error to occur (rather than a warning) and suppress insertion of the value by using strict SQL mode.
But if you get into strict mode first:
mysql> set sql_mode = 'STRICT_ALL_TABLES';
mysql> alter table table_sample change col_sample col_sample varchar(6);
You'll get a nice error message like this:
ERROR 1406 (22001): Data too long for column 'col_sample' at row ...
if your integers don't all fit in your varchar
.
And, of course, you will have a fresh verified backup of your database before you try to change the table. And by verified I mean that you have successfully restored your backup into a test database.