| Server IP : 104.21.38.3 / Your IP : 172.71.82.109 Web Server : Apache System : Linux krdc-ubuntu-s-2vcpu-4gb-amd-blr1-01.localdomain 5.15.0-142-generic #152-Ubuntu SMP Mon May 19 10:54:31 UTC 2025 x86_64 User : www ( 1000) PHP Version : 7.4.33 Disable Function : passthru,exec,system,putenv,chroot,chgrp,chown,shell_exec,popen,proc_open,pcntl_exec,ini_alter,ini_restore,dl,openlog,syslog,readlink,symlink,popepassthru,pcntl_alarm,pcntl_fork,pcntl_waitpid,pcntl_wait,pcntl_wifexited,pcntl_wifstopped,pcntl_wifsignaled,pcntl_wifcontinued,pcntl_wexitstatus,pcntl_wtermsig,pcntl_wstopsig,pcntl_signal,pcntl_signal_dispatch,pcntl_get_last_error,pcntl_strerror,pcntl_sigprocmask,pcntl_sigwaitinfo,pcntl_sigtimedwait,pcntl_exec,pcntl_getpriority,pcntl_setpriority,imap_open,apache_setenv MySQL : OFF | cURL : ON | WGET : ON | Perl : ON | Python : OFF | Sudo : ON | Pkexec : ON Directory : /www/server/mysql/src/mysql-test/suite/innodb_fts/r/ |
Upload File : |
SET NAMES utf8;
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;
Table Create Table
t1 CREATE TABLE `t1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(200) DEFAULT NULL,
`content` text,
PRIMARY KEY (`id`),
FULLTEXT KEY `ft_content` (`content`),
FULLTEXT KEY `ft_title` (`title`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=gb2312
SELECT * FROM t1 WHERE MATCH(title) AGAINST('中国');
id title content
SELECT * FROM t1 WHERE MATCH(title) AGAINST('中国上海');
id title content
2 中国上海 中国上海
SELECT * FROM t1 WHERE MATCH(content) AGAINST('中国');
id title content
SELECT * FROM t1 WHERE MATCH(content) AGAINST('中国上海');
id title content
2 中国上海 中国上海
DROP TABLE t1;
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;
Table Create Table
t1 CREATE TABLE `t1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(200) DEFAULT NULL,
`content` text,
PRIMARY KEY (`id`),
FULLTEXT KEY `ft_content` (`content`) /*!50100 WITH PARSER `ngram` */ ,
FULLTEXT KEY `ft_title` (`title`) /*!50100 WITH PARSER `ngram` */
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=gb2312
SELECT * FROM t1 WHERE MATCH(title) AGAINST('中国');
id title content
1 中国 中国
2 中国上海 中国上海
SELECT * FROM t1 WHERE MATCH(title) AGAINST('中国上海');
id title content
2 中国上海 中国上海
1 中国 中国
SELECT * FROM t1 WHERE MATCH(content) AGAINST('中国');
id title content
1 中国 中国
2 中国上海 中国上海
SELECT * FROM t1 WHERE MATCH(content) AGAINST('中国上海');
id title content
2 中国上海 中国上海
1 中国 中国
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: --ngram=OFF
CHECK TABLE t1;
Table Op Msg_type Msg_text
test.t1 check Error Plugin 'ngram' is not loaded
test.t1 check Error Incorrect information in file: './test/t1.frm'
test.t1 check error Corrupt
SELECT * FROM t1;
ERROR HY000: Plugin 'ngram' is not loaded
SELECT * FROM t1 WHERE MATCH(title) AGAINST('中国');
ERROR HY000: Plugin 'ngram' is not loaded
SELECT * FROM t1 WHERE MATCH(title) AGAINST('中国上海');
ERROR HY000: Plugin 'ngram' is not loaded
SELECT * FROM t1 WHERE MATCH(content) AGAINST('中国');
ERROR HY000: Plugin 'ngram' is not loaded
SELECT * FROM t1 WHERE MATCH(content) AGAINST('中国上海');
ERROR HY000: Plugin 'ngram' is not loaded
INSERT INTO t1 (title, content) VALUES ('中国', '中国');
ERROR HY000: Plugin 'ngram' is not loaded
DROP INDEX ft_title ON t1;
ERROR HY000: Plugin 'ngram' is not loaded
DROP INDEX ft_content ON t1;
ERROR HY000: Plugin 'ngram' is not loaded
TRUNCATE TABLE t1;
ERROR HY000: Plugin 'ngram' is not loaded
ALTER TABLE t1 RENAME TO t3;
ERROR HY000: Plugin 'ngram' is not loaded
RENAME TABLE t2 to t3;
DROP TABLE t3;
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;
ERROR HY000: Function 'ngram' is not defined
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;
ALTER TABLE t2 ADD FULLTEXT INDEX ft_title (title) WITH PARSER ngram;
ERROR HY000: Function 'ngram' is not defined
ALTER TABLE t2 ADD FULLTEXT INDEX ft_title (title);
Warnings:
Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID
INSERT INTO t2 (title) VALUES ('中国');
SELECT * FROM t2 WHERE MATCH(title) AGAINST('中国');
id title
SELECT * FROM t2 WHERE MATCH(title) AGAINST('中国上海');
id title
DROP TABLE t2;
# restart
CHECK TABLE t1;
Table Op Msg_type Msg_text
test.t1 check status OK
SELECT * FROM t1;
id title content
1 中国 中国
2 中国上海 中国上海
SELECT * FROM t1 WHERE MATCH(title) AGAINST('中国');
id title content
1 中国 中国
2 中国上海 中国上海
SELECT * FROM t1 WHERE MATCH(title) AGAINST('中国上海');
id title content
2 中国上海 中国上海
1 中国 中国
SELECT * FROM t1 WHERE MATCH(content) AGAINST('中国');
id title content
1 中国 中国
2 中国上海 中国上海
SELECT * FROM t1 WHERE MATCH(content) AGAINST('中国上海');
id title content
2 中国上海 中国上海
1 中国 中国
DROP TABLE t1;