MariaDB Crash Course - Ben Forta [56]
Input
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('+rabbit +bait' IN BOOLEAN MODE);
Analysis
This search matches rows that contain both the words rabbit and bait.
Input
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('rabbit bait' IN BOOLEAN MODE);
Analysis
Without operators specified, this search matches rows that contain at least one of rabbit or bait.
Input
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('"rabbit bait"' IN BOOLEAN MODE);
Analysis
This search matches the phrase rabbit bait instead of the two words rabbit and bait.
Input
SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('>rabbit Match both rabbit and carrot, increasing the rank of the former and decreasing the rank of the latter. Input SELECT note_text FROM productnotes WHERE Match(note_text) Against('+safe +( This search matches the words safe and combination, lowering the ranking of the latter. * * * Note: Ranked, But Not Sorted In boolean mode, rows will not be returned sorted descending by ranking score. * * * Full-Text Search Usage Notes • When indexing full-text data, short words are ignored and are excluded from the index. Short words are defined as those having three or fewer characters (this number can be changed if needed). • MariaDB comes with a built-in list of stopwords, words that are always ignored when indexing full-text data. This list can be overridden if needed. (Refer to the MariaDB documentation to learn how to accomplish this.) • Many words appear so frequently that searching on them would be useless (too many results would be returned). As such, MariaDB honors a 50% rule—if a word appears in 50% or more of the rows, it is treated as a stopword and is effectively ignored. (The 50% rule is not used for IN BOOLEAN MODE). • Full-text searching never returns any results if there are fewer than three rows in a table (because every word is always in at least 50% of the rows). • Single quote characters in words are ignored. For example, don't is indexed as dont. • Languages that don’t have word delimiters (including Japanese and Chinese) will not return full-text results properly. • As already noted, full-text searching is not supported in all database engines (it is supported in ARIA and MyISAM). * * * Note: No Proximity Operators One feature supported by many full-text search engines is proximity searching, the ability to search for words that are near each other (in the same sentence, in the same paragraph, or no more than a specific number of words apart, and so on). Proximity operators are not yet supported by MariaDB full-text searching, although this is planned for a future release. * * * Summary 19. Inserting Data In this chapter, you learn how to insert data into tables using the SQL INSERT statement. Understanding Data Insertion As its name suggests, INSERT is used to insert (add) rows to a database table. Insert can be used in several ways: • To insert a single complete row • To insert a single partial row • To insert multiple rows • To insert the results of a query We look at each of these in the following sections. * * * Tip: INSERT and
Before finishing this chapter, here are some important notes pertaining to the use of full-text searching:
In this chapter, you learned why full-text searching is used, and how to use the MariaDB Match() and Against() functions to perform these searches. You also learned about query expansion as a way to increase the chances of finding related matches, and how to use boolean mode for more granular lookup control.
SELECT is undoubtedly the most frequently used SQL statement (which is why the past 18 chapters were dedicated to it). But there are three other frequently used SQL statements that you should learn. The first one is INSERT. (You get to the other two in the next chapter.)