mysql> create table test1(id int unsigned not null,first_name varchar(30) not null,last_name varchar(30) not null,joined date not null,primary key(id),
index(id));
Query OK, 0 rows affected (0.02 sec)
mysql> show create table test1;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test1 | CREATE TABLE `test1` (
`id` int(10) unsigned NOT NULL,
`first_name` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
`last_name` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
`joined` date NOT NULL,
PRIMARY KEY (`id`),
KEY `id` (`id`) ##注意蓝色和绿色的区别
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> alter table test1 drop index id;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table test1;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test1 | CREATE TABLE `test1` (
`id` int(10) unsigned NOT NULL,
`first_name` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
`last_name` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
`joined` date NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show indexes from test1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test1 | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
mysql> alter table test1 add index id(id);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table test1;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test1 | CREATE TABLE `test1` (
`id` int(10) unsigned NOT NULL,
`first_name` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
`last_name` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
`joined` date NOT NULL,
PRIMARY KEY (`id`),
KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> create table test2(id int unsigned not null,first_name varchar(30) not null,last_name varchar(30) not null,joined date not null,primary key(id),
key(id));
Query OK, 0 rows affected (0.01 sec)
##换成key也一样！
mysql> show create table test2;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test2 | CREATE TABLE `test2` (
`id` int(10) unsigned NOT NULL,
`first_name` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
`last_name` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
`joined` date NOT NULL,
PRIMARY KEY (`id`),
KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
个人理解，index和key没区别，一样的东西
