Server IP : 104.21.38.3 / Your IP : 162.158.88.9 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/t/ |
Upload File : |
# # InnoDB FULLTEXT SEARCH: CJK support - ngram parser # # --source include/have_innodb.inc # Must have debug code to use SET DEBUG --source include/have_debug.inc # Embedded server does not support restarting --source include/not_embedded.inc # Enable diag print to print query parse tree SET GLOBAL innodb_ft_enable_diag_print = 1; -- echo # Test Case 1: Test English with Default(latin1) Charset # Ignore ngram token check for latin1. SET DEBUG="+d,fts_instrument_ignore_ngram_check"; CREATE TABLE articles ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR(200) ) ENGINE=InnoDB; ALTER TABLE articles ADD FULLTEXT INDEX (title) WITH PARSER ngram; INSERT INTO articles (title) VALUES ('what is M'), ('Myname is wonder'), ('My SQL is good'), ('MySQL database'), ('Mind a good thing'), ('Mind good idea'); SELECT * FROM articles WHERE MATCH(title) AGAINST('m*' IN BOOLEAN MODE); SELECT * FROM articles WHERE MATCH(title) AGAINST('my*' IN BOOLEAN MODE); SELECT * FROM articles WHERE MATCH(title) AGAINST('+mind -mysql' IN BOOLEAN MODE); SELECT * FROM articles WHERE MATCH(title) AGAINST('+(mind thing) -idea' IN BOOLEAN MODE); SELECT * FROM articles WHERE MATCH(title) AGAINST('mysql'); SELECT * FROM articles WHERE MATCH(title) AGAINST('mysql' IN BOOLEAN MODE); SELECT * FROM articles WHERE MATCH(title) AGAINST('"mysql"' IN BOOLEAN MODE); SELECT * FROM articles WHERE MATCH(title) AGAINST('"my sql"' IN BOOLEAN MODE); SET DEBUG="-d,fts_instrument_ignore_ngram_check"; DROP TABLE articles; SET NAMES utf8; -- echo # Test Case 2: Test Stopwords # Compare with results without stopword check. SET DEBUG="+d,fts_instrument_ignore_ngram_check"; # Without stopwords CREATE TABLE articles ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR(200), body TEXT ) ENGINE=InnoDB DEFAULT CHARACTER SET gb2312 COLLATE gb2312_chinese_ci; CREATE FULLTEXT INDEX ft_index ON articles(title, body) WITH PARSER ngram; INSERT INTO articles (title, body) VALUES ('从简单购物清单到画展','或企业网络中的海量数据'), ('要想将数据添加到数据库','访问、处理计算机数据库中保存的数据'), ('从简单的购物清单到画展','或企业网络中的海量数据'), ('要想将数据添加到数据库','或访问、处理计算机数据库中保存的数据'), ('数据库是数据的结构化集合','它可以是任何东西'); SELECT * FROM articles WHERE MATCH(title, body) AGAINST('的* 或* 东西' IN BOOLEAN MODE); SELECT * FROM articles WHERE MATCH(title, body) AGAINST('简单的' IN BOOLEAN MODE); SELECT * FROM articles WHERE MATCH(title, body) AGAINST('或访问' IN BOOLEAN MODE); SELECT * FROM articles WHERE MATCH(title, body) AGAINST('"数据的结构化"' IN BOOLEAN MODE); SET DEBUG="-d,fts_instrument_ignore_ngram_check"; DROP TABLE articles; # With Stopwords CREATE TABLE articles ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR(200), body TEXT ) ENGINE=InnoDB DEFAULT CHARACTER SET gb2312 COLLATE gb2312_chinese_ci; # Define a user stopword table and set to it CREATE TABLE user_stopword(value varchar(30)) ENGINE = InnoDB DEFAULT CHARACTER SET gb2312 COLLATE gb2312_chinese_ci; INSERT INTO user_stopword VALUES('的'),('或'),('东西'),('ab'); SET GLOBAL innodb_ft_server_stopword_table = 'test/user_stopword'; INSERT INTO articles (title, body) VALUES ('从简单购物清单到画展','或企业网络中的海量数据'), ('要想将数据添加到数据库','访问、处理计算机数据库中保存的数据'); CREATE FULLTEXT INDEX ft_index ON articles(title, body) WITH PARSER ngram; INSERT INTO articles (title, body) VALUES ('从简单的购物清单到画展','或企业网络中的海量数据'), ('要想将数据添加到数据库','或访问、处理计算机数据库中保存的数据'), ('要想将数据添加到数据库','或访问、处理计算机数据库中保存的数据 abc xyz'), ('数据库是数据的结构化集合','它可以是任何东西'); SELECT * FROM articles WHERE MATCH(title, body) AGAINST('的* 或* 东西' IN BOOLEAN MODE); SELECT * FROM articles WHERE MATCH(title, body) AGAINST('简单的' IN BOOLEAN MODE); SELECT * FROM articles WHERE MATCH(title, body) AGAINST('或访问' IN BOOLEAN MODE); SELECT * FROM articles WHERE MATCH(title, body) AGAINST('"数据的结构化"' IN BOOLEAN MODE); SELECT * FROM user_stopword WHERE value like 'ab' ; SELECT * FROM articles WHERE MATCH(title, body) AGAINST('"ab"' ); SELECT * FROM articles WHERE MATCH(title, body) AGAINST('数据 +ab' IN BOOLEAN MODE); SELECT * FROM articles WHERE MATCH(title, body) AGAINST('数据 (+xyz -bc)' IN BOOLEAN MODE); DROP TABLE user_stopword; DROP TABLE articles; SET GLOBAL innodb_ft_server_stopword_table=default; -- echo # Test Case 3: Check with FTS debug points CREATE TABLE articles ( seq INT AUTO_INCREMENT NOT NULL PRIMARY KEY, c1 VARCHAR(200) CHARACTER SET big5 COLLATE big5_chinese_ci, c2 CHAR(200) CHARACTER SET gbk COLLATE gbk_chinese_ci, c3 CHAR(200) CHARACTER SET utf8, j1 TEXT CHARACTER SET ujis COLLATE ujis_japanese_ci, j2 VARCHAR(200) CHARACTER SET sjis COLLATE sjis_japanese_ci, j3 VARCHAR(200) CHARACTER SET ujis, k1 CHAR(200) CHARACTER SET euckr COLLATE euckr_korean_ci, k2 CHAR(200) CHARACTER SET utf8, e1 CHAR(200) CHARACTER SET latin1, FULLTEXT KEY `con1` (c1), FULLTEXT KEY `con2` (j1,j3), FULLTEXT KEY `con3` (k2) WITH PARSER ngram ) ENGINE=InnoDB ; SET DEBUG='+d,alter_table_rollback_new_index'; -- error ER_UNKNOWN_ERROR ALTER TABLE articles ADD FULLTEXT INDEX (c2(64)); -- error ER_UNKNOWN_ERROR ALTER TABLE articles ADD FULLTEXT INDEX (j1,j3); -- error ER_UNKNOWN_ERROR ALTER TABLE articles ADD FULLTEXT INDEX (k1); -- error ER_UNKNOWN_ERROR ALTER TABLE articles ADD FULLTEXT INDEX (e1); SET DEBUG='-d,alter_table_rollback_new_index'; # Abort the operation in dict_create_index_step by setting # return status of dict_create_index_tree_step() to DB_OUT_OF_MEMORY # The newly create dict_index_t should be removed from fts cache SET DEBUG="+d,ib_dict_create_index_tree_fail"; --error ER_OUT_OF_RESOURCES ALTER TABLE articles ADD FULLTEXT INDEX (c2(64)); --error ER_OUT_OF_RESOURCES ALTER TABLE articles ADD FULLTEXT INDEX (j1,j3); --error ER_OUT_OF_RESOURCES ALTER TABLE articles ADD FULLTEXT INDEX (k1); --error ER_OUT_OF_RESOURCES ALTER TABLE articles ADD FULLTEXT INDEX (e1); SET DEBUG="-d,ib_dict_create_index_tree_fail"; --error ER_INNODB_FT_LIMIT ALTER TABLE articles ADD FULLTEXT INDEX (e1), ADD FULLTEXT INDEX (k1); DROP TABLE articles; SET GLOBAL innodb_ft_enable_diag_print = default;