Question

mysql index类型: PRIMARY,INDEX,UNIQUE,FULLTEXT,SPATIAL 各有什么不同?

Answer 1

primary 是主键 这个就是表的主键了，唯一的不能重复出现
index 是索引 索引就如同书的目录
unique 是唯一约束 标识数据库表中的每条记录
fulltext 全文索引 一个 FULLTEXT 类型索引
spatial 空间索引 这个跟GIS有关

Answer 2

_name
alter_specification [, alter_specification] ...

alter_specification:
table_option ...
| ADD [COLUMN] column_definition [FIRST | AFTER col_name ]
| ADD [COLUMN] (column_definition,...)
| ADD [index_name] [index_type] (index_col_name,...)
| ADD [CONSTRAINT [symbol]]
PRIMARY KEY [index_type] (index_col_name,...)
| ADD [CONSTRAINT [symbol]]
UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,...)
| ADD [FULLTEXT|SPATIAL] [INDEX|KEY] [index_name] (index_col_name,...)
| ADD [CONSTRAINT [symbol]]
FOREIGN KEY [index_name] (index_col_name,...)
[reference_definition]
| ALTER [COLUMN] col_name
| CHANGE [COLUMN] old_col_name column_definition
[FIRST|AFTER col_name]
| MODIFY [COLUMN] column_definition [FIRST | AFTER col_name]
| DROP [COLUMN] col_name
| DROP PRIMARY KEY
| DROP index_name
| DROP FOREIGN KEY fk_symbol
| DISABLE KEYS
| ENABLE KEYS
| RENAME [TO] new_tbl_name
| ORDER BY col_name [, col_name] ...
| CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
| [DEFAULT] CHARACTER SET charset_name [COLLATE collation_name]
| DISCARD TABLESPACE
| IMPORT TABLESPACE

index_col_name:
col_name [(length)] [ASC | DESC]

index_type:
USING
问题1.如果我忘记把id设为主键现在该怎么办？输入什么命令？
ALTER TABLE record
ADD PRIMARY KEY (id);

例如：ALTER TABLE `abc` ADD PRIMARY KEY(`a`)；

问题2.如果我想把id设为主键，同时想把name改为not null，且改为varchar(10),能不能用一句命令完成问题2？
ALTER TABLE record
ADD PRIMARY KEY (id),
CHANGE name varchar(10) not null;

读懂上面的diagram,你就会了。哈哈。