MATCH

a, an, and, are, as, at, be, but, by, for, if, in, into, is, it, no, not, 
of, on, or, such, that, the, their, then, there, these, they, this, to, was, will, with

+ - && || ! ( ) { } [ ] ^ " ~ * ? : \

CREATE TABLE articles (
    id INT UNSIGNED,
    year int UNSIGNED,
    title VARCHAR(200),
    body TEXT,
    SORT KEY (id),
    FULLTEXT USING VERSION 2 art_ft_index (title, body));    

INSERT INTO articles (id, year, title, body) VALUES
    (1, 2021, 'Introduction to SQL', 'SQL is a standard language for accessing and manipulating databases.'),
    (2, 2022, 'Advanced SQL Techniques', 'Explore advanced techniques and functions in SQL for better data manipulation.'),
    (3, 2020, 'Database Optimization', 'Learn about various optimization techniques to improve database performance.'),
    (4, 2023, 'SQL in Web Development', 'Discover how SQL is used in web development to interact with databases.'),
    (5, 2019, 'Data Security in SQL', 'An overview of best practices for securing data in SQL databases.'),
    (6, 2021, 'SQL and Data Analysis', 'Using SQL for effective data analysis and reporting.'),
    (7, 2022, 'Introduction to Database Design', 'Fundamentals of designing a robust and scalable database.'),
    (8, 2020, 'SQL Performance Tuning', 'Tips and techniques for tuning SQL queries for better performance.'),
    (9, 2023, 'Using SQL with Python', 'Integrating SQL with Python for data science and automation tasks.'),
    (10, 2019, 'NoSQL vs SQL', 'A comparison of NoSQL and SQL databases and their use cases.');

OPTIMIZE TABLE articles FLUSH;

SELECT * FROM articles WHERE MATCH (TABLE articles) AGAINST ('body:database');

+----+------+---------------------------------+-----------------------------------------------------------------------------+
| id | year | title                           | body                                                                        |
+----+------+---------------------------------+-----------------------------------------------------------------------------+
| 7  | 2022 | Introduction to Database Design |	Fundamentals of designing a robust and scalable database.                   |
| 3  | 2020 | Database Optimization	      | Learn about various optimization techniques to improve database performance.|
+----+------+---------------------------------+-----------------------------------------------------------------------------+

SELECT title
    FROM articles  
    WHERE MATCH (TABLE articles) AGAINST ('title:SQL OR body:("Business Intelligence")')
    AND MATCH (TABLE articles) AGAINST ('body:web+'); 

+------------------------+
| title                  |     
+------------------------+
| SQL in Web Development |
+------------------------+

SELECT title
    FROM articles  
    WHERE MATCH (TABLE articles) AGAINST ('title:+Data* OR title:function?');

+---------------------------------+
| title                           |     
+---------------------------------+
| Introduction to Database Design |
| SQL and Data Analysis           |
| Database Optimization           |
| Data Security in SQL            |
+---------------------------------+

CREATE TABLE ft_records (
    id INT UNSIGNED,
    title VARCHAR(200),
    records JSON,
    SORT KEY(id),
    FULLTEXT USING VERSION 2 rec_ft_index (title, records));

INSERT INTO ft_records VALUES (
1, 
'document', 
'{"k1": "cucumber",
  "k2": ["dragonfruit", "eggplant"],
  "k3": [
         {"k3_1": "fig", "k3_2": "grape"},
         {"k3_1": ["huckleberry", "iceberg lettuce"]},
         "jicama"
        ]
 }');

OPTIMIZE TABLE ft_records FLUSH;

SELECT (MATCH (TABLE ft_records) AGAINST ('records:/.*cumber/')) FROM ft_records;

SELECT (MATCH (TABLE ft_records) AGAINST ('records$k3.k3_1:fig')) FROM ft_records;

SELECT id FROM ft_records WHERE MATCH (TABLE ft_records) AGAINST ('records:(iceberg lettuce)');

+------+ 
| id   |
+------+ 
| 1    | 
+------+ 

SELECT id FROM ft_records WHERE MATCH (TABLE ft_records) AGAINST ('records:cucumber dragonfruit');

Empty set (0.008 sec)

SELECT id FROM ft_records WHERE MATCH (TABLE ft_records) AGAINST ('records:(cucumber dragonfruit~100)');

+------+
| id   | 
+------+ 
| 1    | 
+------+ 

CREATE TABLE books (
    id INT UNSIGNED,
    name VARCHAR(100),
    publish_year INT UNSIGNED,
    body TEXT,
    SORT KEY (id),  
    FULLTEXT USING VERSION 1 (body));

SELECT count(*)
    FROM books
    WHERE publish_year = 2017 AND MATCH (body) AGAINST ('memsql');

CREATE TABLE articles (
    id INT UNSIGNED,
    year int UNSIGNED,
    title VARCHAR(200),
    body TEXT,
    SORT KEY (id),
    FULLTEXT USING VERSION 1 (title, body));

SELECT * FROM articles
    WHERE MATCH (title,body)
    AGAINST ('database');

SELECT title
    FROM articles  
    WHERE MATCH (title) AGAINST ('Database OR "Business Intelligence"');

SELECT title
    FROM articles  
    WHERE MATCH (title) AGAINST ('Database OR "Business Intelligence"')
    AND MATCH(body) AGAINST ("real-time analytics");

SELECT title
    FROM articles  
    WHERE MATCH(article_id) AGAINST ('"id\\-" +article*');

SELECT *
    FROM articles  
    WHERE MATCH(title) AGAINST ('Journal*');

SELECT * FROM articles WHERE MATCH(title) AGAINST ('+Data\\- +di*');   

SELECT title
    FROM articles  
    WHERE MATCH (title) AGAINST ('+Data* AND function?');

SELECT id, title, MATCH(body) AGAINST ('database') relevance
    FROM articles
    WHERE MATCH(body) AGAINST ('database');

SELECT id, title, MATCH(body) AGAINST ('database')
    FROM articles
    WHERE MATCH(body) AGAINST ('database') > .12;

UPDATE articles set name = concat(name,".DATABASE") where MATCH(body) AGAINST ('database');

DELETE from articles where MATCH(body) AGAINST ('database');