How to modify the attribute name in mysql

In mysql, you can modify the attribute name through the alter table command. The modification command statement is "alter table table name change original column name new column name". You can directly modify the column attributes of the table by executing this statement in the future. name.

The operating environment of this tutorial: Windows 10 system, mysql5.5 version, Dell G3 computer.

How to modify the attribute name in mysql?

Operations to modify table names, table attribute names, etc. in MySQL

alter table 表名 change 原列名 新列名  类型； --修改表的列属性名
alter table 表名 modify 列名 类型 ；  --修改表的类类型
alter table 表名 drop 列名；  --删除表的某一列
alter table 表名 add  列名 类型；--添加某一列
alter table 表名 rename 新表名； --修改表名

Related expansion introduction:

MySQL ALTER command

When we need When modifying the data table name or modifying the data table fields, you need to use the MySQL ALTER command.

Before starting this tutorial, let us first create a table named: testalter_tbl.

root@host# mysql -u root -p password;
Enter password:*******
mysql> use RUNOOB;
Database changed
mysql> create table testalter_tbl
    -> (
    -> i INT,
    -> c CHAR(1)
    -> );
Query OK, 0 rows affected (0.05 sec)
mysql> SHOW COLUMNS FROM testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| i     | int(11) | YES  |     | NULL    |       |
| c     | char(1) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

Delete, add or modify table fields

The following command uses the ALTER command and DROP clause to delete the i field of the above created table:

mysql> ALTER TABLE testalter_tbl  DROP i;

If in the data table If there is only one field left, you cannot use DROP to delete the field.

The ADD clause is used in MySQL to add columns to the data table. The following example adds the i field to the table testalter_tbl and defines the data type:

mysql> ALTER TABLE testalter_tbl ADD i INT;

After executing the above command, the i field will Automatically added to the end of the data table field.

mysql> SHOW COLUMNS FROM testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c     | char(1) | YES  |     | NULL    |       |
| i     | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

If you need to specify the location of the new field, you can use the keywords FIRST (set in the first column) and AFTER field name (set after a certain field) provided by MySQL.

Try the following ALTER TABLE statement, and after successful execution, use SHOW COLUMNS to view the changes in the table structure:

ALTER TABLE testalter_tbl DROP i;
ALTER TABLE testalter_tbl ADD i INT FIRST;
ALTER TABLE testalter_tbl DROP i;
ALTER TABLE testalter_tbl ADD i INT AFTER c;

The FIRST and AFTER keywords can be used in the ADD and MODIFY clauses, so if you want To reset the position of a data table field, you need to first use DROP to delete the field and then use ADD to add the field and set the position.

Modify the field type and name

If you need to modify the field type and name, you can use the MODIFY or CHANGE clause in the ALTER command.

For example, to change the type of field c from CHAR(1) to CHAR(10), you can execute the following command:

mysql> ALTER TABLE testalter_tbl MODIFY c CHAR(10);

Use the CHANGE clause, the syntax is very different. After the CHANGE keyword, follow the field name you want to modify, and then specify the new field name and type. Try the following example:

mysql> ALTER TABLE testalter_tbl CHANGE i j BIGINT;
mysql> ALTER TABLE testalter_tbl CHANGE j j INT;

The impact of ALTER TABLE on Null values ​​and default values

When you modify a field, you can specify whether to include a value or whether to set a default value.

In the following example, the specified field j is NOT NULL and the default value is 100.

mysql> ALTER TABLE testalter_tbl 
    -> MODIFY j BIGINT NOT NULL DEFAULT 100;

If you do not set a default value, MySQL will automatically set the field to NULL by default.

Modify the default value of the field

You can use ALTER to modify the default value of the field. Try the following example:

mysql> ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
mysql> SHOW COLUMNS FROM testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c     | char(1) | YES  |     | NULL    |       |
| i     | int(11) | YES  |     | 1000    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

You can also use the ALTER command and DROP clause to delete The default value of the field, as shown in the following example:

mysql> ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;
mysql> SHOW COLUMNS FROM testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c     | char(1) | YES  |     | NULL    |       |
| i     | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
Changing a Table Type:

To modify the data table type, you can use the ALTER command and TYPE clause to complete. Try the following example, we change the type of table testalter_tbl to MYISAM:

Note: You can use the SHOW TABLE STATUS statement to view the data table type.

mysql> ALTER TABLE testalter_tbl ENGINE = MYISAM;
mysql>  SHOW TABLE STATUS LIKE 'testalter_tbl'\G
*************************** 1. row ****************
           Name: testalter_tbl
           Type: MyISAM
     Row_format: Fixed
           Rows: 0
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 25769803775
   Index_length: 1024
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2007-06-03 08:04:36
    Update_time: 2007-06-03 08:04:36
     Check_time: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

Modify the table name

If you need to modify the name of the data table, you can use the RENAME clause in the ALTER TABLE statement.

Try the following example to rename the data table testalter_tbl to alter_tbl:

mysql> ALTER TABLE testalter_tbl RENAME TO alter_tbl;

Recommended learning: "MySQL Video Tutorial"

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