添加新的MySQL表列并创建索引？
要添加新的MySQL表列和索引，可以使用ALTERTABLE命令。
语法如下
ALTER TABLE yourTableName ADD COLUMN yourColumnName dataType, ADD INDEX(yourColumnName );
为了理解上述语法，让我们创建一个表。创建表的查询如下
create table AddColumnAndIndexDemo -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> Name varchar(100), -> Address varchar(200) -> );
现在，您可以检查表的描述。查询如下-
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表列和索引
alter table AddColumnAndIndexDemo add column Age int, add index(Age); Records: 0 Duplicates: 0 Warnings: 0
再次检查表说明。查询如下-
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命令从表中检查索引。查询如下-
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)