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'.