MySQL - ngram 全文解析器

要通过 JavaScript 程序执行 ngram 全文解析器，我们需要使用 mysql2 库的 query() 函数执行"Create"语句，如下所示 -

sql = `CREATE TABLE blog (ID INT AUTO_INCREMENT NOT NULL, title VARCHAR(255), DECLARE TEXT, FULLTEXT ( title, DECLARE ) WITH PARSER NGRAM, PRIMARY KEY(id) )ENGINE=INNODB CHARACTER SET UTF8MB4`;
con.query(sql);

要通过 Java 程序执行 ngram 全文解析器，我们需要使用 JDBC 函数 execute() 执行"Create"语句，如下所示 -

String sql = "CREATE TABLE blog (ID INT AUTO_INCREMENT NOT NULL, title VARCHAR(255), description TEXT," +
" FULLTEXT ( title, description ) WITH PARSER NGRAM, PRIMARY KEY(id) )ENGINE=INNODB CHARACTER SET UTF8MB4";
statement.execute(sql);

要通过 Python 程序执行 ngram 全文解析器，我们需要使用 MySQL Connector/Python 的 execute() 函数执行"Create"语句，如下所示 -

create_table_query = 'CREATE TABLE blog(ID INT AUTO_INCREMENT NOT NULL, title VARCHAR(255), description TEXT, FULLTEXT (title, description) WITH PARSER NGRAM, PRIMARY KEY(id)) ENGINE=INNODB CHARACTER SET UTF8MB4'
cursorObj.execute(queryexpansionfulltext_search)

var mysql = require("mysql2");
var con = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "password",
}); //连接到 MySQL

con.connect(function (err) {
  if (err) throw err;
  //   console.log("Connected successfully...!");
  //   console.log("--------------------------");
  sql = "USE TUTORIALS";
  con.query(sql);
 //创建一个表...
  sql = `CREATE TABLE blog (ID INT AUTO_INCREMENT NOT NULL, title VARCHAR(255), description TEXT, FULLTEXT ( title, description ) WITH PARSER NGRAM, PRIMARY KEY(id) )ENGINE=INNODB CHARACTER SET UTF8MB4`;
  con.query(sql);
 //插入数据
  sql = `INSERT INTO blog (id, title, description) VALUES 
  (NULL, '', ''), 
  (NULL, '', '')`;
  con.query(sql);
 //我们将使用下面的语句来查看 ngram 如何标记数据:
  sql = "SET GLOBAL innodb_ft_aux_table = 'TUTORIALS/blog'";
  con.query(sql);
 //显示表详细信息;
  sql = `SELECT * FROM blog WHERE MATCH (title, description) AGAINST ('')`;
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
  });
});                   

输出

获得的输出如下所示 -

[ { id: 1, title: '', description: '' } ]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class NgRamFSearch {
   public static void main(String[] args) {
      String url = "jdbc:mysql://localhost:3306/TUTORIALS";
      String username = "root";
      String password = "password";
      try {
         Class.forName("com.mysql.cj.jdbc.Driver");
         Connection connection = DriverManager.getConnection(url, username, password);
         Statement statement = connection.createStatement();
         System.out.println("Connected successfully...!");
        //creating a table that takes fulltext column with parser ngram...!
         String sql = "CREATE TABLE blog (id INT AUTO_INCREMENT NOT NULL, title VARCHAR(255), description TEXT," + 
         " FULLTEXT ( title, description ) WITH PARSER NGRAM, PRIMARY KEY(id) )ENGINE=INNODB CHARACTER SET UTF8MB4";
         statement.execute(sql);
         //System.out.println("Table created successfully...!");
        //inserting data to the table
         String insert = "INSERT INTO blog (id, title, description) VALUES (NULL, '', '')," +
                 " (NULL, '', '')";
         statement.execute(insert);
         //System.out.println("Data inserted successfully...!");
        //我们将使用下面的语句来查看 ngram 如何标记数据:
         String set_global = "SET GLOBAL innodb_ft_aux_table = 'TUTORIALS/blog'";
         statement.execute(set_global);
        ResultSet resultSet = statement.executeQuery("SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE ORDER BY doc_id, position ");
         System.out.println("Information schema order by Id...!");
         while (resultSet.next()){
            System.out.println(resultSet.getString(1)+" "+resultSet.getString(2));
         }
        //displaying the data...!
         String query = "SELECT * FROM blog WHERE MATCH (title, description) AGAINST ('')";
         ResultSet resultSet1 =  statement.executeQuery(query);
         System.out.println("table records:");
         while (resultSet1.next()){
            System.out.println(resultSet1.getString(1)+" "+resultSet1.getString(2)+ " "+resultSet1.getString(3));
         }
        connection.close();
      } catch (Exception e) {
         System.out.println(e);
      }
   }
}    

输出

获得的输出如下所示 -

Connected successfully...!
Information schema order by Id...!
 2
 2
 2
 2
 2
 2
 2
 2
 2
 2
 2
 2
 2
 3
 3
 3
 3
 3
 3
 2
 2
 2
 2
 3
 3
 3
 3
 3
 3
 3
 3
table records:
1  

import mysql.connector
# 建立连接
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
# 创建游标对象
cursorObj = connection.cursor()
# Create the blog table with NGRAM full-text parser
create_table_query = '''
CREATE TABLE blog (
    id INT AUTO_INCREMENT NOT NULL,
    title VARCHAR(255),
    description TEXT,
    FULLTEXT (title, description) WITH PARSER NGRAM,
    PRIMARY KEY(id)
) ENGINE=INNODB CHARACTER SET UTF8MB4;
'''
cursorObj.execute(create_table_query)
print("Table 'blog' is created successfully!")
# 将字符集设置为 UTF8MB4
set_charset_query = "SET NAMES UTF8MB4;"
cursorObj.execute(set_charset_query)
print("Character set is set to UTF8MB4.")
# 将数据插入 blog 表
data_to_insert = [
    ('', ''),
    ('', '')
]
insert_query = "INSERT INTO blog (title, description) VALUES (%s, %s)"
cursorObj.executemany(insert_query, data_to_insert)
connection.commit()
print("Data inserted into the 'blog' table.")
# 查询INNODB_FT_INDEX_CACHE表，获取全文索引信息
query_index_cache_query = "SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE ORDER BY doc_id, position;"
cursorObj.execute(query_index_cache_query)
results = cursorObj.fetchall()
print("Results of INNODB_FT_INDEX_CACHE table:")
for row in results:
    print(row)
#关闭游标和连接
cursorObj.close()
connection.close() 

输出

获得的输出如下所示 -

Table 'blog' is created successfully!
Character set is set to UTF8MB4.
Data inserted into the 'blog' table.
Results of INNODB_FT_INDEX_CACHE table: