Mysql 简明教程
MySQL - Change Column Type
在 MySQL 中，用户可以灵活地修改字段的数据类型。当您最初为新数据库表中的列设置了错误的数据类型，或者需要调整特定列中值的存储大小时，这将非常有用。
In MySQL, users have the flexibility to modify the data type of a field. This is useful when you initially set the wrong data type for a column in a new database table, or when you need to adjust the storage size for the values in a particular column.
您可以使用 ALTER TABLE 命令修改或更新 MySQL 表中的列类型。
You can modify or update the column type in a MySQL table, using the ALTER TABLE command.
The MySQL ALTER TABLE Command
ALTER TABLE 命令用于修改与数据库表相关的任何数据。这些数据可以是表中的记录或表定义本身。更改列的数据类型是可以使用 ALTER TABLE 命令执行的此类修改之一。
The ALTER TABLE command is used to modify any data related to a database table. This data can either be records in the table or the table definition itself. Changing the data type of a column is one such modification that can be performed using the ALTER TABLE command.
有两种方法可以更改列的数据类型：ALTER TABLE… MODIFY 命令和 ALTER TABLE… CHANGE 命令。
There are two methods to change a column’s data type: the ALTER TABLE… MODIFY command and the ALTER TABLE… CHANGE command.
The MySQL ALTER TABLE… MODIFY Command
您可以将 MODIFY 方法与 ALTER TABLE 语句结合使用来更改列/字段数据类型。
You can use MODIFY method with the ALTER TABLE statement to change the column/field data type.
Syntax
Syntax
以下是使用 ALTER TABLE… MODIFY 命令更改列类型时的基本语法 -
Following is the basic syntax to change the column type using ALTER TABLE… MODIFY command −
ALTER TABLE table_name MODIFY column_name new_datatype;Example
假设您已使用以下查询创建了一个名为“test_table”的表 -
Suppose you have created a table named 'test_table' using the following query −
CREATE TABLE test_table (
field1 INT,
field2 VARCHAR(100),
field3 DATE,
PRIMARY KEY(field1)
);若要检查表是否已成功创建，请使用 DESC 命令，如下所示 -
To check if the table is successfully created or not, use the DESC command as shown below −
DESC test_table;这将显示表结构，如下所示 -
This will display the table structure as follows −
现在，假设您想将 'field2' 的数据类型从 VARCHAR(100) 更改为 TEXT 以使其更简洁。您可以使用 ALTER TABLE… MODIFY 查询来执行此操作，如下所示 -
Now, let us say you want to change the data type of 'field2' from VARCHAR(100) to TEXT for more simplicity. You can do this using the ALTER TABLE… MODIFY query as follows −
ALTER TABLE test_table MODIFY field2 TEXT;以下是所获得的输出 −
Following is the output obtained −
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0若要检查列数据类型是否已成功更改，请使用 DESC 命令，如下所示 -
To check if the column data type is successfully changed, use the DESC command as shown below −
DESC test_table;这将显示修改后的表结构，如下所示 -
This will display the modified table structure as follows −
The MySQL ALTER TABLE… CHANGE Command
您还可以将 CHANGE 方法与 ALTER TABLE 语句结合使用来修改列的数据类型。此方法用于在创建表之后修改与表相关的所有数据。
You can also use the CHANGE method with the ALTER TABLE statement to modify a column’s data type. This method is used to alter all the data related to table after it is created.
CHANGE 和 MODIFY 方法之间的唯一区别在于，MODIFY 方法不能重命名列，而 CHANGE 方法可以。
The only difference between CHANGE and MODIFY methods is that the MODIFY method cannot rename a column whereas the CHANGE method can.
Syntax
Syntax
以下是使用 ALTER TABLE… CHANGE 命令更改列类型的基本语法 −
Following is the basic syntax to change the column type using ALTER TABLE… CHANGE command −
ALTER TABLE table_name CHANGE column_name column_name new_datatype;在使用 CHANGE 方法时，您必须记住在查询中两次指定列名称。
You must remember to specify the column name twice in the query whenever the CHANGE method is used.
Example
在此示例中，我们使用 ALTER TABLE 命令中的 CHANGE 方法更改了“test_table”中“field3”的数据类型。首先，让我们使用 DESC 命令观察表的当前定义 −
In this example, we are changing the datatype of 'field3' in the 'test_table' using the CHANGE method in ALTER TABLE command. Firstly, let us observe current definition of the table using DESC command −
DESC test_table;以下是产生的表 −
Following is the table produced −
现在，使用 ALTER TABLE… CHANGE 方法，更改“field3”的列类型 −
Now, using ALTER TABLE… CHANGE method, change the column type of 'field3' −
ALTER TABLE test_table CHANGE field3 field3 VARCHAR(20);上述代码的输出如下：
Output of the above code is as follows −
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0您可以使用 DESC 命令验证列数据类型是否已更改，如下所示 −
You can verify whether the column data type is changed using the DESC command as shown below −
DESC test_table;我们获得以下表作为输出 −
We get the following table as an output −