How to modify fields in mysql table?

In the mysql table, you can use the "ALTER TABLE table name CHANGE old field name new field name data type;" statement to modify the field name; use the "ALTER TABLE table name MODIFY field name new data type;" statement to modify the field data type.

(Recommended tutorial: mysql video tutorial)

Modify field name

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

ALTER TABLE 表名 CHANGE 旧字段名 新字段名 数据类型;

Among them:

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

• New field name: refers to the modified field name;

• Data type: refers to the modified data type, if not needed To modify the data type of a field, you can set the data type to be the same as the original one, 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 as follows.

mysql> ALTER TABLE tb_emp1
    -> CHANGE col1 col3 CHAR(30);
Query OK, 0 rows affected (0.76 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> 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 only modify the data type to achieve the same effect as MODIFY. The method is to set the "new field name" and "old field name" in the SQL statement to the same name, and only change the "data type".

Tip: Since 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 modify the data type easily.

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;

• 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 the name field from VARCHAR(22) to VARCHAR( 30), the SQL statement and running results are as follows.

mysql> ALTER TABLE tb_emp1
    -> MODIFY name VARCHAR(30);
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> 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 above is the detailed content of How to modify fields in mysql table?. For more information, please follow other related articles on the PHP Chinese website!