MySQL创建索引方法:ALTER TABLE和CREATE INDEX的区别

众所周知,MySQL创建索引有两种语法,即:
ALTER TABLE HeadOfState ADD INDEX (LastName, FirstName);
CREATE INDEX index_name HeadOfState (LastName, FirstName);
那么,这两种语法有什么区别呢? 😉
在网上找了一下,在一个英文网站上,总结了下面几个区别,我翻译出来,如下:
1、CREATE INDEX必须提供索引名,对于ALTER TABLE,将会自动创建,如果你不提供;
2、CREATE INDEX一个语句一次只能建立一个索引,ALTER TABLE可以在一个语句建立多个,如:
      ALTER TABLE HeadOfState ADD PRIMARY KEY (ID), ADD INDEX (LastName,FirstName);
3、只有ALTER TABLE 才能创建主键,ADD INDEX 不能;

英文原句如下:
With CREATE INDEX, we must provide a name for the index. With ALTER TABLE, MySQL creates an index name automatically if you don’t provide one.Unlike ALTER TABLE, the CREATE INDEX statement can create only a single index per statement. In addition, only ALTER TABLE supports the use of PRIMARY KEY.

更详细的说明请参照原网站:
http://khaidoan.wikidot.com/mysql-indexes

另附MySQL的TEXT类型说明(对以下类型的字段建立索引,必须指明索引长度,否则会报错) 😕
TINYTEXT: 256 bytes
TEXT: 65,535 bytes => ~64kb
MEDIUMTEXT: 16,777,215 bytes => ~16MB
LONGTEXT: 4,294,967,295 bytes => ~4GB