How will changing the mysql field type from INT to VARCHAR affect data previously stored as an INT

mysql varchar
john k picture john k · Sep 19, 2011 · Viewed 43.2k times · Source

I need to update a field in my production db from INT to VARCHAR.

The information in the current INT field is sensitive and I want to ensure that I don't alter it inadvertently or destroy it while changing the table type. For example, a user may have INT 123456 stored in this column and I want to ensure that is accurately stored once converted to VARCHAR.

What is the recommended process for accomplishing this?

Is there anything I need to worry about when using something like this?

ALTER TABLE table_sample CHANGE col_sample col_sample VARCHAR;

Thanks in advance for any help.

Answer

mu is too short picture mu is too short · Sep 19, 2011

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 or VARCHAR 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.

Related questions

#1071 - Specified key was too long; max key length is 767 bytes

When I executed the following command: ALTER TABLE `mytable` ADD UNIQUE ( `column1` , `column2` ); I got this error message: #1071 - Specified key was too long; max key length is 767 bytes Information about column1 and column2: column1 varchar(20) utf8_general_ci column2 …

Read More
What is the MySQL VARCHAR max size?

I would like to know what the max size is for a MySQL VARCHAR type. I read that the max size is limited by the row size which is about 65k. I tried setting the field to varchar(20000) but it …

Read More
MySQL: Large VARCHAR vs. TEXT?

I've got a messages table in MySQL which records messages between users. Apart from the typical ids and message types (all integer types) I need to save the actual message text as either VARCHAR or TEXT. I'm setting a front-end …

Read More