sql È«ÎÄË÷Òý

1¡¢ÒªÆôÓÃÈ«ÎÄË÷Òý¹¦ÄÜÊ×ÏÈÐèÒª°²×°full text searchÈ«ÎÄË÷Òý·þÎñ
2¡¢Æô¶¯full text search·þÎñ
3¡¢ÏÈ´´½¨UniqueË÷ÒýºÍÈ«ÎÄË÷Òý£ºCREATE FULLTEXT INDEX ON table_name
4¡¢Ã¿¸ö±íÖ»ÔÊÐí´´½¨Ò»¸öÈ«ÎÄË÷Òý
ɾ³ýÈ«ÎÄË÷Òý DROP FULLTEXT INDEX ON table_name
È«ÎÄËÑË÷Óï¾ä£¬contains(),freeText()
×¢ÒâÊý¾Ý¿âµÄÅÅÐò¹æÔò£¬Chinese_PRC_CI_ASʱÖÐÎIJſɲéѯµ½ÕýÈ·½á¹û¡£
5¡¢Ê¾ÀýÓï¾ä£º
 
--¿ªÆôÊý¾Ý¿âÈ«ÎÄË÷Òý
SP_FULLTEXT_DATABASE 'enable'
--´´½¨±í
CREATE TABLE [dbo].[UserTB](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [fullname] [nvarchar](400) COLLATE Chinese_PRC_CI_AS NULL,
    [address] [nvarchar](500) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
 
--дÈë²âÊÔÊý¾Ý
insert into usertb(fullname,address) values('ÀÏÖÜ','»ØÁú¹ÛµØÇø±±¾©²ýƽ');
insert into usertb(fullname,address) values('ÀÏÍõ','beijing city changping district hui long guan region');
insert into usertb(fullname,address) values('ÀÏÀî','beijingcitychangpingdistricthuilongguan region');
insert into usertb(fullname,address) values('ÀÏÆÅ','aaaaaaaaaa dddddddd');
--´´½¨Ë÷Òý
CREATE UNIQUE INDEX    ix_usertb_id ON usertb(id)
--´´½¨È«ÎÄË÷ÒýĿ¼
CREATE FULLTEXT CATALOG fa AS DEFAULT
--DROP FULLTEXT CATALOG FA
--CREATE FULLTEXT INDEX ON dbo.usertb(fullname) KEY INDEX ix_usertb_id 
--DROP FULLTEXT INDEX on usertb
--´´½¨È«ÎÄË÷Òý
CREATE FULLTEXT INDEX ON dbo.usertb(address) KEY INDEX ix_usertb_id 
--²âÊÔ²éѯ
SELECT * from userTB
--WHERE CONTAINS(address,'changpin')
--WHERE CONTAINS(address,'changping')
WHERE CONTAINS(address,'"aaa*"')
--WHERE CONTAINS(address,'"²ýƽ*"')
--WHERE CONTAINS(address,'²ýƽ')