How to modify the table name and column name of mysql?

In mysql, you can modify the table name through the "ALTER TABLE old table name RENAME new table name;" statement, and through the "ALTER TABLE table name CHANGE old field name/column name new field name/column name new Data type;" statement to modify the column name.

Modify the table name of mysql

MySQL implements the table name through the ALTER TABLE statement Modification, the syntax rules are as follows:

ALTER TABLE <旧表名> RENAME [TO] <新表名>;

Among them, TO is an optional parameter, whether it is used or not will not affect the result.

Example 1

Use ALTER TABLE to rename the data table student to tb_students_info. The SQL statement and running results are as follows.

mysql> ALTER TABLE student RENAME TO tb_students_info;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW TABLES;
+------------------+
| Tables_in_test   |
+------------------+
| tb_students_info |
+------------------+
1 row in set (0.00 sec)

Tip: Modifying the table name does not modify the structure of the table, so the structure of the table after the name is modified is the same as the table before the name is modified. Users can use the DESC command to view the modified table structure,

Modify the column name (field name) of mysql

MySQL data A table is composed of rows and columns. The "columns" of the table are usually called fields, and the "rows" of the table are called records.

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 name before modification;

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

• New data type: refers to the modified data type, if there is no need to modify the field The data type can be set to the same as the original data type, but the data type cannot be empty.

Example 2

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 the data table is already When there is data, do not modify the data type easily.

Recommended tutorial: mysql video tutorial

The above is the detailed content of How to modify the table name and column name of mysql?. For more information, please follow other related articles on the PHP Chinese website!