板凳-------Mysql cookbook学习 （十--2）
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)
相关文章：
板凳-------Mysql cookbook学习 （十--2）
5.12 模式匹配中的大小写问题 mysql> use cookbook Database changed mysql> select a like A, a regexp A; ------------------------------ | a like A | a regexp A | ------------------------------ | 1 | 1 | --------------------------…...
设计模式域——软件设计模式全集
摘要 软件设计模式是软件工程领域中经过验证的、可复用的解决方案，旨在解决常见的软件设计问题。它们是软件开发经验的总结，能够帮助开发人员在设计阶段快速找到合适的解决方案，提高代码的可维护性、可扩展性和可复用性。设计模式主要分为三…...
FTPS、HTTPS、SMTPS以及WebSockets over TLS的概念及其应用场景
一、什么是FTPS？ FTPS，英文全称File Transfer Protocol with support for Transport Layer Security (SSL/TLS)，安全文件传输协议，是一种对常用的文件传输协议(FTP)添加传输层安全(TLS)和安全套接层(SSL)加密协议支持的扩展协议。…...
--uboot系统之外设与PMIC详解)
RK3568项目(七)--uboot系统之外设与PMIC详解
目录 一、引言 二、按键 ------>2.1、按键种类 ------------>2.1.1、RESET ------------>2.1.2、UPDATE ------------>2.1.3、PWRON 部分 ------------>2.1.4、RK809 PMIC ------------>2.1.5、ADC按键 ------------>2.1.6、ADC按键驱动 ------…...
Three.js进阶之粒子系统（一）
一些特定模糊现象，经常使用粒子系统模拟，如火焰、爆炸等。Three.js提供了多种粒子系统，下面介绍粒子系统 一、Sprite粒子系统 使用场景：下雨、下雪、烟花 ce使用代码： var materialnew THRESS.SpriteMaterial();//…...
【仿生机器人】刀剑神域——爱丽丝苏醒计划，需求文档
仿生机器人"爱丽丝"系统架构设计需求文档 一、硬件基础 已完成头部和颈部硬件搭建 25个舵机驱动表情系统 颈部旋转功能 眼部摄像头（视觉输入） 麦克风阵列（听觉输入） 颈部发声装置（语音输出）…...
小白的进阶之路系列之十四----人工智能从初步到精通pytorch综合运用的讲解第七部分
通过示例学习PyTorch 本教程通过独立的示例介绍PyTorch的基本概念。 PyTorch的核心提供了两个主要特性： 一个n维张量，类似于numpy，但可以在gpu上运行 用于构建和训练神经网络的自动微分 我们将使用一个三阶多项式来拟合问题 y = s i n ( x ) y=sin(x) y=sin(x)，作为我们的…...
JavaScript性能优化实战大纲
性能优化的核心目标 降低页面加载时间，减少内存占用，提高代码执行效率，确保流畅的用户体验。 代码层面的优化 减少全局变量使用，避免内存泄漏 // 不好的实践 var globalVar I am global;// 好的实践 (function() {var localV…...
Python 解释器安装全攻略（适用于 Linux / Windows / macOS）
目录 一、Windows安装Python解释器1.1 下载并安装Python解释1.2 测试安装是否成功1.3 设置pip的国内镜像------永久配置 二、macOS安装Python解释器三、Linux下安装Python解释器3.1 Rocky8.10/Rocky9.5安装Python解释器3.2 Ubuntu2204/Ubuntu2404安装Python解释器3.3 设置pip的…...
Java多线程从入门到精通
一、基础概念 1.1 进程与线程 进程是指运行中的程序。 比如我们使用浏览器，需要启动这个程序，操作系统会给这个程序分配一定的资源（占用内存资源）。 线程是CPU调度的基本单位，每个线程执行的都是某一个进程的代码的某…...
【芯片仿真中的X值：隐藏的陷阱与应对之道】
在芯片设计的世界里，X值（不定态）就像一个潜伏的幽灵。它可能让仿真测试顺利通过，却在芯片流片后引发灾难性后果。本文将揭开X值的本质，探讨其危害，并分享高效调试与预防的实战经验。 一、X值的本质与致…...
C++ 变量和基本类型
1、变量的声明和定义 1.1、变量声明规定了变量的类型和名字。定义初次之外，还申请存储空间，也可能会为变量赋一个初始值。 如果想声明一个变量而非定义它，就在变量名前添加关键字extern，而且不要显式地初始化变量： e…...
LeetCode第244题_最短单词距离II
LeetCode第244题：最短单词距离II 问题描述 设计一个类，接收一个单词数组 wordsDict，并实现一个方法，该方法能够计算两个不同单词在该数组中出现位置的最短距离。 你需要实现一个 WordDistance 类: WordDistance(String[] word…...
Linux 中替换文件中的某个字符串
如果你想在 Linux 中替换文件中的某个字符串，可以使用以下命令： 1. 基本替换（sed 命令） sed -i s/原字符串/新字符串/g 文件名示例：将 file.txt 中所有的 old_text 替换成 new_text sed -i s/old_text/new_text/g fi…...
python3GUI--基于PyQt5+DeepSort+YOLOv8智能人员入侵检测系统（详细图文介绍）
文章目录 一．前言二．技术介绍1.PyQt52.DeepSort3.卡尔曼滤波4.YOLOv85.SQLite36.多线程7.入侵人员检测8.ROI区域 三．核心功能1.登录注册1.登录2.注册 2.主界面1.主界面简介2.数据输入3.参数配置4.告警配置5.操作控制台6.核心内容显示区域7.检…...
5. TypeScript 类型缩小
在 TypeScript 中，类型缩小（Narrowing）是指根据特定条件将变量的类型细化为更具体的过程。它帮助开发者编写更精确、更准确的代码，确保变量在运行时只以符合其类型的方式进行处理。 一、instanceof 缩小类型 TypeScript 中的 in…...
Python_day48随机函数与广播机制
在继续讲解模块消融前，先补充几个之前没提的基础概念 尤其需要搞懂张量的维度、以及计算后的维度，这对于你未来理解复杂的网络至关重要 一、 随机张量的生成 在深度学习中经常需要随机生成一些张量，比如权重的初始化，或者计算输入…...
【QT】qtdesigner中将控件提升为自定义控件后，css设置样式不生效（已解决，图文详情）
目录 0.背景 1.解决思路 2.详细代码 0.背景 实际项目中遇到的问题，描述如下： 我在qtdesigner用界面拖了一个QTableView控件，object name为【tableView_electrode】，然后【提升为】了自定义的类【Steer_Electrode_Table】&…...
【Docker 02】Docker 安装
🌈 一、各版本的平台支持情况 ⭐ 1. Server 版本 Server 版本的 Docker 就只有个命令行，没有界面。 Platformx86_64 / amd64arm64 / aarch64arm(32 - bit)s390xCentOs√√Debian√√√Fedora√√Raspbian√RHEL√SLES√Ubuntu√√√√Binaries√√√ …...
【大厂机试题+算法可视化】最长的指定瑕疵度的元音子串
题目 开头和结尾都是元音字母（aeiouAEIOU）的字符串为元音字符串，其中混杂的非元音字母数量为其瑕疵度。比如: “a” 、 “aa”是元音字符串，其瑕疵度都为0 “aiur”不是元音字符串（结尾不是元音字符） “…...
【免杀】C2免杀技术（十五）shellcode混淆uuid/ipv6/mac
针对 shellcode 混淆（Shellcode Obfuscation） 的实战手段还有很多，如下表所示： 类型举例目的编码 / 加密XOR、AES、RC4、Base64、Poly1305、UUID、IP/MAC改变字节特征，避开静态签名或 YARA结构伪装PE Stub、GIF/PNG 嵌入、RTF OLE、UUID、IP/MAC看起来像合法文件/数据，弱…...
Java严格模式withResolverStyle解析日期错误及解决方案
在Java中使用DateTimeFormatter并启用严格模式（ResolverStyle.STRICT）时，解析日期字符串"2025-06-01"报错的根本原因是：模式字符串中的年份格式yyyy被解释为YearOfEra（纪元年份），而非…...
Async-profiler 内存采样机制解析：从原理到实现
引言 在 Java 性能调优的工具箱中，async-profiler 是一款备受青睐的低开销采样分析器。它不仅能分析 CPU 热点，还能精确追踪内存分配情况。本文将深入探讨 async-profiler 实现内存采样的多种机制，结合代码示例解析其工作原理。 为什么需要内…...
C++ 使用 ffmpeg 解码 rtsp 流并获取每帧的YUV数据
一、简介 FFmpeg 是一个开源的多媒体处理框架，非常适用于处理音视频的录制、转换、流化和播放。 二、代码 示例代码使用工作线程读取rtsp视频流，自动重连，支持手动退出，解码并将二进制文件保存下来。 注意： 代…...
Java毕业设计：办公自动化系统的设计与实现
JAVA办公自动化系统 一、系统概述 本办公自动化系统基于Java EE平台开发，实现了企业日常办公的数字化管理。系统包含文档管理、流程审批、会议管理、日程安排、通讯录等核心功能模块，采用B/S架构设计，支持多用户协同工作。系统使用Spring B…...
论文笔记：Large Language Models for Next Point-of-Interest Recommendation
SIGIR 2024 1 intro 传统的基于数值的POI推荐方法在处理上下文信息时存在两个主要限制 需要将异构的LBSN数据转换为数字，这可能导致上下文信息的固有含义丢失仅依赖于统计和人为设计来理解上下文信息，缺乏对上下文信息提供的语义概念的理解 ——>使用…...
LeetCode 2894.分类求和并作差
目录 题目： 题目描述： 题目链接： 思路： 思路一详解（遍历 判断）： 思路二详解（数学规律/公式）： 代码： Java思路一（遍历 判断&a…...
n8n：解锁自动化工作流的无限可能
在当今快节奏的数字时代，无论是企业还是个人，都渴望提高工作效率，减少重复性任务的繁琐操作。而 n8n，这个强大的开源自动化工具，就像一位智能的数字助手，悄然走进了许多人的工作和生活，成为提升…...
链结构与工作量证明7️⃣：用 Go 实现比特币的核心机制
链结构与工作量证明：用 Go 实现比特币的核心机制 如果你用 Go 写过区块、算过哈希，也大致理解了非对称加密、数据序列化这些“硬核知识”，那么恭喜你，现在我们终于可以把这些拼成一条完整的“区块链”。 不过别急，这一节我们重点搞懂两件事： 区块之间是怎么连接成“链”…...
CMake系统学习笔记
CMake系统学习笔记 基础操作 最基本的案例 // code #include <iostream>int main() {std::cout << "hello world " << std::endl;return 0; }// CMakeLists.txt cmake_minimum_required(VERSION 3.0)# 定义当前工程名称 project(demo)add_execu…...