How to change the data type of a field in a table in mysql?

In mysql, you can use the "ALTER TABLE" statement with the "MODIFY" keyword to change the data type of a field in the table; the syntax format is "ALTER TABLE

MODIFY ".

##(Recommended tutorial:

mysql video tutorial)

In MySQL ,

ALTER TABLE statement can change the structure of the original table, such as adding or deleting columns, changing the original column type, renaming columns or tables, etc.

The syntax format is as follows:

ALTER TABLE <表名> [修改选项]

The syntax format for modifying options is as follows:

##{ ADD COLUMN
##| CHANGE COLUMN

• | ALTER COLUMN { SET DEFAULT | DROP DEFAULT }

• | MODIFY COLUMN

• | DROP COLUMN

• ##| RENAME TO

• | CHARACTER SET

• | COLLATE }

• ##Modify field data type

#Modifying the data type of a field is to convert the data type of the field into another data type. The syntax rules for modifying field data types in MySQL are as follows:

ALTER TABLE <表名> MODIFY <字段名> <数据类型>

Among them:

Table name: refers to the name of the table where the field whose data type is to be modified is located;

• Field name: refers to the field that needs to be modified;

• Data type: refers to the new data type of the modified field.

• Example:

Create a new student data table in the test database. The SQL statements and running results are as follows:

mysql> USE test;
Database changed
mysql> CREATE TABLE student (
    -> id INT(4),
    -> name VARCHAR(20),
    -> sex CHAR(1));
Query OK, 0 rows affected (0.09 sec)

Use DESC View the student table structure, the SQL statement and the running results are as follows:

mysql> DESC student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(4)      | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| sex   | char(1)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

Use ALTER TABLE to modify the structure of the student table, and change the data type of the name field from VARCHAR(20) to VARCHAR(30), the SQL statement and The running results are as follows.

mysql> ALTER TABLE student
    -> MODIFY name VARCHAR(30);
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> DESC student;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | YES  |     | NULL    |       |
| name   | varchar(30) | YES  |     | NULL    |       |
| sex    | char(1)     | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

After the statement is executed, it is found that the data type of the name field in the table student has been modified to VARCHAR(30), and the modification is successful.

The above is the detailed content of How to change the data type of a field in a table in mysql?. For more information, please follow other related articles on the PHP Chinese website!