Shulou(Shulou.com)06/01 Report--

This article will explain in detail the method of modifying fields in mysql table for everyone. Xiaobian thinks it is quite practical, so share it with you for reference. I hope you can gain something after reading this article.

In mysql tables, field names can be modified using the ALTER TABLE table name CHANGE old field name new field name data type; statement; field data types can be modified using the ALTER TABLE table name MODIFY field name new data type; statement.

Modify field name

The syntax rules for modifying table field names in MySQL are as follows:

ALTER TABLE table name CHANGE old field name new field name data type;

Of which:

Old field name: refers to the field name before modification;

New field name: refers to the modified field name;

Data type: refers to the modified data type. If it is not necessary to modify the data type of the field, the data type can be set to be the same as the original data type, but the data type cannot be empty.

Example 1

Use ALTER TABLE to modify the structure of table tb_emp1, change the col1 field name to col3, and change the data type to CHAR(30). The SQL statement and running results are shown below.

mysql> ALTER TABLE tb_emp1 -> CHANGE col1 col3 CHAR(30);Query OK, 0 rows affected (0.76 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> DESC tb_emp1;+--------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+--------+-------------+------+-----+---------+-------+| col3 | char(30) | YES | | NULL | || id | int(11) | YES | | NULL | || name | varchar(30) | YES | | NULL | || deptId | int(11) | YES | | NULL | || salary | float | YES | | NULL | |+--------+-------------+------+-----+---------+-------+5 rows in set (0.01 sec)

CHANGE can also modify only the data type to achieve the same effect as MODIFY, by setting the "new field name" and "old field name" in the SQL statement to the same name and changing only the "data type".

Tip: Because different types of data are stored in different ways and lengths in the machine, modifying the data type may affect the existing data records in the data table. Therefore, when there is already data in the data table, do not easily modify the data type.

Modify field data type

Changing a field's data type is converting the field's data type to another data type. The syntax rules for modifying field data types in MySQL are as follows:

ALTER TABLE table name MODIFY field name new data type;

Of which:

Table Name: refers to the name of the table in which the field of data type to be modified is located;

Field Name: refers to the field to be modified;

New data type: refers to the new data type of the modified field.

Example 2

Use ALTER TABLE to modify the structure of table tb_emp1, and change the data type of name field from VARCHAR(22) to VARCHAR(30). The SQL statement and running results are shown below.

mysql> ALTER TABLE tb_emp1 -> MODIFY name VARCHAR(30);Query OK, 0 rows affected (0.15 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> DESC tb_emp1;+--------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+--------+-------------+------+-----+---------+-------+| col1 | int(11) | YES | | NULL | || id | int(11) | YES | | NULL | || name | varchar(30) | YES | | NULL | || col2 | int(11) | YES | | NULL | || deptId | int(11) | YES | | NULL | || salary | float | 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 table tb_emp1 has been modified to VARCHAR(30), and the modification is successful.

The method of modifying fields in mysql table is shared here. I hope the above content can be of some help to everyone and can learn more knowledge. If you think the article is good, you can share it so that more people can see it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.