Working with Strings - MySQL - page 170

Each record contains the following fields:
• Book section ( O or N , signifying Old or New Testament)
• Book name and corresponding book number, from 1 to 66
• Chapter and verse numbers
• Text of the verse
To import the records into MySQL, create a table named kjv that looks like this:
CREATE TABLE kjv
(
bsect ENUM ( 'O' , 'N' ) NOT NULL , # book section ( testament )
bname VARCHAR ( 20 ) NOT NULL , # book name
bnum TINYINT UNSIGNED NOT NULL , # book number
cnum TINYINT UNSIGNED NOT NULL , # chapter number
vnum TINYINT UNSIGNED NOT NULL , # verse number
vtext TEXT NOT NULL , # text of verse
FULLTEXT ( vtext ) # full - text index
) ENGINE = MyISAM ; # can be InnoDB for MySQL 5 . 6 +
The table has a FULLTEXT index to enable its use in full-text searching. It also uses the
MyISAM storage engine. If you have MySQL 5.6 or higher and want to use InnoDB
instead, modify the ENGINE clause to ENGINE = InnoDB .
After creating the kjv table, load the kjv.txt file into it using this statement:
mysql> LOAD DATA LOCAL INFILE 'kjv.txt' INTO TABLE kjv;
You'll notice that the kjv table contains columns both for book names (Genesis, Exo‐
dus, ...) and for book numbers (1, 2, ...). The names and numbers have a fixed corre‐
spondence, and one can be derived from the other—a redundancy that means the table
is not in normal form. It's possible to eliminate the redundancy by storing just the topic
numbers (which take less space than the names), and then producing the names when
necessary in query results by joining the numbers to a mapping table that associates
each book number with the corresponding name. But I want to avoid using joins at this
point. Thus, the table includes book names so search results can be interpreted more
easily, and numbers so the results can be sorted easily into book order.
To perform a search using the FULLTEXT index, use MATCH() to name the indexed column
and AGAINST() to specify what text to look for. For example, you might wonder, “How
many times does the name Hadoram occur?” To answer that question, search the vtext
column using this statement:
mysql> SELECT COUNT(*) from kjv WHERE MATCH(vtext) AGAINST('Hadoram');
+----------+
| COUNT(*) |
+----------+
| 4 |
+----------+