如何转换MySQL字段类型?

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?

运行这个命令不会给我任何错误，但是当我检查数据类型时，它们是不变的。甚至当我使用commit的时候;数据保持不变。特别是，我正在尝试将长类型的数据转换为varchar。什么好主意吗?

2 个解决方案

#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.

给定的SELECT查询将返回新类型中的值，但不会更改数据库中表的字段类型。

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.

注意，对于大型表，这可能需要一段时间，因为MySQL会重写整个表。

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.

有关更多信息，请参阅MySQL参考手册中的ALTER TABLE语法。

#2

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

注意，您可以通过使用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.

没有varchar类型可用来转换或转换，但是不给出字段长度的情况下对char的转换应该是您想要的结果。

#1