mysql 索引

create index index_name on t1(id); Query OK, 0 rows affected (2.80 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table t1; +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | t1 | CREATE TABLE `t1` ( `id` int DEFAULT NULL, `name` varchar(50) DEFAULT NULL, KEY `index_name` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci | +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)

 

（2）修改表结构的方式添加索引

普通索引

ALTER TABLE table_name ADD INDEX index_name (column(length))

唯一索引

ALTER TABLE ec_address add UNIQUE INDEX suoyin  (FIRST_LETTER(3))

主键

alter table ec_address add primary key(ID)

（3）删除索引

DROP INDEX index_name ON table

mysql> drop index index_name on t1;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t1;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                      |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `id` int DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------+

 

mysql 索引

标签：first   weight   letter   res   rop   ima   cte   unique   ica