当前位置： 首页 > news >正文

板凳-------Mysql cookbook学习 （十--3）

news 2025/6/10 5:18:54

5.16 用短语来进行fulltext查询

mysql> select count(*) from kjv where match(vtext) against('God');
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)mysql> select count(*) from kjv where match(vtext) against('sin');
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)mysql> select count(*) as 'total verses',->          count(if (vtext like '%God', 1, null)) as 'verses containing "God"',->          count(if (vtext like '%sin', 1, null)) as 'verses containing "sin"'-> from kjv;
+--------------+-------------------------+-------------------------+
| total verses | verses containing "God" | verses containing "sin" |
+--------------+-------------------------+-------------------------+
|        31102 |                       0 |                       0 |
+--------------+-------------------------+-------------------------+
1 row in set (0.72 sec)mysql> SELECT->     COUNT(*) AS 'total verses',->     COUNT(IF(vtext LIKE '%God%', 1, NULL)) AS 'verses containing "God"',->     COUNT(IF(vtext LIKE '%sin%', 1, NULL)) AS 'verses containing "sin"'-> FROM kjv;
+--------------+-------------------------+-------------------------+
| total verses | verses containing "God" | verses containing "sin" |
+--------------+-------------------------+-------------------------+
|        31102 |                    4118 |                    1292 |
+--------------+-------------------------+-------------------------+
1 row in set (0.69 sec)
•	修改后的查询采用LIKE '%God%'和LIKE '%sin%'。
o	%God%能够匹配包含 "God" 的所有字符串，不管 "God" 是在开头、中间，还是结尾，比如 "God is good" "Good is God" "Thank God" 都能被匹配。
o	%sin%同理，能匹配所有包含 "sin" 的字符串，像 "sin is bad" "bad is sin" "original sin" 都符合要求。
总结
•	错误原因：原查询的条件过于严格，要求目标词汇必须出现在文本末尾，导致大量符合条件的经文被遗漏。
•	解决办法：在搜索词前后都加上%通配符，放宽匹配条件，确保包含目标词汇的所有经文都能被统计到。--修改配置文件/etc/my.cnf 或者D:\software\MySql\Data\my.ini
[mysqld]
ft_min_word_len=3;--重启服务器启用新的设置
mysql> ALTER TABLE kjv ENGINE=InnoDB;
Query OK, 31102 rows affected (3.31 sec)
Records: 31102  Duplicates: 0  Warnings: 0mysql> SHOW VARIABLES LIKE 'ft_min_word_len';  -- 应显示 3
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| ft_min_word_len | 4     |
+-----------------+-------+
1 row in set, 1 warning (0.00 sec)mysql> SELECT count(*) FROM kjv WHERE MATCH(vtext) AGAINST('God');
+----------+
| count(*) |
+----------+
|     3893 |
+----------+
1 row in set (0.01 sec)mysql> select count(*) from kjv where match(vtext) against('sin');
+----------+
| count(*) |
+----------+
|      389 |
+----------+
1 row in set (0.00 sec)
1.	InnoDB 引擎的特殊行为：
o	InnoDB 引擎在某些版本中，即使 ft_min_word_len=4，对于 停用词列表之外 的短词（如 "God"）仍会进行索引。
o	你之前通过 ft_stopword_file="" 禁用了停用词，使得 "God" 能被索引，而 MyISAM 引擎可能更严格地遵循 ft_min_word_len。
2.	数据转换的副作用：
o	ALTER TABLE ... ENGINE=InnoDB 会重建整个表，这个过程可能隐式地重建了符合当前配置的全文索引。

5.17 要求或禁止fulltext搜索单词

mysql> SELECT COUNT(*) FROM kjv WHERE MATCH(vtext) AGAINST('David Goliath');
+----------+
| COUNT(*) |
+----------+
|      972 |
+----------+
1 row in set (0.00 sec)mysql> SELECT COUNT(*) FROM kjv WHERE MATCH(vtext) AGAINST('David')-> and match(vtext) against('Goliath');
+----------+
| COUNT(*) |
+----------+
|        2 |
+----------+
1 row in set (0.01 sec)mysql> SELECT COUNT(*) FROM kjv WHERE MATCH(vtext) AGAINST('David  +Goliath' in boolean mode);
+----------+
| COUNT(*) |
+----------+
|        6 |
+----------+
1 row in set (0.00 sec)mysql> SELECT COUNT(*) FROM kjv WHERE MATCH(vtext) AGAINST('+David  -Goliath' in boolean mode);
+----------+
| COUNT(*) |
+----------+
|      966 |
+----------+
1 row in set (0.00 sec)mysql> SELECT COUNT(*) FROM kjv WHERE MATCH(vtext) AGAINST('-David  +Goliath' in boolean mode);
+----------+
| COUNT(*) |
+----------+
|        4 |
+----------+
1 row in set (0.00 sec)mysql> SELECT COUNT(*) FROM kjv WHERE MATCH(vtext) AGAINST('Whirl*' in boolean mode);
+----------+
| COUNT(*) |
+----------+
|       28 |
+----------+
1 row in set (0.00 sec)5.18 用fulltext索引来执行词组查询mysql> select count(*) from kjv where match(vtext) against('still small voice');
+----------+
| count(*) |
+----------+
|      640 |
+----------+
1 row in set (0.00 sec)mysql> select count(*) from kjv where match(vtext) against('still small voice' in boolean mode);
+----------+
| count(*) |
+----------+
|      640 |
+----------+
1 row in set (0.00 sec)mysql> select count(*) from kjv where match(vtext) against('"still small voice"' in boolean mode);
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

第6章：使用日期和时间
6.0 引言
6.1 选择合适的日期或者时间变量类型

--创建表、初始化数据
DROP TABLE IF EXISTS date_val;
CREATE TABLE date_val
(d DATE
);INSERT INTO date_val (d) VALUES('1864-02-28');
INSERT INTO date_val (d) VALUES('1900-01-15');
INSERT INTO date_val (d) VALUES('1987-03-05');
INSERT INTO date_val (d) VALUES('1999-12-31');
INSERT INTO date_val (d) VALUES('2000-06-04');DROP TABLE IF EXISTS datetime_val;
CREATE TABLE datetime_val
(dt  DATETIME
);INSERT INTO datetime_val (dt) VALUES('1970-01-01 00:00:00');
INSERT INTO datetime_val (dt) VALUES('1987-03-05 12:30:15');
INSERT INTO datetime_val (dt) VALUES('1999-12-31 09:00:00');
INSERT INTO datetime_val (dt) VALUES('2000-06-04 15:45:30');DROP TABLE IF EXISTS time_val;
CREATE TABLE time_val
(t1  TIME,t2  TIME
);INSERT INTO time_val (t1,t2) VALUES('15:00:00','15:00:00');
INSERT INTO time_val (t1,t2) VALUES('05:01:30','02:30:20');
INSERT INTO time_val (t1,t2) VALUES('12:30:20','17:30:45');DROP TABLE IF EXISTS time_val;
CREATE TABLE time_val
(t1  TIME,t2  TIME
);INSERT INTO time_val (t1,t2) VALUES('15:00:00','15:00:00');
INSERT INTO time_val (t1,t2) VALUES('05:01:30','02:30:20');
INSERT INTO time_val (t1,t2) VALUES('12:30:20','17:30:45');DROP TABLE IF EXISTS timestamp_val;
CREATE TABLE timestamp_val
(ts  TIMESTAMP
);INSERT INTO timestamp_val (ts) VALUES('1970-01-01 09:00:00');
INSERT INTO timestamp_val (ts) VALUES('1987-03-05 12:30:15');
INSERT INTO timestamp_val (ts) VALUES('1999-12-31 09:00:00');
INSERT INTO timestamp_val (ts) VALUES('2000-06-04 15:45:30');
--查看数据mysql> select * from date_val;
+------------+
| d          |
+------------+
| 1864-02-28 |
| 1900-01-15 |
| 1999-12-31 |
| 2000-06-04 |
| 2017-03-16 |
| 1864-02-28 |
| 1900-01-15 |
| 1999-12-31 |
| 2000-06-04 |
| 2017-03-16 |
+------------+
10 rows in set (0.00 sec)mysql> select * from datetime_val;
+---------------------+
| dt                  |
+---------------------+
| 1970-01-01 00:00:00 |
| 1999-12-31 09:00:00 |
| 2000-06-04 15:45:30 |
| 2017-03-16 12:30:15 |
| 1970-01-01 00:00:00 |
| 1999-12-31 09:00:00 |
| 2000-06-04 15:45:30 |
| 2017-03-16 12:30:15 |
+---------------------+
8 rows in set (0.00 sec)mysql> select * from time_val;
+----+----------+----------+
| id | t1       | t2       |
+----+----------+----------+
|  1 | 15:00:00 | 15:00:00 |
|  2 | 05:01:30 | 02:30:20 |
|  3 | 12:30:20 | 17:30:45 |
+----+----------+----------+
3 rows in set (0.00 sec)mysql> select * from timestamp_val;
+---------------------+
| ts                  |
+---------------------+
| 1970-01-01 09:00:00 |
| 1987-03-05 12:30:15 |
| 1999-12-31 09:00:00 |
| 2000-06-04 15:45:30 |
+---------------------+
4 rows in set (0.00 sec)

6.2 修改mysql中的日期格式

mysql> -- 假设你想将日期存入日期类型列
mysql> ALTER TABLE t ADD d DATE;  -- 添加日期列
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> INSERT INTO t (d) VALUES (STR_TO_DATE('May 13, 2007', '%M %d, %Y'));
Query OK, 1 row affected (0.01 sec)mysql> select d from t;
+------------+
| d          |
+------------+
| NULL       |
| NULL       |
| NULL       |
| NULL       |
| 2007-05-13 |
+------------+
5 rows in set (0.00 sec)mysql> insert into date_val (d) values(str_to_date('May 13, 2007', '%M %d, %Y'));
Query OK, 1 row affected (0.01 sec)mysql> select * from date_val;
+------------+
| d          |
+------------+
| 1864-02-28 |
| 1900-01-15 |
| 1999-12-31 |
| 2000-06-04 |
| 2017-03-16 |
| 1864-02-28 |
| 1900-01-15 |
| 1999-12-31 |
| 2000-06-04 |
| 2017-03-16 |
| 2007-05-13 |
+------------+
11 rows in set (0.00 sec)mysql> select d, date_format(d, '%M %d, %Y') from date_val;
+------------+-----------------------------+
| d          | date_format(d, '%M %d, %Y') |
+------------+-----------------------------+
| 1864-02-28 | February 28, 1864           |
| 1900-01-15 | January 15, 1900            |
| 1999-12-31 | December 31, 1999           |
| 2000-06-04 | June 04, 2000               |
| 2017-03-16 | March 16, 2017              |
| 1864-02-28 | February 28, 1864           |
| 1900-01-15 | January 15, 1900            |
| 1999-12-31 | December 31, 1999           |
| 2000-06-04 | June 04, 2000               |
| 2017-03-16 | March 16, 2017              |
| 2007-05-13 | May 13, 2007                |
+------------+-----------------------------+
11 rows in set (0.01 sec)mysql> select d, date_format(d, '%M %d, %Y') as date from date_val;
+------------+-------------------+
| d          | date              |
+------------+-------------------+
| 1864-02-28 | February 28, 1864 |
| 1900-01-15 | January 15, 1900  |
| 1999-12-31 | December 31, 1999 |
| 2000-06-04 | June 04, 2000     |
| 2017-03-16 | March 16, 2017    |
| 1864-02-28 | February 28, 1864 |
| 1900-01-15 | January 15, 1900  |
| 1999-12-31 | December 31, 1999 |
| 2000-06-04 | June 04, 2000     |
| 2017-03-16 | March 16, 2017    |
| 2007-05-13 | May 13, 2007      |
+------------+-------------------+
11 rows in set (0.00 sec)--格式化串定义
格式化字符                含义
%Y                                年份，数字形式，4位数
%y                                年份，数字形式，2位数
%M                                完整度月份名称（Hanuary-December）
%b                                月份名称的前三个字母（Jan-Dec）
%m                                月份，数字形式（01..12）
%c                                月份，数字形式(1..12)
%d                                该月日期，数字形式（01..31）
%e                                该月日期，数字形式（1..31）
%W                                工作日名称（Sunday..Saturday）
%r                                时间，12小时制，以AM或PM结尾
%T                                时间，24小时制
%H                                小时，数字形式，2位数（00..23）
%i                                分钟，数字形式，2位数（00..59）
%s                                秒，数字形式，2位数（00..59）
%%                                '%'文字字符mysql> select dt, 
date_format(dt, '%c/%e/%y %r') as format1,
date_format(dt, '%M %e, %Y %T') as format2 
from datetime_val;
+---------------------+----------------------+----------------------------+
| dt                  | format1              | format2                    |
+---------------------+----------------------+----------------------------+
| 1970-01-01 00:00:00 | 1/1/70 12:00:00 AM   | January 1, 1970 00:00:00   |
| 1999-12-31 09:00:00 | 12/31/99 09:00:00 AM | December 31, 1999 09:00:00 |
| 2000-06-04 15:45:30 | 6/4/00 03:45:30 PM   | June 4, 2000 15:45:30      |
| 2017-03-16 12:30:15 | 3/16/17 12:30:15 PM  | March 16, 2017 12:30:15    |
| 1970-01-01 00:00:00 | 1/1/70 12:00:00 AM   | January 1, 1970 00:00:00   |
| 1999-12-31 09:00:00 | 12/31/99 09:00:00 AM | December 31, 1999 09:00:00 |
| 2000-06-04 15:45:30 | 6/4/00 03:45:30 PM   | June 4, 2000 15:45:30      |
| 2017-03-16 12:30:15 | 3/16/17 12:30:15 PM  | March 16, 2017 12:30:15    |
+---------------------+----------------------+----------------------------+
8 rows in set (0.00 sec)mysql> select dt, 
time_format(dt, '%r') as '12-hour time', 
time_format(dt, '%T') as '24-hour time'
from datetime_val;
+---------------------+--------------+--------------+
| dt                  | 12-hour time | 24-hour time |
+---------------------+--------------+--------------+
| 1970-01-01 00:00:00 | 12:00:00 AM  | 00:00:00     |
| 1999-12-31 09:00:00 | 09:00:00 AM  | 09:00:00     |
| 2000-06-04 15:45:30 | 03:45:30 PM  | 15:45:30     |
| 2017-03-16 12:30:15 | 12:30:15 PM  | 12:30:15     |
| 1970-01-01 00:00:00 | 12:00:00 AM  | 00:00:00     |
| 1999-12-31 09:00:00 | 09:00:00 AM  | 09:00:00     |
| 2000-06-04 15:45:30 | 03:45:30 PM  | 15:45:30     |
| 2017-03-16 12:30:15 | 12:30:15 PM  | 12:30:15     |
+---------------------+--------------+--------------+
8 rows in set (0.00 sec)mysql> DELIMITER //
mysql>
mysql> CREATE FUNCTION time_ampm(t TIME)-> RETURNS VARCHAR(13)-> DETERMINISTIC-> BEGIN->     DECLARE ampm CHAR(4);->     IF TIME_TO_SEC(t) < 12*60*60 THEN->         SET ampm = 'a.m.';->     ELSE->         SET ampm = 'p.m.';->     END IF;->->     -- Handle midnight (12:00:00 a.m.) and noon (12:00:00 p.m.) cases->     IF HOUR(t) = 12 THEN->         SET ampm = 'p.m.';->     ELSEIF HOUR(t) = 0 THEN->         SET ampm = 'a.m.';->     END IF;->->     -- Use %h:%i:%s format for hours:minutes:seconds, then add am/pm->     RETURN CONCAT(->         TIME_FORMAT(t, '%h:%i:%s'),->         ' ',->         ampm->     );-> END //
Query OK, 0 rows affected (0.02 sec)mysql> SELECT time_ampm('08:30:00');  -- Returns "08:30:00 a.m."
+-----------------------+
| time_ampm('08:30:00') |
+-----------------------+
| 08:30:00 a.m.         |
+-----------------------+
1 row in set (0.01 sec)mysql> SELECT time_ampm('13:45:00');  -- Returns "01:45:00 p.m."
+-----------------------+
| time_ampm('13:45:00') |
+-----------------------+
| 01:45:00 p.m.         |
+-----------------------+
1 row in set (0.00 sec)mysql> SELECT time_ampm('00:00:00');  -- Returns "12:00:00 a.m."
+-----------------------+
| time_ampm('00:00:00') |
+-----------------------+
| 12:00:00 a.m.         |
+-----------------------+
1 row in set (0.00 sec)mysql> SELECT time_ampm('12:00:00');  -- Returns "12:00:00 p.m."
+-----------------------+
| time_ampm('12:00:00') |
+-----------------------+
| 12:00:00 p.m.         |
+-----------------------+
1 row in set (0.00 sec)mysql> select t1, time_ampm(t1) from time_val;
+----------+---------------+
| t1       | time_ampm(t1) |
+----------+---------------+
| 15:00:00 | 03:00:00 p.m. |
| 05:01:30 | 05:01:30 a.m. |
| 12:30:20 | 12:30:20 p.m. |
+----------+---------------+
3 rows in set (0.00 sec)
http://www.xdnf.cn/news/945361.html

相关文章：

  • Tomcat Jetty 和 UnderTow 的粗略对比
  • 企业级AI-DevOps工具链的构成及实现方案
  • 借助 LangGraph 对 Agent 进行测试
  • Redis实战-黑马点评项目完结（p78-p95）
  • 解决网页导出PDF部分内容被遮挡问题
  • 性能优化中，多面体模型基本原理
  • 使用centos服务器和Let‘s Encypted配置SpingBoot项目的https证书
  • JVM如何优化
  • 深度解析 Linux 内核参数 net.ipv4.tcp_rmem：优化网络性能的关键
  • DeFi模式：去中心化金融架构与流动性池设计
  • Dify 社区版本地docker compose部署记录
  • Docker环境下安装 Elasticsearch + IK 分词器 + Pinyin插件 + Kibana（适配7.10.1）
  • uni-app学习笔记三十五--扩展组件的安装和使用
  • A2A JS SDK 完整教程：快速入门指南
  • Linux线程互斥与竞态条件解析
  • LeetCode Hot100刷题——三数之和
  • 2025企业级采购系统深度评测：AI技术如何助力采购成本直降40%？
  • Python训练营-Day26-函数专题1：函数定义与参数
  • 从实验室到产业：IndexTTS 在六大核心场景的落地实践
  • 影子栈指针是什么？
  • 原型模式深度解析：Java设计模式实战指南与克隆机制优化实践
  • 一种使用 PowerToys 的键盘管理器工具重新映射按键实现在 Windows 上快捷输入字符的方式
  • 在Spring Boot中集成RabbitMQ的完整指南
  • vue3+vite+pnpm项目 使用monaco-editor常见问题
  • 数据结构篇--分离链表vs线性探测
  • Redis的发布订阅模式与专业的 MQ（如 Kafka, RabbitMQ）相比，优缺点是什么？适用于哪些场景？
  • laravel8+vue3.0+element-plus搭建方法
  • Hugging Face、魔塔社区(MOTA)与OpenRouter：AI模型平台深度对比与实战指南
  • (七) 深度学习进阶：现代卷积神经网络技术解析与应用实践
  • ＜STC32G12K128入门第十九步＞QT串口ISP更新上位机