How to convert MySQL field types?

Question

I have already come across the convert function. As I understand it, the basic syntax is:

select convert(columnName, targetFieldType) as newColumnName from table;

Running this command doesn't give me any errors, but when I check the data types they are unchanged. Even when I use commit; the data remains unchanged. In particular, I'm trying to convert data with long type to varchar. Any ideas?

Answer 1

The given SELECT query will return the value in the new type, but it doesn't change the field type of the table in your database.

If you want to permanently change the table on disk, use:

ALTER TABLE table CHANGE columnName newColumnName targetFieldType NOT NULL;

Note that for large tables, this can take a while, because MySQL rewrites the entire table.

Note: remove the NOT NULL qualifier if you also want to allow NULL values in this column.

For more information, see ALTER TABLE Syntax in MySQL Reference Manual.