我要评论sql中日期的特殊性总结
在sql中，日期是一种特殊的数据类型，既有数值的特性，又有字符串的表现形式，使用时有诸多需要注意的地方。
一、日期数据类型的特点
| 特性 | 说明 | 示例 |
|---|---|---|
| 存储格式 | 内部存储为数字（从某个基准日期开始的天数/秒数） | oracle: 4712-01-01 起的天数 |
| 显示格式 | 由数据库参数控制，不一定是输入时的格式 | oracle: 17-12月-80 |
| 运算能力 | 支持加减运算（天数/月数/年数） | hiredate + 30（30天后） |
| 比较能力 | 支持 <, >, =, between 等比较操作 | hiredate > to_date('1981-01-01') |
二、日期与字符串的转换（最重要）
核心函数
| 函数 | 方向 | 用途 |
|---|---|---|
to_date(字符串, 格式) | 字符串 → 日期 | 将字符串按指定格式解析为日期类型 |
to_char(日期, 格式) | 日期 → 字符串 | 将日期按指定格式转换为字符串 |
常用日期格式元素
| 格式符 | 含义 | 示例 |
|---|---|---|
| yyyy | 四位年份 | 1981 |
| yy | 两位年份 | 81 |
| mm | 两位月份 | 05 |
| mon | 月份缩写（中文环境为'5月'） | '5月' |
| month | 月份全称 | '5月' |
| dd | 两位日期 | 01 |
| day | 星期几 | '星期三' |
| hh24 | 24小时制 | 14 |
| mi | 分钟 | 30 |
| ss | 秒钟 | 45 |
在sql中，日期格式符是区分大小写的，这是一个非常重要的细节，写错了会导致转换失败或结果错误。
核心规则：格式符严格区分大小写
| 格式符 | 含义 | 正确示例 | 错误示例（大小写错误） |
|---|---|---|---|
mm | 月份（01-12） | to_char(date, 'mm') → 04 | mm → ❌ 报错或无效 |
mi | 分钟（00-59） | to_char(date, 'mi') → 30 | mi / mi → ❌ |
hh24 | 24小时制（00-23） | to_char(date, 'hh24') → 14 | hh24 → ❌ |
hh12 / hh | 12小时制（01-12） | to_char(date, 'hh12') → 02 | hh12 → ❌ |
yyyy | 四位年份 | to_char(date, 'yyyy') → 2026 | yyyy → ❌ |
yy | 两位年份 | to_char(date, 'yy') → 26 | yy → ❌ |
mon | 月份缩写（如'4月'） | to_char(date, 'mon') → 4月 | mon / mon → ❌ |
month | 月份全称（如'4月'） | to_char(date, 'month') → 4月 | month → ❌ |
dd | 日期（01-31） | to_char(date, 'dd') → 23 | dd → ❌ |
dy | 星期缩写（如'周三'） | to_char(date, 'dy') → 周三 | dy → ❌ |
day | 星期全称（如'星期三'） | to_char(date, 'day') → 星期三 | day → ❌ |
示例代码
-- to_date：字符串转日期
to_date('1981-05-01', 'yyyy-mm-dd') -- 返回日期：1981年5月1日
to_date('19810501', 'yyyymmdd') -- 返回日期：1981年5月1日
to_date('1981-05', 'yyyy-mm') -- 返回日期：1981年5月1日（默认当月1号）
-- to_char：日期转字符串
to_char(hiredate, 'yyyy-mm-dd') -- '1981-05-01'
to_char(hiredate, 'yyyymm') -- '198105'
to_char(hiredate, 'mon dd, yyyy') -- '5月 01, 1981'三、日期比较的特殊性
1. 不能直接用字符串比较日期
-- ❌ 错误：字符串 '1981' 和日期类型不能直接比较
select * from emp where hiredate = '1981';
-- ✅ 正确方式1：转换日期为字符串比较
select * from emp where to_char(hiredate, 'yyyy') = '1981';
-- ✅ 正确方式2：字符串转日期比较
select * from emp where hiredate >= to_date('1981-01-01', 'yyyy-mm-dd')
and hiredate < to_date('1982-01-01', 'yyyy-mm-dd');2. 日期比较的边界问题（重要⚠️）
-- 查询1981年入职的员工（错误写法）
select * from emp
where to_char(hiredate, 'yyyy') = 1981; -- ✅ 可行，但效率低
-- 查询1981年入职的员工（正确写法 - 使用范围）
select * from emp
where hiredate >= to_date('1981-01-01', 'yyyy-mm-dd')
and hiredate < to_date('1982-01-01', 'yyyy-mm-dd');
-- 查询1981年5月入职（错误写法）
where hiredate between to_date('1981-05-01', 'yyyy-mm-dd')
and to_date('1981-05-31', 'yyyy-mm-dd'); -- ⚠️ 漏掉了5月31日23:59:59之后的数据
-- 查询1981年5月入职（正确写法）
where hiredate >= to_date('1981-05-01', 'yyyy-mm-dd')
and hiredate < to_date('1981-06-01', 'yyyy-mm-dd');边界写法参考
| 需求 | to_char 写法 | to_date 范围写法 |
|---|---|---|
| 年份 = 1981 | to_char(hiredate,'yyyy') = 1981 | hiredate >= to_date('1981-01-01','yyyy-mm-dd') and hiredate < to_date('1982-01-01','yyyy-mm-dd') |
| 年份 < 1982 | to_char(hiredate,'yyyy') < 1982 | hiredate < to_date('1982-01-01','yyyy-mm-dd') |
| 年份 <= 1982 | to_char(hiredate,'yyyy') <= 1982 | hiredate < to_date('1983-01-01','yyyy-mm-dd') |
| 年份 > 1981 | to_char(hiredate,'yyyy') > 1981 | hiredate >= to_date('1982-01-01','yyyy-mm-dd') |
| 年份 >= 1982 | to_char(hiredate,'yyyy') >= 1982 | hiredate >= to_date('1982-01-01','yyyy-mm-dd') |
四、日期的加减运算
| 运算 | 含义 | 示例 |
|---|---|---|
日期 + 数字 | 增加天数 | hiredate + 30（30天后） |
日期 - 数字 | 减少天数 | hiredate - 7（7天前） |
日期1 - 日期2 | 相差天数 | sysdate - hiredate（入职天数） |
add_months(日期, 数字) | 增加月份 | add_months(hiredate, 6)（6个月后） |
months_between(日期1, 日期2) | 相差月数 | months_between(sysdate, hiredate) |
示例代码
-- 计算员工入职天数 select ename, sysdate - hiredate as 工作天数 from emp; -- 计算员工入职月数 select ename, months_between(sysdate, hiredate) as 工作月数 from emp; -- 查询入职超过30年的员工 select * from emp where add_months(hiredate, 30*12) < sysdate;
五、日期函数对比（oracle vs mysql）
| 功能 | oracle | mysql |
|---|---|---|
| 当前日期时间 | sysdate | now() / curdate() |
| 提取年份 | to_char(date, 'yyyy') | year(date) |
| 提取月份 | to_char(date, 'mm') | month(date) |
| 日期加减天数 | date + 10 | date_add(date, interval 10 day) |
| 日期差（天数） | date1 - date2 | datediff(date1, date2) |
| 增加月份 | add_months(date, 6) | date_add(date, interval 6 month) |
六、常见陷阱与最佳实践
❌ 常见错误
-- 1. 直接比较字符串和日期 where hiredate = '1981-05-01' -- 隐式转换可能失败 -- 2. 使用 between 包含结束日期（会丢失当天23:59:59后的数据） where hiredate between '1981-05-01' and '1981-05-31' -- 3. to_char 写在 where 条件的左边（无法使用索引） where to_char(hiredate, 'yyyy') = '1981' -- 4. 忽略时区问题 where create_time = '2026-04-23' -- 可能漏掉带时分秒的记录
✅ 最佳实践
-- 1. 始终使用显式转换
where hiredate >= to_date('1981-05-01', 'yyyy-mm-dd')
and hiredate < to_date('1981-06-01', 'yyyy-mm-dd')
-- 2. 范围查询使用左闭右开区间
where hiredate >= trunc(sysdate - 30) -- 30天前零点
and hiredate < trunc(sysdate) -- 今天零点
-- 3. 让函数作用在常量上，保持索引有效
where hiredate >= to_date('1981-01-01', 'yyyy-mm-dd') -- ✅ 索引有效
where to_char(hiredate, 'yyyy') = '1981' -- ❌ 索引失效
-- 4. 使用 trunc 去掉时间部分
where trunc(hiredate) = to_date('1981-05-01', 'yyyy-mm-dd')使用 trunc 去掉时间部分是什么意思
trunc 是一个用于截断日期或数字的函数。在日期处理中，"去掉时间部分"是指将日期中的时、分、秒清零，只保留年、月、日。
为什么需要"去掉时间部分"？问题场景：日期比较的陷阱
-- 假设表中有一条记录，hiredate = 1981-05-01 14:30:00
-- ❌ 错误：这条记录会被漏掉！
select * from emp
where hiredate = to_date('1981-05-01', 'yyyy-mm-dd');
-- 因为左边有 14:30:00，右边是 00:00:00，不相等
-- ✅ 解法1：使用 trunc 去掉时间部分
select * from emp
where trunc(hiredate) = to_date('1981-05-01', 'yyyy-mm-dd');
-- ✅ 解法2：使用范围查询（更推荐，索引友好）
select * from emp
where hiredate >= to_date('1981-05-01', 'yyyy-mm-dd')
and hiredate < to_date('1981-05-02', 'yyyy-mm-dd');trunc 的常用格式
| 用法 | 结果 | 说明 |
|---|---|---|
trunc(sysdate) | 2026-04-24 00:00:00 | 截断到当天开始（默认） |
trunc(sysdate, 'dd') | 2026-04-24 00:00:00 | 同上，dd表示天 |
trunc(sysdate, 'mm') | 2026-04-01 00:00:00 | 截断到当月第一天 |
trunc(sysdate, 'q') | 2026-04-01 00:00:00 | 截断到当季第一天 |
trunc(sysdate, 'yyyy') | 2026-01-01 00:00:00 | 截断到当年第一天 |
trunc(sysdate, 'hh24') | 2026-04-24 14:00:00 | 截断到当前小时开始 |
对比总结
| 函数 | 作用 | 示例输入 | 示例输出 |
|---|---|---|---|
trunc(date) | 去掉时间部分（归零） | 2026-04-24 14:35:28 | 2026-04-24 00:00:00 |
to_char(date, 'yyyy-mm-dd') | 转为字符串（丢失时间） | 2026-04-24 14:35:28 | '2026-04-24' |
round(date) | 四舍五入到天 | 2026-04-24 14:35:28 | 2026-04-25 00:00:00 |
trunc 会让索引失效（类似 to_char）
-- ❌ 索引失效
where trunc(hiredate) = to_date('1981-05-01', 'yyyy-mm-dd')
-- ✅ 推荐：范围查询（索引有效）
where hiredate >= to_date('1981-05-01', 'yyyy-mm-dd')
and hiredate < to_date('1981-05-02', 'yyyy-mm-dd')原则：能不用 trunc 在 where 条件中就不用，除非数据量很小或没有时间精度要求。
trunc 常用于 group by 分组
-- 按天统计（即使数据库存了时分秒） select trunc(hiredate) as 入职日期, count(*) as 人数 from emp group by trunc(hiredate);
一句话总结
trunc去掉时间部分 = 把14:35:28变成00:00:00
用于忽略时分秒的干扰，让日期比较只看年月日。
但在where中要小心使用，因为它和to_char一样会让索引失效，大数据量时建议用范围查询替代。
七、快速参考卡片
| 需求 | sql写法 |
|---|---|
| 当前系统日期 | sysdate（oracle）/ curdate()（mysql） |
| 年月日格式 | 'yyyy-mm-dd' |
| 字符串→日期 | to_date('1981-05-01', 'yyyy-mm-dd') |
| 日期→字符串 | to_char(hiredate, 'yyyy-mm-dd') |
| 提取年份 | to_char(hiredate, 'yyyy') |
| 提取年月 | to_char(hiredate, 'yyyymm') |
| 某月第一天 | trunc(hiredate, 'mm') |
| 某年第一天 | trunc(hiredate, 'yyyy') |
| 月底最后一天 | last_day(hiredate) |
| 下个月同一天 | add_months(hiredate, 1) |
八、你在作业中的日期问题总结
-- 第3题 ✅ 正确 where to_char(hiredate, 'yyyymm') < 198210 -- 第5题 ⚠️ 缺少括号（结果正确但不规范） where deptno=20 and to_char(hiredate,'yyyy')<1982 or deptno=30 and to_char(hiredate,'yyyy')<1985 -- 第11题 ❌ 完全遗漏where条件 -- 应该加：where to_char(hiredate, 'yyyy') > 1981
核心要点：
- 日期比较时，优先使用范围查询（左闭右开）
- to_char 会让索引失效，大数据量时慎用
- 始终用 显式转型，不要依赖隐式转换
- 注意边界值，between 可能丢失最后一天的末尾时间
to_char 会让索引失效，大数据量时慎用。是什么意思？
索引就像一本书的"目录"，它能帮你快速翻到需要的页码。但如果对"目录"里的文字做了修改（比如加了格式），那原来的目录就失效了，你只能一页一页地翻完整本书来找内容。
1. 为什么to_char会让索引失效？
sql的执行顺序决定了索引的生效机制。
当你执行 where to_char(hiredate, 'yyyy') = '1981' 时，数据库的处理过程是这样的：
- 读取一条数据：数据库从硬盘或内存中取出第一行员工的数据。
- 执行函数：对这行数据的
hiredate列执行to_char函数，把日期类型（如1981-05-01的内部存储值）转换成字符串类型（如'1981'）。 - 条件比对：判断这个转换后的字符串
'1981'是否等于你指定的'1981'。 - 重复：对表中的每一行重复第1到第3步。
索引为什么没起作用？
因为索引里存的是原始的、未经过任何处理的 hiredate 值，而你在查询时使用的是 to_char(hiredate) 这个函数的返回值。数据库没法用原始的日期值去匹配一个函数的返回值，所以只能放弃索引，从头到尾把整张表的数据都处理一遍。
这正是你在笔记中看到的高级查询优化问题。
2. 另一种写法：为什么索引能生效？
如果你换一种写法，比如 where hiredate >= to_date('1981-01-01', 'yyyy-mm-dd')，过程完全不同：
- 函数执行一次：数据库首先执行
to_date('1981-01-01', 'yyyy-mm-dd')，把字符串'1981-01-01'转换成一个日期值（比如1981-01-01的内部存储数字）。 - 索引快速定位：数据库拿着这个日期值，直接去索引（书的目录）里查找。
- 直接找到数据：通过索引快速定位到满足条件的数据在磁盘上的物理位置，然后直接读取。
索引生效的关键在于：
列本身（hiredate）没有被任何函数、计算所改变，数据库可以直接用你在 where 里给的值去跟索引里的值做比对。
3. “大数据量时慎用”是什么意思？
| 数据量 | 影响程度 | 说明 |
|---|---|---|
| 小数据量 (几十、几百条) | 影响极微 | 即便没有索引，逐行扫描也快如闪电，用户完全感受不到差异。 |
| 中等数据量 (几万、几十万条) | 影响显著 | 逐行扫描开始变慢，可能需要几秒甚至更久，用户能明显感觉到"卡"。 |
| 大数据量 (百万、千万条以上) | 灾难性影响 | 逐行扫描会让查询耗时从毫秒级（有索引）变成分钟甚至小时级（无索引，全表扫描） |
举个生活化的例子：
- 小数据量：在一个五六个座位的家庭餐桌上找一个人，扫一眼就行，不需要名片索引。
- 大数据量：在鸟巢（容纳9万人）里找一个座位号是 '1981' 的人。如果不用座位索引，每个座位都去核对，会累到崩溃。
4. 优化建议与最佳实践
核心原则：永远不要让函数去修饰被筛选的列本身。
| 要避免的写法 (❌ 索引失效) | 推荐写法 (✅ 索引生效) | 说明 |
|---|---|---|
where to_char(hiredate, 'yyyy') = '1981' | where hiredate >= to_date('1981-01-01', 'yyyy-mm-dd') and hiredate < to_date('1982-01-01', 'yyyy-mm-dd') | 用范围查询代替函数 |
where to_char(hiredate, 'yyyymm') = '198105' | where hiredate >= to_date('1981-05-01', 'yyyy-mm-dd') and hiredate < to_date('1981-06-01', 'yyyy-mm-dd') | 同上 |
where sal * 12 > 50000 | where sal > 50000 / 12 | 让函数作用在常量上 |
where substr(ename, 1, 1) = 's' | where ename like 's%' | 使用 like 前缀匹配（也能利用索引） |
总结一句话：
写 where 条件时，让“列”自己待着，别碰它。如果需要处理，去处理等号右边的“值”。 这样你的查询才能在大数据量下保持高效。
你之前写的 where to_char(hiredate, 'yyyy') = 1981 在小数据量练习时完全没问题，但在真正的企业生产环境中（数据量可能上千万），这种写法几乎是被明令禁止的。
初学阶段掌握规则即可，但养成好习惯会很有帮助。
到此这篇关于sql 中日期的特殊性总结(格式符严格要求全大写)的文章就介绍到这了,更多相关sql日期特殊性内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！
发表评论