20240516_001145 建表时创建索引

创建普通索引

格式：

index 索引名称(字段名称)

示例：

mysql> create table tb1(
    -> id int,
    -> name varchar(5),
    -> index myidx(id)
    -> );
Query OK, 0 rows affected (0.47 sec)

mysql> show create table tb1;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                  |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------+
| tb1   | CREATE TABLE `tb1` (
  `id` int(11) default NULL,
  `name` varchar(5) default NULL,
  KEY `myidx` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

唯一索引

格式：

unique index 索引名(字段名)

示例：

mysql> create table tb1(
    -> id int,
    -> name varchar(5),
    -> unique index only(id)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> show create table tb1;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                        |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| tb1   | CREATE TABLE `tb1` (
  `id` int(11) default NULL,
  `name` varchar(5) default NULL,
  UNIQUE KEY `only` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

全文索引

格式：

FULLTEXT INDEX idx_fulltext_description (description)

示例：

FULLTEXT INDEX idx_fulltext_description (description)

多列索引

格式：

INDEX 索引名称(字段1, 字段2) 

示例：

INDEX idx_name (first_name, last_name) 

主键索引

示例：

id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,