Server IP : 172.67.216.182 / Your IP : 172.70.208.31 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 : |
--source include/have_innodb.inc # This is the DDL function tests for innodb FTS # Functional testing with FTS proximity search using '@' # and try search default words --disable_warnings drop table if exists t1; --enable_warnings --disable_query_log let $innodb_file_per_table_orig = `select @@innodb_file_per_table`; --enable_query_log # Create FTS table CREATE TABLE t1 ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, a VARCHAR(200), b TEXT ) ENGINE= InnoDB; # Create the FTS index again CREATE FULLTEXT INDEX idx on t1 (a,b); # Insert rows INSERT INTO t1 (a,b) VALUES ('MySQL from Tutorial','DBMS stands for DataBase ...') , ('when To Use MySQL Well','After that you went through a ...'), ('where will Optimizing MySQL','what In this tutorial we will show ...'); # Try to Search default stopword from innodb, "where", "will", "what" # and "when" are all stopwords SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("where will"); SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("when"); SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("what" WITH QUERY EXPANSION); # boolean No result expected SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("whe*" IN BOOLEAN MODE); SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("+what +will" IN BOOLEAN MODE); SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("+from" IN BOOLEAN MODE); SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("+where +(show what)" IN BOOLEAN MODE); # no result expected. Words are filtered out as stopwords SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('"where will"@6' IN BOOLEAN MODE); # no result expected SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('"where will"@9' IN BOOLEAN MODE); # insert record with @ character which is used in proximity search INSERT INTO t1 (a,b) VALUES ('MySQL Tutorial','request [email protected] ...') , ('Trial version','query performace @1255 minute on 2.1Hz Memory 2GB...') , ('when To Use MySQL Well','for free faq [email protected] ...'); # proximity search with @ charcter # We don't need more than one word in proximity search. Single word # treated as single word search SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('"request"@10' IN BOOLEAN MODE); # If the distance is 0, it is treated as "phrase search" SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('"Trial version"@0' IN BOOLEAN MODE); # @ is word seperator SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('"request [email protected]"@10' IN BOOLEAN MODE); # This should not return any document SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('"1255 minute"@1' IN BOOLEAN MODE); # This should return the first document. That is "1255" and "minutes" are # in a two-word range (adjacent) SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('"1255 minute"@2' IN BOOLEAN MODE); SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('"1255"@10' IN BOOLEAN MODE); SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('1255' WITH QUERY EXPANSION); SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('"request docteam"@2' IN BOOLEAN MODE); SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('"1255 minute"' IN BOOLEAN MODE); SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('request [email protected]'); # Test across fields search SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('"MySQL request"@3' IN BOOLEAN MODE); # Two words are in 10 words range SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('"Trial memory"@10' IN BOOLEAN MODE); SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('"Trial memory"@9' IN BOOLEAN MODE); DROP TABLE t1; # test on utf8 encoded proximity search CREATE TABLE t1 ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, a VARCHAR(200), b TEXT ) CHARACTER SET = UTF8, ENGINE= InnoDB; INSERT INTO t1 (a,b) VALUES ('MySQL from Tutorial','DBMS stands for DataBase ...') , ('when To Use MySQL Well','After that you went through a ...'), ('where will Optimizing MySQL','what In this tutorial we will show ...'); CREATE FULLTEXT INDEX idx on t1 (a,b); INSERT INTO t1 (a,b) VALUES ('MySQL Tutorial','request [email protected] ...') , ('Trial version','query performace @1255 minute on 2.1Hz Memory 2GB...'), ('when To Use MySQL Well','for free faq [email protected] ...'); # Should have 2 rows. Note proximity search does require words in order SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('"mysql use"@2' IN BOOLEAN MODE); # Should return 0 row SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('"mysql use"@1' IN BOOLEAN MODE); INSERT INTO t1 (a,b) VALUES ('XYZ, long blob', repeat("a", 9000)); INSERT IGNORE INTO t1 (a,b) VALUES (repeat("b", 9000), 'XYZ, long blob'); # 2 rows match SELECT count(*) FROM t1 WHERE MATCH (a,b) AGAINST ('"xyz blob"@3' IN BOOLEAN MODE); DROP TABLE t1; set global innodb_file_per_table=1; set names utf8; --character_set utf8 # Test fts with externally stored long column CREATE TABLE t1 ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, a TEXT, b TEXT, c TEXT ) CHARACTER SET = UTF8, ROW_FORMAT=DYNAMIC, ENGINE= InnoDB; INSERT INTO t1 (a,b,c) VALUES (repeat("b", 19000), 'XYZ, long text', 'very long blob'); INSERT INTO t1 (a,b,c) VALUES (repeat("b", 19000), 'XYZ, very little long blob very much blob', 'very long blob'); # Note 租车 is count as one word INSERT INTO t1 (a,b,c) VALUES (repeat("b", 19000),"very 租车 供 blob","new 供需分析information"); CREATE FULLTEXT INDEX idx on t1 (a,b,c); INSERT INTO t1 (a,b,c) VALUES (repeat("x", 19000), 'new, long text', 'very new blob'); INSERT INTO t1 (a,b,c) VALUES ('interesting, long text', repeat("x", 19000), 'very very good new blob'); # 3 rows should match SELECT count(*) FROM t1 WHERE MATCH (a,b,c) AGAINST ('"very blob"@3' IN BOOLEAN MODE); SELECT count(*) FROM t1 WHERE MATCH (a,b,c) AGAINST ('"very long blob"@0' IN BOOLEAN MODE); # 4 rows should match SELECT count(*) FROM t1 WHERE MATCH (a,b,c) AGAINST ('"very blob"@4' IN BOOLEAN MODE); # 1 row should match SELECT count(*) FROM t1 WHERE MATCH (a,b,c) AGAINST ('"interesting blob"@9' IN BOOLEAN MODE); # should have 3 rows SELECT COUNT(*) FROM t1 WHERE MATCH (a,b,c) AGAINST ('"interesting blob"@9 "very long blob"@0' IN BOOLEAN MODE); # should have 3 rows SELECT COUNT(*) FROM t1 WHERE MATCH (a,b,c) AGAINST ('"very blob"@4 - "interesting blob"@9' IN BOOLEAN MODE); DROP TABLE t1; CREATE TABLE t1 ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, a VARCHAR(200), b TEXT ) CHARACTER SET = UTF8, ENGINE= InnoDB; # Space and special characters are not counted as word INSERT INTO t1 (a,b) VALUES ('MySQL from Tutorial','DBMS stands for + DataBase ...'); CREATE FULLTEXT INDEX idx on t1 (a,b); SELECT * FROM t1 WHERE MATCH (a,b) AGAINST ('"stands database"@3' IN BOOLEAN MODE); DROP TABLE t1; # Test fts with externally stored long column CREATE TABLE t1 ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, a TEXT, b TEXT, c TEXT ) CHARACTER SET = UTF8, ROW_FORMAT=DYNAMIC, ENGINE= InnoDB; INSERT INTO t1 (a,b,c) VALUES (repeat("b", 19000), 'XYZ, long text', 'very long blob'); INSERT INTO t1 (a,b,c) VALUES ('XYZ, 租车 very little long blob very much blob', repeat("b", 19000), 'very long but smaller blob'); CREATE FULLTEXT INDEX idx on t1 (a,b,c); DELETE FROM t1; INSERT INTO t1 (a,b,c) VALUES (repeat("b", 19000), 'XYZ, long text', 'very long blob'); INSERT INTO t1 (a,b,c) VALUES ('XYZ, 租车 very little long blob is a very much longer blob', repeat("b", 19000), 'this is very long but smaller blob'); SELECT count(*) FROM t1 WHERE MATCH (a,b,c) AGAINST ('"very blob"@4' IN BOOLEAN MODE); SELECT count(*) FROM t1 WHERE MATCH (a,b,c) AGAINST ('"very blob"@3' IN BOOLEAN MODE); DROP TABLE t1; eval SET GLOBAL innodb_file_per_table=$innodb_file_per_table_orig;