MySQL:索引

SQL语法

创建索引

drop table if exists lck_tst;
create table lck_tst(
id int not null,
name varchar(200) not null,
number varchar(20) not null, 
type1 char(1),
type2 char(2),
content varchar(200),
remark varchar(200)
) ENGINE=MyISAM;
alter table lck_tst add primary key (id);
alter table lck_tst add unique idx_number(number);
alter table lck_tst add index idx_name(name);
alter table lck_tst add index idx_type(type1, type2);
alter table lck_tst add fulltext(content);

insert into lck_tst(id, name, number, type1, type2, content, remark)
values
(1, 'lee', '0001', 'M', 'S', 'Stray birds of summer come to my window to sing and fly away.And yellow leaves
 of autumn, which have no songs, flutter and fall there with a sign.', '140Kg, 25Y/O, like music'),
(2, 'zhang', '0002', 'F', 'Q', 'O Troupe of little vagrants of the world, leave your footprints in my 
words.', '150Kg, 26Y/O, like sport'),
(3, '刘德华', '0003', 'M', 'S', 'The world puts off its mask of vastness to its lover.', '180Kg, 25Y/O, like
 music'),
(4, '侯宝林', '0004', 'M', 'Q', 'It is the tears of the earth that keep here smiles in bloom.', '14Kg, 
26Y/O, like sport'),
(5, '梅葆玖', '0005', 'M', 'W', 'The mighty desert is burning for the love of a bladeof grass who shakes her
 head and laughs and flies away.', 'wahaha a wahaha'),
(6, 'Riguz', 'Ab01', 'F', 'R', 'If you shed tears when you miss the sun, you also miss the stars.',
 'kasjdslkhah asjdhjksa xhjawhejqw xjhjask'),
(7, 'Linus', 'BB-2', 'F', 'R', 'The sands in your way beg for your song and your movement, dancing water. 
Will you carry the burden of their lameness?', '180Kg'),
(8, '列夫托尔斯泰', '30x9', 'E', 'S', '跳舞着的流水呀，在你途中的泥沙，要求你的歌声，你的流动呢。你肯挟瘸足
的泥沙而俱下么？', 'shdiuowa weqwehoxz xckahwejk asjdhkjas'),
(9, '马克思', '9845', 'M', 'E', '她的热切的脸，如夜雨似的，搅扰着我的梦魂', '018989yhahhhjashd'),
(10, '买买提 铁木耳', '9863', 'S', 'E', '忧思在我的心里平静下去，正如暮色降临在寂静的山林中。　　', '123sser');

全文索引

SELECT * FROM lck_tst WHERE MATCH(content) AGAINST('tears earth birds');