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.

Answer 2

Note that you can convert anything to character type easily by using CONCAT:

select concat(columnName,'') as newColumnName from table;

The actual syntax you want for CONVERT is as follows:

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

There is no varchar type available to CONVERT or CAST, but casting to char without giving the field length should be the result you want.