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 ccc43248daffbac9770dee47fdaff697 MODIFY 37247d2cd6bb520a55be9c516c333e44".

##(Recommended tutorial:

mysql video tutorial)

In MySQL ,

ALTER TABLEstatement 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 895a828a5767d0ff83c19d6a93c7da7c ac32f71863a4f170235d8bf07c056a74 de792e514673c1077a498e8f6aba0987 5be509b072e8a62aa604bb901d2ff1a6

• | ALTER COLUMN 895a828a5767d0ff83c19d6a93c7da7c { SET DEFAULT 47292eb776f1048422ffa13791917062 | DROP DEFAULT }

• | MODIFY COLUMN 895a828a5767d0ff83c19d6a93c7da7c 30690cee1a11d5dfbdced93b89f678ee

• | DROP COLUMN 4856a503a57d954e03d677c32e973360

• ##| RENAME TO f299c995c5c851e72bb99c2ea611eb31

• | CHARACTER SET 762c527a1437241e2d9df6561b2a9a7e

• | COLLATE 7986e35b966c8be17ac1da11246800d7 }

• ##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!