创建 Key=MUL 的 MySQL 列?
mysqlmysqli database
您需要使用 ADD KEY 来创建 Key=MUL 的列。语法如下−
ALTER TABLE yourTableName MODIFY COLUMN yourColumnName data type, ADD KEY(yourColumnName);
为了理解上述语法，让我们创建一个表。创建表的查询如下 −
mysql> create table Instructor -> ( -> Instructor_Id int, -> Instructor_Name varchar(30), -> Instructor_CourseName varchar(100) -> ); Query OK, 0 rows affected (0.63 sec)
现在您可以查看表格的描述，列 KEY 没有任何 MUL 键。查询如下，使用 DESC 命令检查表格的描述。
mysql> desc Instructor;
以下是输出 −
+-----------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------------+--------------+------+-----+---------+-------+ | Instructor_Id | int(11) | YES | | NULL | | | Instructor_Name | varchar(30) | YES | | NULL | | | Instructor_CourseName | varchar(100) | YES | | NULL | | +-----------------------+--------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
以下是在 MySQL 中创建 Key=MUL 列的查询。将列 ‘Instructor_Id’ 设为 Key=MUL。查询如下 −
mysql> alter table Instructor modify column Instructor_Id int NOT NULL AUTO_INCREMENT, -> add key(Instructor_Id); Query OK, 0 rows affected (2.88 sec) Records: 0 Duplicates: 0 Warnings: 0
现在再次检查表描述。查询如下 −
mysql> desc Instructor;
以下是输出 −
+-----------------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------------+--------------+------+-----+---------+----------------+ | Instructor_Id | int(11) | NO | MUL | NULL | auto_increment | | Instructor_Name | varchar(30) | YES | | NULL | | | Instructor_CourseName | varchar(100) | YES | | NULL | | +-----------------------+--------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec)
要添加非主键，您需要使用以下查询 −
mysql> alter table Instructor modify column Instructor_Name varchar(30) NOT NULL, -> add key(Instructor_Name); Query OK, 0 rows affected (2.77 sec) Records: 0 Duplicates: 0 Warnings: 0
现在再次检查表描述。查询如下 −
mysql> desc Instructor;
以下是显示 Key 为其中一个字段的 MUL 的输出 −
+-----------------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------------+--------------+------+-----+---------+----------------+ | Instructor_Id | int(11) | NO | MUL | NULL | auto_increment | | Instructor_Name | varchar(30) | NO | MUL | NULL | | | Instructor_CourseName | varchar(100) | YES | | NULL | | +-----------------------+--------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec)
