In MySQL, there is a provision to search based on natural mode. Let us consider the following example to understand this in detail.
Creation of the table:
CREATE TABLE 'PROP_TBL' ( 'PROPERTY' VARCHAR(100), 'DOMAIN' VARCHAR(20), 'VALUE' VARCHAR(2000), 'PATH' VARCHAR(100), PRIMARY KEY ('PROPERTY', 'DOMAIN', 'PATH'), FULLTEXT (PROPERTY,DOMAIN))ENGINE=InnoDB;
And use the following insert statements,
INSERT INTO 'PROP_TBL' ('PROPERTY', 'DOMAIN', 'VALUE', 'PATH') VALUES ('CLEANUP MAX SESSION TIMEOUT', 'ALARMS', '1800000', '/MICROWAVE/TSSH');INSERT INTO 'PROP_TBL' ('PROPERTY', 'DOMAIN', 'VALUE', 'PATH') VALUES ('ALARM DATA PRIORITY', 'ALARMS', 'false', '/MICROWAVE/GLOBAL');INSERT INTO 'PROP_TBL' ('PROPERTY', 'DOMAIN', 'VALUE', 'PATH') VALUES ('CN FORB EQ LIST', 'ALARMS', ',4,5,6,7,11,', '/MICROWAVE/GLOBAL');INSERT INTO 'PROP_TBL' ('PROPERTY', 'DOMAIN', 'VALUE', 'PATH') VALUES ('COMMAND EXECUTION BLOCK TIME', 'ALARMS', '1000', '/MICROWAVE/TSSH');INSERT INTO 'PROP_TBL' ('PROPERTY', 'DOMAIN', 'VALUE', 'PATH') VALUES ('COMMAND EXECUTION SUFFIX', 'ALARMS', '', '/MICROWAVE/GLOBAL');INSERT INTO 'PROP_TBL' ('PROPERTY', 'DOMAIN', 'VALUE', 'PATH') VALUES ('COMMAND REPUSH TIME', 'ALARMS', '3', '/MICROWAVE/GLOBAL');INSERT INTO 'PROP_TBL' ('PROPERTY', 'DOMAIN', 'VALUE', 'PATH') VALUES ('COMMAND REPUSH TIMEOUT', 'ALARMS', '60', '/MICROWAVE/GLOBAL');INSERT INTO 'PROP_TBL' ('PROPERTY', 'DOMAIN', 'VALUE', 'PATH') VALUES ('COMMAND REPUSH TO NE', 'ALARMS', 'true', '/MICROWAVE/GLOBAL');INSERT INTO 'PROP_TBL' ('PROPERTY', 'DOMAIN', 'VALUE', 'PATH') VALUES ('COMMAND REPUSH WAIT TIME BTW CMDS', 'ALARMS', '60', '/MICROWAVE/GLOBAL');INSERT INTO 'PROP_TBL' ('PROPERTY', 'DOMAIN', 'VALUE', 'PATH') VALUES ('COMMAND RETRY COUNT', 'ALARMS', '3', '/MICROWAVE/TSSH');INSERT INTO 'PROP_TBL' ('PROPERTY', 'DOMAIN', 'VALUE', 'PATH') VALUES ('COMMON RETRY COUNT', 'ALARMS', '3', '/MICROWAVE/GLOBAL');INSERT INTO 'PROP_TBL' ('PROPERTY', 'DOMAIN', 'VALUE', 'PATH') VALUES ('COMMON RETRY TIMOUT PERIOD', 'ALARMS', '60', '/MICROWAVE/GLOBAL');
Here, note that we have used FULLTEXT (PROPERTY,DOMAIN) along with the table creation, which indicates that there can be a search on the columns involved in the FULLTEXT.
Querying the table as below, we have
Query:
SELECT * FROM PROP_TBL WHERE MATCH (PROPERTY,DOMAIN) AGAINST ('commanD' IN NATURAL LANGUAGE MODE);
Output
+-----------------------------------+--------+--------------+-------------------+| PROPERTY | DOMAIN | VALUE | PATH |+-----------------------------------+--------+--------------+-------------------+| COMMAND EXECUTION BLOCK TIME | ALARMS | 2500 | /MICROWAVE/TSSH || COMMAND EXECUTION SUFFIX | ALARMS | 300 | /MICROWAVE/GLOBAL || COMMAND REPUSH TIME | ALARMS | 35 | /MICROWAVE/GLOBAL || COMMAND REPUSH TIMEOUT | ALARMS | 60 | /MICROWAVE/GLOBAL || COMMAND REPUSH TO NE | ALARMS | true | /MICROWAVE/GLOBAL || COMMAND REPUSH WT TIME BTW CMDS | ALARMS | 50 | /MICROWAVE/GLOBAL || COMMAND RETRY COUNT | ALARMS | 3 | /MICROWAVE/TSSH |+-----------------------------------+--------+--------------+-------------------+
The output can vary based on the search text in the place of ‘commanD’.