5.12 模式匹配中的大小寫問題
mysql> use cookbook
Database changed
mysql> select 'a' like 'A', 'a' regexp 'A';
+--------------+----------------+
| 'a' like 'A' | 'a' regexp 'A' |
+--------------+----------------+
| 1 | 1 |
+--------------+----------------+
1 row in set (0.04 sec)mysql> select 'a' regexp '[[:lower:]]', 'a' regexp '[[:upper:]]';
+--------------------------+--------------------------+
| 'a' regexp '[[:lower:]]' | 'a' regexp '[[:upper:]]' |
+--------------------------+--------------------------+
| 1 | 1 |
+--------------------------+--------------------------+
1 row in set (0.00 sec)mysql> set names latin1;
Query OK, 0 rows affected (0.01 sec)mysql> set @s = 'a' collate latin1_general_cs;
Query OK, 0 rows affected (0.01 sec)mysql> select @s like 'A', @s regexp 'A';
+-------------+---------------+
| @s like 'A' | @s regexp 'A' |
+-------------+---------------+
| 0 | 0 |
+-------------+---------------+
1 row in set (0.00 sec)mysql> set @s = 'a', @s_cs = 'a' collate latin1_general_cs;
Query OK, 0 rows affected (0.00 sec)mysql> select @s regexp '[[:upper:]]', @s_cs regexp '[[:upper:]];'-> ;
+-------------------------+-----------------------------+
| @s regexp '[[:upper:]]' | @s_cs regexp '[[:upper:]];' |
+-------------------------+-----------------------------+
| 1 | 0 |
+-------------------------+-----------------------------+
1 row in set (0.00 sec) Thursday, June 5, 2025
5.13 分割或者串聯字符串 p234/951
https://blog.csdn.net/liqfyiyi/article/details/50886752
--取得左側、中間、右側字串
mysql> select name, left(name, 2), mid(name, 3, 1), right(name, 3) from metal;
+----------+---------------+-----------------+----------------+
| name | left(name, 2) | mid(name, 3, 1) | right(name, 3) |
+----------+---------------+-----------------+----------------+
| copper | co | p | per |
| gold | go | l | old |
| iron | ir | o | ron |
| lead | le | a | ead |
| mercury | me | r | ury |
| platinum | pl | a | num |
| silver | si | l | ver |
| tin | ti | n | tin |
+----------+---------------+-----------------+----------------+
8 rows in set (0.18 sec)--取得字串substring()mysql> select name, substring(name, 4), mid(name, 4)from metal;
+----------+--------------------+--------------+
| name | substring(name, 4) | mid(name, 4) |
+----------+--------------------+--------------+
| copper | per | per |
| gold | d | d |
| iron | n | n |
| lead | d | d |
| mercury | cury | cury |
| platinum | tinum | tinum |
| silver | ver | ver |
| tin | | |
+----------+--------------------+--------------+
8 rows in set (0.00 sec)
--取得字串substring_index()
mysql> select name, substring_index(name, 'r', 1), substring_index(name, 'i', -1) from metal;
+----------+-------------------------------+--------------------------------+
| name | substring_index(name, 'r', 1) | substring_index(name, 'i', -1) |
+----------+-------------------------------+--------------------------------+
| copper | coppe | copper |
| gold | gold | gold |
| iron | i | ron |
| lead | lead | lead |
| mercury | me | mercury |
| platinum | platinum | num |
| silver | silve | lver |
| tin | tin | n |
+----------+-------------------------------+--------------------------------+
8 rows in set (0.00 sec)
結果分析
對于 SUBSTRING_INDEX(name, 'r', 1)（查找第一個 'r' 之前的部分）
金屬名 結果 解釋
copper coppe 第一個 'r' 出現在第5位，取前4個字符 "coppe"
gold gold 沒有 'r'，返回整個字符串
iron i 第一個 'r' 出現在第2位，取前1個字符 "i"
lead lead 沒有 'r'，返回整個字符串
mercury me 第一個 'r' 出現在第3位，取前2個字符 "me"
platinum platinum 沒有 'r'，返回整個字符串
silver silve 第一個 'r' 出現在第5位，取前4個字符 "silve"
tin tin 沒有 'r'，返回整個字符串
對于 SUBSTRING_INDEX(name, 'i', -1)（從右邊查找第一個 'i' 之后的部分）
金屬名 結果 解釋
copper copper 從右邊沒有找到 'i'，返回整個字符串
gold gold 從右邊沒有找到 'i'，返回整個字符串
iron ron 從右邊第一個 'i' 是開頭的 'i'，取其后內容 "ron"
lead lead 從右邊沒有找到 'i'，返回整個字符串
mercury mercury 從右邊沒有找到 'i'（注意 'i' 在中間但方向是從右找），返回整個字符串
platinum num 從右邊第一個 'i' 是 'platinum' 中的 'i'，取其后 "num"
silver lver 從右邊第一個 'i' 是 'silver' 中的 'i'，取其后 "lver"
tin n 從右邊第一個 'i' 是 'tin' 中的 'i'，取其后 "n"
關鍵發現
當分隔符不存在時，函數返回整個字符串負數的 count 參數表示從右向左搜索搜索是區分大小寫的（'I' 和 'i' 不同）返回結果不包含分隔符本身
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(name, 'r', 2), -- 取到第二個 'r''r', -1 -- 從右邊取第一個 'r' 之后的部分)
FROM metal;
這個查詢展示了 MySQL 字符串處理的靈活性，特別適用于日志分析、數據清洗等場景。
+--------------------------------------------------------+
| substring_index(substring_index(name, 'r', 2),'r', -1) |
+--------------------------------------------------------+
| |
| gold |
| on |
| lead |
| cu |
| platinum |
| |
| tin |
+--------------------------------------------------------+
8 rows in set (0.00 sec)
mysql> select name from metal where left(name, 1)>= 'n';
+----------+
| name |
+----------+
| platinum |
| silver |
| tin |
+----------+
3 rows in set (0.01 sec)
–拼接字符串concat()
mysql> use cookbook
Database changed
mysql> select concat('Hello', user(), ', welcome to MySQL!') as greeting;
+----------------------------------------+
| greeting |
+----------------------------------------+
| Helloroot@localhost, welcome to MySQL! |
+----------------------------------------+
1 row in set (0.00 sec)mysql> select concat(name, ' ends in "d": ', if(right(name, 1)='d', 'yes', 'no')) as 'ends in "d"?' from metal;
+-----------------------------+
| ends in "d"? |
+-----------------------------+
| copperide ends in "d": no |
| goldide ends in "d": no |
| ironide ends in "d": no |
| leadide ends in "d": no |
| mercuryide ends in "d": no |
| platinumide ends in "d": no |
| silveride ends in "d": no |
| tinide ends in "d": no |
+-----------------------------+
8 rows in set (0.01 sec)mysql> update metal set name = concat(name, 'ide');
Query OK, 8 rows affected (0.01 sec)
Rows matched: 8 Changed: 8 Warnings: 0select name from metal;mysql> -- 刪除表(如果已存在)
mysql> DROP TABLE IF EXISTS tbl_name;
Query OK, 0 rows affected, 1 warning (0.01 sec)mysql>
mysql> -- 創建表
mysql> CREATE TABLE tbl_name (-> id INT AUTO_INCREMENT PRIMARY KEY,-> name VARCHAR(50) NOT NULL,-> set_col VARCHAR(255),-> val VARCHAR(100),-> created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP-> );
Query OK, 0 rows affected (0.10 sec)mysql>
mysql> -- 插入測試數據
mysql> INSERT INTO tbl_name (name, set_col, val) VALUES-> ('item1', NULL, 'gold'),-> ('item2', 'copper', 'silver'),-> ('item3', 'iron,steel', 'carbon'),-> ('item4', NULL, 'aluminum');
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0mysql>
mysql> -- 執行更新
mysql> UPDATE tbl_name-> SET set_col = IF(set_col IS NULL, val, CONCAT(set_col, ', ', val));
Query OK, 4 rows affected (0.01 sec)
Rows matched: 4 Changed: 4 Warnings: 0mysql>
mysql> -- 查看結果
mysql> SELECT * FROM tbl_name;
+----+-------+--------------------+----------+---------------------+
| id | name | set_col | val | created_at |
+----+-------+--------------------+----------+---------------------+
| 1 | item1 | gold | gold | 2025-06-08 15:26:50 |
| 2 | item2 | copper, silver | silver | 2025-06-08 15:26:50 |
| 3 | item3 | iron,steel, carbon | carbon | 2025-06-08 15:26:50 |
| 4 | item4 | aluminum | aluminum | 2025-06-08 15:26:50 |
+----+-------+--------------------+----------+---------------------+
4 rows in set (0.00 sec)mysql> update tbl_name set set_col = if(set_col is null, val, concat(set_col, ', ', val));
Query OK, 4 rows affected (0.01 sec)
Rows matched: 4 Changed: 4 Warnings: 0mysql> select * from tbl_name;
+----+-------+----------------------------+----------+---------------------+
| id | name | set_col | val | created_at |
+----+-------+----------------------------+----------+---------------------+
| 1 | item1 | gold, gold | gold | 2025-06-08 15:26:50 |
| 2 | item2 | copper, silver, silver | silver | 2025-06-08 15:26:50 |
| 3 | item3 | iron,steel, carbon, carbon | carbon | 2025-06-08 15:26:50 |
| 4 | item4 | aluminum, aluminum | aluminum | 2025-06-08 15:26:50 |
+----+-------+----------------------------+----------+---------------------+
4 rows in set (0.00 sec)mysql> update metal set name = left(name, char_length(name) - 3);
Query OK, 8 rows affected (0.01 sec)
Rows matched: 8 Changed: 8 Warnings: 0mysql> select * from tbl_name;
+----+-------+----------------------------+----------+---------------------+
| id | name | set_col | val | created_at |
+----+-------+----------------------------+----------+---------------------+
| 1 | item1 | gold, gold | gold | 2025-06-08 15:26:50 |
| 2 | item2 | copper, silver, silver | silver | 2025-06-08 15:26:50 |
| 3 | item3 | iron,steel, carbon, carbon | carbon | 2025-06-08 15:26:50 |
| 4 | item4 | aluminum, aluminum | aluminum | 2025-06-08 15:26:50 |
+----+-------+----------------------------+----------+---------------------+
4 rows in set (0.00 sec)mysql> select name from metal;
+-------------+
| name |
+-------------+
| copperide |
| goldide |
| ironide |
| leadide |
| mercuryide |
| platinumide |
| silveride |
| tinide |
+-------------+
8 rows in set (0.00 sec)mysql> update metal set name = left(name, char_length(name) - 3);
Query OK, 8 rows affected (0.01 sec)
Rows matched: 8 Changed: 8 Warnings: 0mysql> select * from metal;
+----------+-------------+
| name | composition |
+----------+-------------+
| copper | gold,base |
| gold | base |
| iron | base |
| lead | base |
| mercury | base |
| platinum | base |
| silver | base |
| tin | base |
+----------+-------------+
8 rows in set (0.00 sec)
5.14 查詢子串
select name, locate('in', name), locate('in', name, 3) from metal;
5.15 使用fulltext查詢
方法2：永久啟用（修改配置文件）
找到 MySQL 配置文件 my.cnf 或 my.ini
Linux: /etc/my.cnf 或 /etc/mysql/my.cnfWindows: C:\ProgramData\MySQL\MySQL Server 8.0\my.ini
在 [mysqld] 和 [client] 部分添加：text
[mysqld]
local_infile=1[client]
local_infile=1
重啟 MySQL 服務：bash
# Linux
sudo service mysql restart# Windows
net stop MySQL80
net start MySQL80--創建表
create table kjv
(bsect enum('0', 'N') not null, bname varchar(20) not null, bnum tinyint unsigned not null,cnum tinyint unsigned not null,vnum tinyint unsigned not null,vtext text not null
) engine = MyISAM;--導入初始化數據
D:\software\MySql\bin>mysql --local-infile=1 -u root -p
Enter password: ****
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 22
Server version: 8.0.40 MySQL Community Server - GPLCopyright (c) 2000, 2024, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> set global local_infile = 1;
Query OK, 0 rows affected (0.01 sec)mysql> use cookbook;
Database changed
mysql> show tables;
+-----------------------+
| Tables_in_cookbook |
+-----------------------+
| actors |
| adcount |
| invoice |
| item |
| kjv |
| limbs |
| limbs_backup |
+-----------------------+
128 rows in set (0.01 sec)mysql> load data local infile 'D:/sql/Mysql_learning/mcb-kjv/kjv.txt' into table kjv;
Query OK, 31102 rows affected (0.70 sec)
Records: 31102 Deleted: 0 Skipped: 0 Warnings: 0
--添加全文索引mysql> alter table kjv add fulltext(vtext);
Query OK, 31102 rows affected (1.86 sec)
Records: 31102 Duplicates: 0 Warnings: 0mysql> SELECT * FROM kjv LIMIT 5;
+-------+---------+------+------+------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| bsect | bname | bnum | cnum | vnum | vtext |
+-------+---------+------+------+------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| O | Genesis | 1 | 1 | 1 | In the beginning God created the heaven and the earth. |
| O | Genesis | 1 | 1 | 2 | And the earth was without form, and void; and darkness [was] upon the face of the deep. And the Spirit of God moved upon the face of the waters. |
| O | Genesis | 1 | 1 | 3 | And God said, Let there be light: and there was light. |
| O | Genesis | 1 | 1 | 4 | And God saw the light, that [it was] good: and God divided the light from the darkness. |
| O | Genesis | 1 | 1 | 5 | And God called the light Day, and the darkness he called Night. And the evening and the morning were the first day. |
+-------+---------+------+------+------+--------------------------------------------------------------------------------------------------------------------------------------------------+
5 rows in set (0.00 sec)
1. 基礎檢索示例
(1) 查找特定書卷章節
sql
-- 查找創世記第3章全部經文
SELECT vnum, vtext
FROM kjv
WHERE bname = 'Genesis' AND bnum = 1 AND cnum = 3
ORDER BY vnum;
+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| vnum | vtext |
+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | Now the serpent was more subtil than any beast of the field which the LORD God had made. And he said unto the woman, Yea, hath God said, Ye shall not eat of every tree of the garden? |
| 2 | And the woman said unto the serpent, We may eat of the fruit of the trees of the garden: |
| 3 | But of the fruit of the tree which [is] in the midst of the garden, God hath said, Ye shall not eat of it, neither shall ye touch it, lest ye die. |
| 4 | And the serpent said unto the woman, Ye shall not surely die: |
| 5 | For God doth know that in the day ye eat thereof, then your eyes shall be opened, and ye shall be as gods, knowing good and evil. |
| 6 | And when the woman saw that the tree [was] good for food, and that it [was] pleasant to the eyes, and a tree to be desired to make [one] wise, she took of the fruit thereof, and did eat, and gave also unto her husband with her; and he did eat. |
| 7 | And the eyes of them both were opened, and they knew that they [were] naked; and they sewed fig leaves together, and made themselves aprons. |
| 8 | And they heard the voice of the LORD God walking in the garden in the cool of the day: and Adam and his wife hid themselves from the presence of the LORD God amongst the trees of the garden. |
| 9 | And the LORD God called unto Adam, and said unto him, Where [art] thou? |
| 10 | And he said, I heard thy voice in the garden, and I was afraid, because I [was] naked; and I hid myself. |
| 11 | And he said, Who told thee that thou [wast] naked? Hast thou eaten of the tree, whereof I commanded thee that thou shouldest not eat? |
| 12 | And the man said, The woman whom thou gavest [to be] with me, she gave me of the tree, and I did eat. |
| 13 | And the LORD God said unto the woman, What [is] this [that] thou hast done? And the woman said, The serpent beguiled me, and I did eat. |
| 14 | And the LORD God said unto the serpent, Because thou hast done this, thou [art] cursed above all cattle, and above every beast of the field; upon thy belly shalt thou go, and dust shalt thou eat all the days of thy life: |
| 15 | And I will put enmity between thee and the woman, and between thy seed and her seed; it shall bruise thy head, and thou shalt bruise his heel. |
| 16 | Unto the woman he said, I will greatly multiply thy sorrow and thy conception; in sorrow thou shalt bring forth children; and thy desire [shall be] to thy husband, and he shall rule over thee. |
| 17 | And unto Adam he said, Because thou hast hearkened unto the voice of thy wife, and hast eaten of the tree, of which I commanded thee, saying, Thou shalt not eat of it: cursed [is] the ground for thy sake; in sorrow shalt thou eat [of] it all the days of thy life; |
| 18 | Thorns also and thistles shall it bring forth to thee; and thou shalt eat the herb of the field; |
| 19 | In the sweat of thy face shalt thou eat bread, till thou return unto the ground; for out of it wast thou taken: for dust thou [art], and unto dust shalt thou return. |
| 20 | And Adam called his wife's name Eve; because she was the mother of all living. |
| 21 | Unto Adam also and to his wife did the LORD God make coats of skins, and clothed them. |
| 22 | And the LORD God said, Behold, the man is become as one of us, to know good and evil: and now, lest he put forth his hand, and take also of the tree of life, and eat, and live for ever: |
| 23 | Therefore the LORD God sent him forth from the garden of Eden, to till the ground from whence he was taken. |
| 24 | So he drove out the man; and he placed at the east of the garden of Eden Cherubims, and a flaming sword which turned every way, to keep the way of the tree of life. |
+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
24 rows in set (0.30 sec)
(2) 統計各書卷經文數量
mysql> SELECT bname, COUNT(*) as verse_count-> FROM kjv-> GROUP BY bname-> ORDER BY verse_count DESC;
+-----------------+-------------+
| bname | verse_count |
+-----------------+-------------+
| Psalms | 2461 |
| Genesis | 1533 |
| Jeremiah | 1364 |
| Isaiah | 1292 |
| Numbers | 1288 |
| Ezekiel | 1273 |
| Exodus | 1213 |
| Luke | 1151 |
| Matthew | 1071 |
| Job | 1070 |
| Acts | 1007 |
| Deuteronomy | 959 |
| 1 Chronicles | 942 |
| Proverbs | 915 |
| John | 879 |
| Leviticus | 859 |
| 2 Chronicles | 822 |
| 1 Kings | 816 |
| 1 Samuel | 810 |
| 2 Kings | 719 |
| 2 Samuel | 695 |
| Mark | 678 |
| Joshua | 658 |
| Judges | 618 |
| 1 Corinthians | 437 |
| Romans | 433 |
| Nehemiah | 406 |
| Revelation | 404 |
| Daniel | 357 |
| Hebrews | 303 |
| Ezra | 280 |
| 2 Corinthians | 257 |
| Ecclesiastes | 222 |
| Zechariah | 211 |
| Hosea | 197 |
| Esther | 167 |
| Ephesians | 155 |
| Lamentations | 154 |
| Galatians | 149 |
| Amos | 146 |
| Song of Solomon | 117 |
| 1 Timothy | 113 |
| James | 108 |
| Micah | 105 |
| 1 Peter | 105 |
| 1 John | 105 |
| Philippians | 104 |
| Colossians | 95 |
| 1 Thessalonians | 89 |
| Ruth | 85 |
| 2 Timothy | 83 |
| Joel | 73 |
| 2 Peter | 61 |
| Habakkuk | 56 |
| Malachi | 55 |
| Zephaniah | 53 |
| Jonah | 48 |
| Nahum | 47 |
| 2 Thessalonians | 47 |
| Titus | 46 |
| Haggai | 38 |
| Philemon | 25 |
| Jude | 25 |
| Obadiah | 21 |
| 3 John | 14 |
| 2 John | 13 |
+-----------------+-------------+
66 rows in set (0.36 sec)
- 全文搜索高級用法
(1) 搜索包含兩個關鍵詞的經文（布爾模式）
mysql> SELECT CONCAT(bname,' ',bnum,':',cnum,':',vnum) AS reference,-> SUBSTRING(vtext, 1, 50) AS excerpt-> FROM kjv-> WHERE MATCH(vtext) AGAINST('+creation +light' IN BOOLEAN MODE);
Empty set (0.01 sec)
(2) 排除特定詞的搜索
mysql> SELECT CONCAT(bname,' ',bnum,':',cnum,':',vnum) AS reference,-> vtext-> FROM kjv-> WHERE MATCH(vtext) AGAINST('angel -fallen' IN BOOLEAN MODE)-> LIMIT 10;
+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| reference | vtext |
+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Genesis 1:16:7 | And the angel of the LORD found her by a fountain of water in the wilderness, by the fountain in the way to Shur. |
| Genesis 1:16:9 | And the angel of the LORD said unto her, Return to thy mistress, and submit thyself under her hands. |
| Genesis 1:16:10 | And the angel of the LORD said unto her, I will multiply thy seed exceedingly, that it shall not be numbered for multitude. |
| Genesis 1:16:11 | And the angel of the LORD said unto her, Behold, thou [art] with child, and shalt bear a son, and shalt call his name Ishmael; because the LORD hath heard thy affliction. |
| Genesis 1:21:17 | And God heard the voice of the lad; and the angel of God called to Hagar out of heaven, and said unto her, What aileth thee, Hagar? fear not; for God hath heard the voice of the lad where he [is]. |
| Genesis 1:22:11 | And the angel of the LORD called unto him out of heaven, and said, Abraham, Abraham: and he said, Here [am] I. |
| Genesis 1:22:15 | And the angel of the LORD called unto Abraham out of heaven the second time, |
| Genesis 1:24:7 | The LORD God of heaven, which took me from my father's house, and from the land of my kindred, and which spake unto me, and that sware unto me, saying, Unto thy seed will I give this land; he shall send his angel before thee, and thou shalt take a wife unto my son from thence. |
| Genesis 1:24:40 | And he said unto me, The LORD, before whom I walk, will send his angel with thee, and prosper thy way; and thou shalt take a wife for my son of my kindred, and of my father's house: |
| Genesis 1:31:11 | And the angel of God spake unto me in a dream, [saying], Jacob: And I said, Here [am] I. |
+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
10 rows in set (0.00 sec)
- 數據探索技巧
(1) 查找最長經文
mysql> SELECT bname, bnum, cnum, vnum, LENGTH(vtext) AS length-> FROM kjv-> ORDER BY length DESC-> LIMIT 5;
+----------+------+------+------+--------+
| bname | bnum | cnum | vnum | length |
+----------+------+------+------+--------+
| Esther | 17 | 8 | 9 | 534 |
| 2 Kings | 12 | 16 | 15 | 445 |
| Jeremiah | 24 | 21 | 7 | 441 |
| Ezekiel | 26 | 48 | 21 | 434 |
| Esther | 17 | 3 | 12 | 433 |
+----------+------+------+------+--------+
5 rows in set (0.29 sec)
(2) 統計新約/舊約經文比例
mysql> SELECT bsect,-> COUNT(*) AS verses,-> ROUND(COUNT(*)/(SELECT COUNT(*) FROM kjv)*100,1) AS percentage-> FROM kjv-> GROUP BY bsect;
+-------+--------+------------+
| bsect | verses | percentage |
+-------+--------+------------+
| O | 23145 | 74.4 |
| N | 7957 | 25.6 |
+-------+--------+------------+
2 rows in set (0.34 sec)
4. 創建常用視圖
sql
-- 創建新約經文視圖
mysql> create view nt_verses as-> select * from kjv where bsect = 'N';
Query OK, 0 rows affected (0.02 sec)
-- 創建詩篇視圖
mysql> CREATE VIEW psalms AS-> SELECT bnum AS psalm_number, cnum, vnum, vtext-> FROM kjv-> WHERE bname = 'Psalms';
Query OK, 0 rows affected (0.01 sec)
5. 跨章節檢索
sql
-- 查找"十誡"相關經文（出埃及記20:1-17）
mysql> SELECT vnum, vtext-> FROM kjv-> WHERE bname = 'Exodus' AND bnum = 2 AND cnum = 20-> AND vnum BETWEEN 1 AND 17;
+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| vnum | vtext |
+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | And God spake all these words, saying, |
| 2 | I [am] the LORD thy God, which have brought thee out of the land of Egypt, out of the house of bondage. |
| 3 | Thou shalt have no other gods before me. |
| 4 | Thou shalt not make unto thee any graven image, or any likeness [of any thing] that [is] in heaven above, or that [is] in the earth beneath, or that [is] in the water under the earth: |
| 5 | Thou shalt not bow down thyself to them, nor serve them: for I the LORD thy God [am] a jealous God, visiting the iniquity of the fathers upon the children unto the third and fourth [generation] of them that hate me; |
| 6 | And shewing mercy unto thousands of them that love me, and keep my commandments. |
| 7 | Thou shalt not take the name of the LORD thy God in vain; for the LORD will not hold him guiltless that taketh his name in vain. |
| 8 | Remember the sabbath day, to keep it holy. |
| 9 | Six days shalt thou labour, and do all thy work: |
| 10 | But the seventh day [is] the sabbath of the LORD thy God: [in it] thou shalt not do any work, thou, nor thy son, nor thy daughter, thy manservant, nor thy maidservant, nor thy cattle, nor thy stranger that [is] within thy gates: |
| 11 | For [in] six days the LORD made heaven and earth, the sea, and all that in them [is], and rested the seventh day: wherefore the LORD blessed the sabbath day, and hallowed it. |
| 12 | Honour thy father and thy mother: that thy days may be long upon the land which the LORD thy God giveth thee. |
| 13 | Thou shalt not kill. |
| 14 | Thou shalt not commit adultery. |
| 15 | Thou shalt not steal. |
| 16 | Thou shalt not bear false witness against thy neighbour. |
| 17 | Thou shalt not covet thy neighbour's house, thou shalt not covet thy neighbour's wife, nor his manservant, nor his maidservant, nor his ox, nor his ass, nor any thing that [is] thy neighbour's. |
+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
17 rows in set (0.28 sec)
6. 數據導出示例
mysql> SHOW VARIABLES LIKE 'secure_file_priv';
+------------------+---------------------------------+
| Variable_name | Value |
+------------------+---------------------------------+
| secure_file_priv | D:\software\MySql\Data\Uploads\ |
+------------------+---------------------------------+
1 row in set, 1 warning (0.08 sec)
-- 導出約翰福音3:16到文件-- 錯誤（注意路徑前的空格）：
INTO OUTFILE ' D:/software/MySql/Data/Uploads/john_3_16.txt'-- 正確寫法（去掉空格）：
INTO OUTFILE 'D:/software/MySql/Data/Uploads/john_3_16.txt'mysql> SELECT vtext-> INTO OUTFILE 'D:\\software\\MySql\\Data\\Uploads\\john_3_16.txt'-> FROM kjv-> WHERE bname = 'John' AND bnum = 4 AND cnum = 3 AND vnum = 16;
Query OK, 0 rows affected (0.37 sec)D:\software\MySql\bin>mysql -u root -p -e "select vtext from cookbook.kjv where bname='John' and bnum= 43 and cnum= 3 and vnum = 16" > C:\Users\lenovo\desktop\john.txt
Enter password: ****D:\software\MySql\bin>1. 正確的查詢語句
sql
SELECT vtext
FROM kjv
WHERE bname = 'John' AND bnum = 43 AND cnum = 3 AND vnum = 16;
2. 驗證約翰福音第3章的節數范圍
sql
-- 查看約翰福音第3章有多少節
SELECT MIN(vnum), MAX(vnum)
FROM kjv
WHERE bname = 'John' AND bnum = 43 AND cnum = 3;
- 導出正確的經文到文件
sql
-- 方法一：使用INTO OUTFILE（需在secure_file_priv目錄）
SELECT vtext
INTO OUTFILE 'D:/software/MySql/Data/Uploads/john_3_16.txt'
FROM kjv
WHERE bname = 'John' AND bnum = 43 AND cnum = 3 AND vnum = 16;-- 方法二：使用命令行重定向
mysql -u root -p -e "SELECT vtext FROM cookbook.kjv WHERE bname='John' AND bnum=43 AND cnum=3 AND vnum=16" > john_3_16.txt
4. 其他有用的查詢
sql
-- 查看約翰福音3:16附近的經文（15-17節）
SELECT vnum, vtext
FROM kjv
WHERE bname = 'John' AND bnum = 43 AND cnum = 3
AND vnum BETWEEN 15 AND 17
ORDER BY vnum;-- 統計約翰福音的章節數
SELECT cnum, COUNT(*) as verse_count
FROM kjv
WHERE bname = 'John' AND bnum = 43
GROUP BY cnum
ORDER BY cnum;
注意事項
不同圣經譯本的書卷編號可能不同確保導出目錄有寫入權限如果使用命令行導出，注意字符編碼問題（建議添加--default-character-set=utf8）--查詢'Mizraim'一共出現了多少次mysql> select count(*)from kjv where match(vtext) against('Mizraim');
+----------+
| count(*) |
+----------+
| 4 |
+----------+
1 row in set (0.01 sec)
--查詢'Mizraim'具體出現在什么地方
mysql> select bname, cnum, vnum, vtext-> from kjv where match(vtext) against('Mizraim')\G
*************************** 1. row ***************************
bname: 1 Chroniclescnum: 1vnum: 8
vtext: The sons of Ham; Cush, and Mizraim, Put, and Canaan.
*************************** 2. row ***************************
bname: Genesiscnum: 10vnum: 6
vtext: And the sons of Ham; Cush, and Mizraim, and Phut, and Canaan.
*************************** 3. row ***************************
bname: Genesiscnum: 10vnum: 13
vtext: And Mizraim begat Ludim, and Anamim, and Lehabim, and Naphtuhim,
*************************** 4. row ***************************
bname: 1 Chroniclescnum: 1vnum: 11
vtext: And Mizraim begat Ludim, and Anamim, and Lehabim, and Naphtuhim,
4 rows in set (0.00 sec)select bname, cnum, vnum, vtext from kjv where match(vtext) against('search string') order by bnum, cnum, vnum\G
vtext: And straightway his ears were opened, and the string of his tongue was loosed, and he spake plain.
*************************** 47. row ***************************
bname: Johncnum: 5vnum: 39
vtext: Search the scriptures; for in them ye think ye have eternal life: and they are they which testify of me.
*************************** 48. row ***************************
bname: Johncnum: 7vnum: 52
vtext: They answered and said unto him, Art thou also of Galilee? Search, and look: for out of Galilee ariseth no prophet.
48 rows in set (0.02 sec)
select count(*) from kjv where match(vtext) against('Abraham') and bsect = 'N';
+----------+
| count(*) |
+----------+
| 70 |
+----------+
1 row in set (0.02 sec)
select count(*) from kjv where match(vtext) against('Abraham') and bname = 'Hebrews';
+----------+
| count(*) |
+----------+
| 10 |
+----------+
1 row in set (0.00 sec)
select count(*) from kjv where match(vtext) against('Abraham') and bname = 'Hebrews' and cnum = 11;
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)mysql> alter table kjv add index(bnum), add index(cnum), add index(vnum);
Query OK, 31102 rows affected (3.51 sec)
Records: 31102 Duplicates: 0 Warnings: 0mysql> select count(*) from kjv-> where match(vtext) against('Abraham');
+----------+
| count(*) |
+----------+
| 230 |
+----------+
1 row in set (0.00 sec)mysql> select count(*) from kjv where match(vtext) against('Abraham Sarah');
+----------+
| count(*) |
+----------+
| 244 |
+----------+
1 row in set (0.00 sec)mysql> select count(*) from kjv where match(vtext) against('Abraham Sarah Ishmael Isaac');
+----------+
| count(*) |
+----------+
| 334 |
+----------+
1 row in set (0.01 sec)mysql> alter table tbl_name-> add column col1 text,-> add column col2 text,-> add column col3 text;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> alter table tbl_name add fulltext (col1, col2, col3);
Query OK, 0 rows affected, 1 warning (0.64 sec)
Records: 0 Duplicates: 0 Warnings: 1mysql> show index from tbl_name;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| tbl_name | 0 | PRIMARY | 1 | id | A | 4 | NULL | NULL | | BTREE | | | YES | NULL |
| tbl_name | 1 | col1 | 1 | col1 | NULL | 4 | NULL | NULL | YES | FULLTEXT | | | YES | NULL |
| tbl_name | 1 | col1 | 2 | col2 | NULL | 4 | NULL | NULL | YES | FULLTEXT | | | YES | NULL |
| tbl_name | 1 | col1 | 3 | col3 | NULL | 4 | NULL | NULL | YES | FULLTEXT | | | YES | NULL |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
4 rows in set (0.03 sec)
– 插入測試數據
mysql> INSERT INTO tbl_name (name, col1, col2, col3) VALUES-> ('Record 1', 'MySQL database management', 'text search functions', 'fulltext indexing'),-> ('Record 2', 'Python programming language', 'data analysis', 'machine learning');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0mysql> -- 自然語言搜索
mysql> SELECT * FROM tbl_name-> WHERE MATCH(col1, col2, col3) AGAINST('database');
+----+----------+---------+------+---------------------+---------------------------+-----------------------+-------------------+
| id | name | set_col | val | created_at | col1 | col2 | col3 |
+----+----------+---------+------+---------------------+---------------------------+-----------------------+-------------------+
| 5 | Record 1 | NULL | NULL | 2025-06-08 18:47:22 | MySQL database management | text search functions | fulltext indexing |
+----+----------+---------+------+---------------------+---------------------------+-----------------------+-------------------+
1 row in set (0.00 sec)mysql> -- 布爾模式搜索（精確匹配）
mysql> SELECT * FROM tbl_name-> WHERE MATCH(col1, col2, col3) AGAINST('+Python -Java' IN BOOLEAN MODE);
+----+----------+---------+------+---------------------+-----------------------------+---------------+------------------+
| id | name | set_col | val | created_at | col1 | col2 | col3 |
+----+----------+---------+------+---------------------+-----------------------------+---------------+------------------+
| 6 | Record 2 | NULL | NULL | 2025-06-08 18:47:22 | Python programming language | data analysis | machine learning |
+----+----------+---------+------+---------------------+-----------------------------+---------------+------------------+
1 row in set (0.00 sec)全文索引使用進階技巧
1. 多關鍵詞權重查詢
SELECT name,MATCH(col1, col2, col3) AGAINST('database Python' IN NATURAL LANGUAGE MODE) AS relevance_score
FROM tbl_name
ORDER BY relevance_score DESC;
+----------+--------------------+
| name | relevance_score |
+----------+--------------------+
| Record 1 | 0.6055193543434143 |
| Record 2 | 0.6055193543434143 |
| item1 | 0 |
| item2 | 0 |
| item3 | 0 |
| item4 | 0 |
+----------+--------------------+
6 rows in set (0.00 sec)
2. 短語精確搜索
mysql> SELECT * FROM tbl_name-> WHERE MATCH(col1, col2, col3) AGAINST('"programming language"' IN BOOLEAN MODE);
+----+----------+---------+------+---------------------+-----------------------------+---------------+------------------+
| id | name | set_col | val | created_at | col1 | col2 | col3 |
+----+----------+---------+------+---------------------+-----------------------------+---------------+------------------+
| 6 | Record 2 | NULL | NULL | 2025-06-08 18:47:22 | Python programming language | data analysis | machine learning |
+----+----------+---------+------+---------------------+-----------------------------+---------------+------------------+
1 row in set (0.00 sec)
3. 通配符搜索
mysql> SELECT * FROM tbl_name-> WHERE MATCH(col1, col2, col3) AGAINST('data*' IN BOOLEAN MODE);
+----+----------+---------+------+---------------------+-----------------------------+-----------------------+-------------------+
| id | name | set_col | val | created_at | col1 | col2 | col3 |
+----+----------+---------+------+---------------------+-----------------------------+-----------------------+-------------------+
| 5 | Record 1 | NULL | NULL | 2025-06-08 18:47:22 | MySQL database management | text search functions | fulltext indexing |
| 6 | Record 2 | NULL | NULL | 2025-06-08 18:47:22 | Python programming language | data analysis | machine learning |
+----+----------+---------+------+---------------------+-----------------------------+-----------------------+-------------------+
2 rows in set (0.00 sec)
性能優化建議
- 索引重建（數據量大時）：
mysql> ALTER TABLE tbl_name DROP INDEX col1;
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> ALTER TABLE tbl_name ADD FULLTEXT ft_content(col1, col2, col3);
Query OK, 0 rows affected (0.33 sec)
Records: 0 Duplicates: 0 Warnings: 0
2. 結果分頁：
mysql> SELECT * FROM tbl_name-> WHERE MATCH(col1, col2, col3) AGAINST('search term')-> LIMIT 10 OFFSET 0;
+----+----------+---------+------+---------------------+---------------------------+-----------------------+-------------------+
| id | name | set_col | val | created_at | col1 | col2 | col3 |
+----+----------+---------+------+---------------------+---------------------------+-----------------------+-------------------+
| 5 | Record 1 | NULL | NULL | 2025-06-08 18:47:22 | MySQL database management | text search functions | fulltext indexing |
+----+----------+---------+------+---------------------+---------------------------+-----------------------+-------------------+
（1 row in set (0.00 sec)
監控索引使用：
mysql> EXPLAIN SELECT * FROM tbl_name-> WHERE MATCH(col1, col2, col3) AGAINST('MySQL');
+----+-------------+----------+------------+----------+---------------+------------+---------+-------+------+----------+-------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+----------+---------------+------------+---------+-------+------+----------+-------------------------------+
| 1 | SIMPLE | tbl_name | NULL | fulltext | ft_content | ft_content | 0 | const | 1 | 100.00 | Using where; Ft_hints: sorted |
+----+-------------+----------+------------+----------+---------------+------------+---------+-------+------+----------+-------------------------------+
1 row in set, 1 warning (0.00 sec)
- 高級搜索技巧
(1) 權重控制
mysql> ALTER TABLE tbl_name ADD FULLTEXT(col1, col2);
Query OK, 0 rows affected (0.31 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> SELECT *,-> MATCH(col1, col2, col3) AGAINST('Python analysis' IN NATURAL LANGUAGE MODE) AS weighted_score-> FROM tbl_name-> ORDER BY weighted_score DESC;
+----+----------+----------------------------+----------+---------------------+-----------------------------+-----------------------+-------------------+--------------------+
| id | name | set_col | val | created_at | col1 | col2 | col3 | weighted_score |
+----+----------+----------------------------+----------+---------------------+-----------------------------+-----------------------+-------------------+--------------------+
| 6 | Record 2 | NULL | NULL | 2025-06-08 18:47:22 | Python programming language | data analysis | machine learning | 1.2110387086868286 |
| 1 | item1 | gold, gold | gold | 2025-06-08 15:26:50 | NULL | NULL | NULL | 0 |
| 2 | item2 | copper, silver, silver | silver | 2025-06-08 15:26:50 | NULL | NULL | NULL | 0 |
| 3 | item3 | iron,steel, carbon, carbon | carbon | 2025-06-08 15:26:50 | NULL | NULL | NULL | 0 |
| 4 | item4 | aluminum, aluminum | aluminum | 2025-06-08 15:26:50 | NULL | NULL | NULL | 0 |
| 5 | Record 1 | NULL | NULL | 2025-06-08 18:47:22 | MySQL database management | text search functions | fulltext indexing | 0 |
+----+----------+----------------------------+----------+---------------------+-----------------------------+-----------------------+-------------------+--------------------+
6 rows in set (0.00 sec) (2) 排除停用詞-- 查看當前停用詞列表
mysql> SELECT * FROM information_schema.INNODB_FT_DEFAULT_STOPWORD;
+-------+
| value |
+-------+
| a |
| about |
| an |
| are |
| as |
| at |
| be |
| by |
| com |
| de |
| en |
| for |
| from |
| how |
| i |
| in |
| is |
| it |
| la |
| of |
| on |
| or |
| that |
| the |
| this |
| to |
| was |
| what |
| when |
| where |
| who |
| will |
| with |
| und |
| the |
| www |
+-------+
36 rows in set (0.01 sec)4. 性能監控建議
-- 查看索引統計
mysql> SELECT table_name, index_name, stat_value-> FROM mysql.innodb_index_stats-> WHERE table_name = 'tbl_name';
+------------+------------------+------------+
| table_name | index_name | stat_value |
+------------+------------------+------------+
| tbl_name | FTS_DOC_ID_INDEX | 6 |
| tbl_name | FTS_DOC_ID_INDEX | 1 |
| tbl_name | FTS_DOC_ID_INDEX | 1 |
| tbl_name | PRIMARY | 6 |
| tbl_name | PRIMARY | 1 |
| tbl_name | PRIMARY | 1 |
+------------+------------------+------------+
6 rows in set (0.01 sec)
-- 檢查索引使用情況
mysql> ANALYZE TABLE tbl_name;
+-------------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-------------------+---------+----------+----------+
| cookbook.tbl_name | analyze | status | OK |
+-------------------+---------+----------+----------+
1 row in set (0.02 sec)
5. 實際應用示例
構建搜索頁面時：
-- 分頁搜索（每頁10條）
mysql> -- 假設在應用代碼中動態構建查詢
mysql> SET @search_term = '+Python +analysis'; -- 手動構建布爾模式搜索詞
Query OK, 0 rows affected (0.00 sec)mysql>
mysql> PREPARE stmt FROM ''> SELECT id, name,'> MATCH(col1,col2,col3) AGAINST(? IN BOOLEAN MODE) AS score,'> CONCAT(LEFT(col1, 50), ''...'') AS snippet'> FROM tbl_name'> WHERE MATCH(col1,col2,col3) AGAINST(? IN BOOLEAN MODE)'> ORDER BY score DESC'> LIMIT ?, 10';
Query OK, 0 rows affected (0.01 sec)
Statement preparedmysql>
mysql> SET @page = 0; -- 示例：第一頁
Query OK, 0 rows affected (0.00 sec)mysql> EXECUTE stmt USING @search_term, @search_term, @page;
+----+----------+--------------------+--------------------------------+
| id | name | score | snippet |
+----+----------+--------------------+--------------------------------+
| 6 | Record 2 | 1.2110387086868286 | Python programming language... |
+----+----------+--------------------+--------------------------------+
1 row in set (0.00 sec)
下一步建議
如果要處理大量數據，考慮添加更多測試記錄（1000+條）測試中英文混合內容的搜索效果探索 WITH PARSER ngram 中文分詞（如需支持中文）mysql> show create table tbl_name;
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tbl_name | CREATE TABLE `tbl_name` (`id` int NOT NULL AUTO_INCREMENT,`name` varchar(50) NOT NULL,`set_col` varchar(255) DEFAULT NULL,`val` varchar(100) DEFAULT NULL,`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,`col1` text,`col2` text,`col3` text,PRIMARY KEY (`id`),FULLTEXT KEY `ft_content` (`col1`,`col2`,`col3`),FULLTEXT KEY `col1` (`col1`,`col2`,`col3`),FULLTEXT KEY `col1_2` (`col1`,`col2`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)刪除重復的 FULLTEXT 索引：
mysql> ALTER TABLE tbl_name DROP INDEX col1;
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0使用明確的列名替代省略號：
mysql> SELECT id, name, col1, col2, col3,-> MATCH(col1, col2, col3) AGAINST('search string' IN NATURAL LANGUAGE MODE) AS score-> FROM tbl_name-> WHERE MATCH(col1, col2, col3) AGAINST('search string' IN NATURAL LANGUAGE MODE);
+----+----------+---------------------------+-----------------------+-------------------+--------------------+
| id | name | col1 | col2 | col3 | score |
+----+----------+---------------------------+-----------------------+-------------------+--------------------+
| 5 | Record 1 | MySQL database management | text search functions | fulltext indexing | 0.6055193543434143 |
+----+----------+---------------------------+-----------------------+-------------------+--------------------+
1 row in set (0.00 sec)select ... from tbl_name where match(col1, col2, col3) against('search string');明確列名：用實際列名（id, name, col1, col2, col3）替換了省略號 ...
正確的預處理語句：
使用 PREPARE 正確創建了語句模板
每個 AGAINST() 對應一個參數占位符 ?
參數傳遞：EXECUTE stmt USING @search_term, @search_term 正確傳遞了兩個參數
關鍵總結
錯誤原因：之前的所有錯誤均由 SELECT ... 中的省略號導致語法錯誤
解決方案：必須始終提供明確的列名列表（如 SELECT id, name, col1）預處理語句的正確寫法：
mysql> SET @search_term = 'search string';
Query OK, 0 rows affected (0.00 sec)mysql> PREPARE stmt FROM ''> SELECT id, name, col1, col2, col3,'> MATCH(col1, col2, col3) AGAINST(? IN NATURAL LANGUAGE MODE) AS score'> FROM tbl_name'> WHERE MATCH(col1, col2, col3) AGAINST(? IN NATURAL LANGUAGE MODE)';
Query OK, 0 rows affected (0.00 sec)
Statement preparedmysql>
mysql> EXECUTE stmt USING @search_term, @search_term;
+----+----------+---------------------------+-----------------------+-------------------+--------------------+
| id | name | col1 | col2 | col3 | score |
+----+----------+---------------------------+-----------------------+-------------------+--------------------+
| 5 | Record 1 | MySQL database management | text search functions | fulltext indexing | 0.6055193543434143 |
+----+----------+---------------------------+-----------------------+-------------------+--------------------+
1 row in set (0.00 sec)
