1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
|
#
# InnoDB FULLTEXT SEARCH: CJK support - ngram parser
#
#
#Test 3 scenarios:
#1. CREATE a CJK fulltext index with ngram parser when ngram parser is loaded,
# restart without ngram parser, do SELECT/INSERT;
#2. CREATE a CJK fulltext index with ngram parser when ngram parser is not loaded.
#3. CREATE a CJK fulltext index without ngram parser when ngram parser is not loaded.
SET NAMES utf8mb4;
# t1 with default parser
CREATE TABLE t1 (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
content TEXT,
FULLTEXT INDEX ft_content (content)
) ENGINE=InnoDB DEFAULT CHARACTER SET gb2312 COLLATE gb2312_chinese_ci;
INSERT INTO t1 (title, content) VALUES ('中国', '中国'), ('中国上海', '中国上海');
ALTER TABLE t1 ADD FULLTEXT INDEX ft_title (title);
SHOW CREATE TABLE t1;
SELECT * FROM t1 WHERE MATCH(title) AGAINST('中国') ORDER BY id;
SELECT * FROM t1 WHERE MATCH(title) AGAINST('中国上海') ORDER BY id;
SELECT * FROM t1 WHERE MATCH(content) AGAINST('中国') ORDER BY id;
SELECT * FROM t1 WHERE MATCH(content) AGAINST('中国上海') ORDER BY id;
DROP TABLE t1;
# t1 with ngram parser
CREATE TABLE t1 (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
content TEXT,
FULLTEXT INDEX ft_content (content) WITH PARSER ngram
) ENGINE=InnoDB DEFAULT CHARACTER SET gb2312 COLLATE gb2312_chinese_ci;
INSERT INTO t1 (title, content) VALUES ('中国', '中国'), ('中国上海', '中国上海');
ALTER TABLE t1 ADD FULLTEXT INDEX ft_title (title) WITH PARSER ngram;
SHOW CREATE TABLE t1;
SELECT * FROM t1 WHERE MATCH(title) AGAINST('中国') ORDER BY id;
SELECT * FROM t1 WHERE MATCH(title) AGAINST('中国上海') ORDER BY id;
SELECT * FROM t1 WHERE MATCH(content) AGAINST('中国') ORDER BY id;
SELECT * FROM t1 WHERE MATCH(content) AGAINST('中国上海') ORDER BY id;
# t2 with ngram parser
CREATE TABLE t2 (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
content TEXT,
FULLTEXT INDEX ft_content (content) WITH PARSER ngram
) ENGINE=InnoDB DEFAULT CHARACTER SET gb2312 COLLATE gb2312_chinese_ci;
ALTER TABLE t2 ADD FULLTEXT INDEX ft_title (title) WITH PARSER ngram;
# Restart without ngram.
let $restart_parameters = restart: --ngram=OFF;
--source include/restart_mysqld.inc
# t1
CHECK TABLE t1;
--error ER_PLUGIN_IS_NOT_LOADED
SELECT * FROM t1;
--error ER_PLUGIN_IS_NOT_LOADED
SELECT * FROM t1 WHERE MATCH(title) AGAINST('中国');
--error ER_PLUGIN_IS_NOT_LOADED
SELECT * FROM t1 WHERE MATCH(title) AGAINST('中国上海');
--error ER_PLUGIN_IS_NOT_LOADED
SELECT * FROM t1 WHERE MATCH(content) AGAINST('中国');
--error ER_PLUGIN_IS_NOT_LOADED
SELECT * FROM t1 WHERE MATCH(content) AGAINST('中国上海');
--error ER_PLUGIN_IS_NOT_LOADED
INSERT INTO t1 (title, content) VALUES ('中国', '中国');
--error ER_PLUGIN_IS_NOT_LOADED
DROP INDEX ft_title ON t1;
--error ER_PLUGIN_IS_NOT_LOADED
DROP INDEX ft_content ON t1;
--error ER_PLUGIN_IS_NOT_LOADED
TRUNCATE TABLE t1;
--error ER_PLUGIN_IS_NOT_LOADED
ALTER TABLE t1 RENAME TO t3;
# t2
RENAME TABLE t2 to t3;
DROP TABLE t3;
--error ER_FUNCTION_NOT_DEFINED
CREATE TABLE t2 (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
FULLTEXT INDEX ft_title (title) WITH PARSER ngram
) ENGINE=InnoDB DEFAULT CHARACTER SET gb2312 COLLATE gb2312_chinese_ci;
CREATE TABLE t2 (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200)
) ENGINE=InnoDB DEFAULT CHARACTER SET gb2312 COLLATE gb2312_chinese_ci;
--error ER_FUNCTION_NOT_DEFINED
ALTER TABLE t2 ADD FULLTEXT INDEX ft_title (title) WITH PARSER ngram;
ALTER TABLE t2 ADD FULLTEXT INDEX ft_title (title);
INSERT INTO t2 (title) VALUES ('中国');
SELECT * FROM t2 WHERE MATCH(title) AGAINST('中国') ORDER BY id;
SELECT * FROM t2 WHERE MATCH(title) AGAINST('中国上海') ORDER BY id;
DROP TABLE t2;
# Restart with ngram.
let $restart_parameters = restart;
--source include/restart_mysqld.inc
CHECK TABLE t1;
SELECT * FROM t1;
SELECT * FROM t1 WHERE MATCH(title) AGAINST('中国') ORDER BY id;
SELECT * FROM t1 WHERE MATCH(title) AGAINST('中国上海') ORDER BY id;
SELECT * FROM t1 WHERE MATCH(content) AGAINST('中国') ORDER BY id;
SELECT * FROM t1 WHERE MATCH(content) AGAINST('中国上海') ORDER BY id;
DROP TABLE t1;
|
