如何添加新的MySQL表列并创建索引?
要添加新的MySQL表列和索引,可以使用ALTER TABLE命令。
语法如下:
ALTER TABLE 你的表名 ADD COLUMN 你的列名 数据类型,
ADD INDEX(你的列名);
为了理解上述语法,让我们创建一个表。创建表的查询如下:
mysql> create table AddColumnAndIndexDemo
-> (
-> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> Name varchar(100),
-> Address varchar(200)
-> );
Query OK, 0 rows affected (0.81 sec)
现在可以检查表的描述。查询如下−
mysql> desc AddColumnAndIndexDemo;
以下是输出结果
+---------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+----------------+
| Id | int(11) | NO | PRI | NULL | auto_increment |
| Name | varchar(100) | YES | | NULL | |
| Address | varchar(200) | YES | | NULL | |
+---------+--------------+------+-----+---------+----------------+
3 rows in set (0.03 sec)
以下是添加新的MySQL表列和索引的查询
mysql> alter table AddColumnAndIndexDemo add column Age int, add index(Age);
Query OK, 0 rows affected (1.81 sec)
Records: 0 Duplicates: 0 Warnings: 0
再次检查表的描述。查询如下−
mysql> desc AddColumnAndIndexDemo;
以下是输出结果
+---------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+----------------+
| Id | int(11) | NO | PRI | NULL | auto_increment |
| Name | varchar(100) | YES | | NULL | |
| Address | varchar(200) | YES | | NULL | |
| Age | int(11) | YES | MUL | NULL | |
+---------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
使用SHOW命令从表中检查索引。查询如下−
mysql> show index from AddColumnAndIndexDemo;
以下是输出结果
+-----------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible |
+-----------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| addcolumnandindexdemo | 0 | PRIMARY | 1 | Id | A | 0 | NULL | NULL | | BTREE | | | YES |
| addcolumnandindexdemo | 1 | Age | 1 | Age | A | 0 | NULL | NULL | YES | BTREE | | | YES |
+-----------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
2 rows in set (0.16 sec)
阅读更多:MySQL 教程