MySQL - ngram 全文解析器

要透過 JavaScript 程式執行 ngram 全文解析器，我們需要使用 **mysql2** 庫的 **query()** 函式執行 "Create" 語句，如下所示：

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);

要透過 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",
}); //Connecting to MySQL

con.connect(function (err) {
  if (err) throw err;
  //   console.log("Connected successfully...!");
  //   console.log("--------------------------");
  sql = "USE TUTORIALS";
  con.query(sql);

  //create a table...
  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);

  //insert data
  sql = `INSERT INTO blog (id, title, description) VALUES 
  (NULL, '教程', '教程是對一個概念的冗長研究'), 
  (NULL, '文章', '文章是關於一個概念的基於事實的小資訊')`;
  con.query(sql);

  //we will use the below statement to see how the ngram tokenizes the data:
  sql = "SET GLOBAL innodb_ft_aux_table = 'TUTORIALS/blog'";
  con.query(sql);

  //display the table details;
  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://: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...!");

         //we will use the below statement to see how the ngram tokenizes the data:
         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
# Establishing the connection
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
# Creating a cursor object
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!")
# Set the character set to UTF8MB4
set_charset_query = "SET NAMES UTF8MB4;"
cursorObj.execute(set_charset_query)
print("Character set is set to UTF8MB4.")
# Insert data into the blog table
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.")
# Query the INNODB_FT_INDEX_CACHE table to get the full-text index information
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)
# Close the cursor and connection
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: