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)
