How to add new columns to mysql table?

You can use the "ALTER TABLE" statement in mysql to add new columns to the table. The syntax format is "ALTER TABLE table name ADD new field name data type [constraints];"; the default is to add it at the end of the table New columns can be added at the beginning if used with the FIRST keyword.

(Recommended tutorial: mysql video tutorial)

mysql adds new columns to the table Method:

1. Add a new column (field) at the end

MySQL defaults to the last position of the table Add new fields.

A complete field includes field name, data type and constraints.

The syntax format of MySQL for adding fields at the end is as follows:

ALTER TABLE 表名 ADD 新字段名 数据类型 [约束条件];

The description of the syntax format is as follows:                                    

• Table name: is the name of the data table ;

• New field name: the name of the field to be added;

• Data type: the data that the field to be added can store data Type;

• [Constraints]: Optional, used to constrain the added fields.

This syntax format adds a new field at the last position of the table (after the last column) by default.

Example:

Use DESC to view the structure of a student table

mysql> DESC student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(20) | YES  |     | NULL    |       |
| sex   | char(1)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

Add an INT type field age at the end of the table

mysql> ALTER TABLE student ADD age INT(4);
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(20) | YES  |     | NULL    |       |
| sex   | char(1)     | YES  |     | NULL    |       |
| age   | int(4)      | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

Add a new column (field) at the beginning

If you want to add a new field at the beginning (in front of the first column), you can use the FIRST key word, the syntax format is as follows:

ALTER TABLE 表名 ADD 新字段名 数据类型 [约束条件] FIRST;

Note: The FIRST keyword is generally placed at the end of the statement.

Example:

Add the INT type field id in the first column of the table

mysql> ALTER TABLE student ADD id INT(4) FIRST;
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(4)      | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| sex   | char(1)     | YES  |     | NULL    |       |
| age   | int(4)      | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

In the middle Position to add a new column (field)

In addition to allowing fields to be added at the beginning and end of the table, MySQL also allows fields to be added in the middle (after the specified field). At this time You need to use the AFTER keyword, the syntax format is as follows:

ALTER TABLE 表名 ADD 新字段名 数据类型 [约束条件] AFTER 已经存在的字段名;

The function of AFTER is to add a new field after an existing field.

Note that you can only add a new field after an existing field, but not in front of it

For more programming-related knowledge, please visit : Introduction to Programming! !

The above is the detailed content of How to add new columns to mysql table?. For more information, please follow other related articles on the PHP Chinese website!