SQL查询：每个 id 对应的 value 和前一天比较

最新推荐文章于 2024-06-03 08:37:33 发布
最新推荐文章于 2024-06-03 08:37:33 发布
阅读量1.1k 收藏 1
点赞数
CC 4.0 BY-SA版权
分类专栏： SQL
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/qq_38362772/article/details/96107142
本文介绍了一种通过SQL查询计算连续两天数据比率的方法，并对比了复杂版本与简化版本的查询效率。通过对SQL语句的优化，简化版本提高了查询速度。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

1，复杂版本(不可取)

     我的思路是，假设查询时间段2019-07-01到2019-07-16的所有数据及其和前一天的比率，分别查出2019-07-01到2019-07-16和2019-06-30到2019-07-15的数据，存放于两张临时表，通过date1=date2+1将两张临时表关联起来，这样，当天的 value 值和前天的 value 值就在一条记录里面了，再通过条件比较即可。

临时表：

with t1 as (select aa.*,bb.the_date,bb.val,bb.data_type,bb.this_val,bb.last_val,bb.ctpt from (
    select b.SN_METER,a.statistics_date from (
         select to_char(to_date('2019-07-01','yyyy-mm-dd')+level-1,'yyyy-mm-dd') as statistics_date from dual
         connect by level <=to_date('2019-07-16','yyyy-mm-dd')-to_date('2019-07-01','yyyy-mm-dd')+1
    ) a
    full join V_DT_METER_DAY_ALL_DATA b on 1=1
    group by a.statistics_date,b.SN_METER) aa
    left join V_DT_METER_DAY_ALL_DATA bb on aa.SN_METER=bb.SN_METER and aa.statistics_date=bb.the_date),

t2 as (select aa.*,bb.the_date,bb.val,bb.data_type,bb.this_val,bb.last_val,bb.ctpt from (
    select b.SN_METER,a.statistics_date from (
         select to_char(to_date('2019-06-30','yyyy-mm-dd')+level-1,'yyyy-mm-dd') as statistics_date from dual
         connect by level <=to_date('2019-07-15','yyyy-mm-dd')-to_date('2019-06-30','yyyy-mm-dd')+1
    ) a
    full join V_DT_METER_DAY_ALL_DATA b on 1=1
    group by a.statistics_date,b.SN_METER) aa
    left join V_DT_METER_DAY_ALL_DATA bb on aa.SN_METER=bb.SN_METER and aa.statistics_date=bb.the_date)

从临时表里面查询和比较 value :

    select t1.sn_meter,t1.statistics_date,t1.val as now_value,t2.val as before_value,
            t1.last_val,t1.this_val,t1.data_type,
            round((t1.val-t2.val)/t2.val,2) as changeRate
        from t1 left join t2
            on t1.sn_meter=t2.sn_meter 
            and to_char(to_date(t1.statistics_date,'yyyy-mm-dd')-1,'yyyy-mm-dd')=t2.statistics_date
    where t2.val!=0 and t1.val!=0
union all
    select t1.sn_meter,t1.statistics_date,t1.val as now_value,t2.val as before_value,
            t1.last_val,t1.this_val,t1.data_type,
            9999 as changeRate
        from t1 left join t2
            on t1.sn_meter=t2.sn_meter 
            and to_char(to_date(t1.statistics_date,'yyyy-mm-dd')-1,'yyyy-mm-dd')=t2.statistics_date
    where t1.val=0
order by sn_meter,statistics_date

如果还有附加条件，可以把这里查到的数据看做一个整体，关联之后再做查询

2，简化版(查找速率提升) 

select t1.this_date,t1.this_used,t1.last_used,t1.rate,t2.* from
(select b1.* from
      (select a1.meter_guid,a1.this_date,a2.val as this_used,a3.val as last_used,
              (case when a3.val=0 then 0 when a2.val=0 then 1 else (a2.val-a3.val)/a3.val end) as rate
      from
           (select b.meter_guid,a.this_date,a.last_date from
                (select to_char(to_date('2019-06-01','yyyy-mm-dd')+level-1,'yyyy-mm-dd') as this_date,
                        to_char(to_date('2019-06-01','yyyy-mm-dd')+level-2,'yyyy-mm-dd') as last_date from dual
                connect by level <=to_date('2019-07-15','yyyy-mm-dd')-to_date('2019-06-01','yyyy-mm-dd')+1) a
  inner join table1 b on 1=1 and b.is_load='1' and b.sys_id='01') a1
  left join table2 a2 on a1.meter_guid=a2.sn_meter and a1.this_date=a2.the_date
  left join table2 a3 on  a1.meter_guid=a3.sn_meter and a1.last_date=a3.the_date
  )b1 where abs(b1.rate)>=0.2) t1,
(select a.meter_guid,a.customer_no,a.customer_name,a.meter_no,a.cldh,a.meter_model_name,a.wldz,b.cst_name,b.cst_addr
from table3 a,T_RUN_CST b
where a.cst_id=b.cst_id and a.is_load='1' and a.sys_id='01' and a.dot_no like '01%' and a.meter_sort='1'
  ) t2
where t1.meter_guid=t2.meter_guid
order by t1.meter_guid,t1.this_date;

       用 level-1 和 level-2 分别查找当天和前一天的日期，并放在一张表里面，连续关联用量表两次就可以得到分别有当天数据和前一天数据的结果列表，这样就可以直接对两个数据进行比较了，用case then语句代替了 union 除数为0和不为0的两种情况的并集，也是十分的巧妙，select a,b where a.xxx=b.xxx 直接相当于内连接，简化了代码

两种方法的思路是一样的，区别在于后面的方法对sql语句更加熟悉，也不是一次性取出所有数据进行比较，可以提高查询效率

SQL语句全攻略：从基础到进阶的编程之旅
邓邓子的博客
07-01 987
本文系统梳理SQL语句的核心知识体系，从基础语法到高级应用层层递进。开篇解析SQL语句的标准写法顺序，深入讲解关联查询（内连接、外连接等）与常用函数操作；继而覆盖数据库与表的创建、增删改查等基础操作，结合一对多、多对多关系场景演示复杂数据关联逻辑。针对性能优化，重点分析索引策略、子查询替代方案等实用技巧；高级部分引入窗口函数、公共表表达式（CTEs）及聚合函数的进阶应用，辅以场景化案例提升理解。无论是数据库新手还是进阶开发者，均可通过本文夯实SQL基础、掌握性能调优方法，实现从语法应用到工程实践的能力提升。
大数据Flink（一百一十七）：Flink SQL的窗口操作
Lansonli（蓝深李）的博客
09-12 1782
在流处理应用中，数据是连续不断的，因此我们不可能等到所有数据都到了才开始处理。当然我们可以每来一个消息就处理一次，但是有时我们需要做一些聚合类的处理，例如：在过去的1分钟内有多少用户点击了我们的网页。在这种情况下，我们必须定义一个窗口，用来收集最近一分钟内的数据，并对这个窗口内的数据进行计算。Flink 认为 Batch 是 Streaming 的一个特例，所以 Flink 底层引擎是一个流式引擎，在上面实现了流处理和批处理。而窗口（window）就是从 Streaming 到 Batch 的一个桥梁。
根据ID查询数据&回填
weixin_44538411的博客
06-28 1536
开发工具与关键技术：VS软件，根据ID查询数据 作者：陈隆 撰写时间：2019年06月27日 这个需要根据ID来查询数据的功能，可以说是大部分的项目中都是会用到的一个功能，因为每个项目中都是会需要修改一些数据之类的操作，而这个根据ID查询数据一般都是用于数据修改的时候使用的，修改的时候是经过获取到选中行的ID，然后传到控制器中进行查询询对应的那一条数据，记住根据ID来查询的都是只有一条数据的，因...
图解面试题：如何比较日期数据？
公众号（猴子数据分析）
12-05 1773
【题目】下面是某公司每天的营业额，表名为“日销”。“日期”这一列的数据类型是日期类型（date）。请找出所有比前一天（昨天）营业额更高的数据。（前一天的意思，如果“当天”是1月，“昨天”...
SQL | 窗口函数/表连接 | 找出所有比前一天（昨天）营业额更高的数据
weixin_46023346的博客
05-17 1919
请找出所有比前一天（昨天）营业额更高的数据 （1）创建表 CREATE TABLE IF NOT EXISTS daily_sales( id INT PRIMARY KEY AUTO_INCREMENT, date1 DATE, sales INT ); （2）插入数据 INSERT INTO daily_sales VALUES(1,'2021-3-1',97), (2,'2021-3-2',87), (3,'2021-3-3',88), (4,'
mysql与前一天数据比较_mysql查询前一天的数据
weixin_30432831的博客
01-19 2655
在mysql数据库中，常常会遇到统计当天的内容。 例如，在user表中，日期字段为：log_time 统计当天sql语句为：select * from user where date(log_time) = curdate();curdate()表示当天日期 统计前一天如果表示前一天的数据，则不能使用curdate()-1，因为当日期为月初时，curdate()-1 日期就不是上一个月的月末日期。...
Oracle获取第二天的日期,获取和指定日期前一天的对比数据，这样的sql语句该如何写呢...
weixin_29235923的博客
04-02 808
SQL codewith t (rq, sl, pid) as (select to_date('2012-9-1','yyyy-mm-dd'), 50, 1 from dual union allselect to_date('2012-9-1','yyyy-mm-dd'), 30, 2 from dual union allselect to_date('2012-8-31','yyyy-m...
【第34天】SQL进阶-SQL高级技巧-Window Funtion（SQL 小虚竹)
小虚竹的专栏
04-22 6054
【第34天】SQL进阶-SQL高级技巧-Window Funtion（SQL 小虚竹)
sql案例分析：统计连续登陆、日活、蚂蚁森林、排名等
.
07-28 3882
这样处理的结果是，相同的 group by key 有可能分发到不同的 reduce 中，从而达到负载均衡的目的；问题：假设2017年1月1日开始记录低碳数据（user_low_carbon），假设2017年10月1日之前满足申领条件的用户都申领了一颗p004-胡杨，剩余的能量全部用来领取“p002-沙柳”。问题：查询user_low_carbon表中每日流水记录，条件为：用户在2017年，连续三天（或以上）的天数里，每天减少碳排放（low_carbon）都超过100g的用户低碳流水。
SQL—数据库查询语言，全面详解演示，入门进阶必会
qq_44182424的博客
06-03 2261
优先处理 AND，当一个过滤表达式涉及到多个 AND 和 OR 时，可以使用 () 来决定优先级，使得优先级关系更清晰。为结束符，而存储过程中也包含了分号，因此会错误把这部分分号当成是结束符，造成语法错误。UPDATE 触发器包含一个名为 NEW 和一个名为 OLD 的虚拟表，其中 NEW 是可以被修改的，而 OLD 是只读的。来组合两个查询，如果第一个查询返回 M 行，第二个查询返回 N 行，那么组合查询的结果一般为 M+N 行。分为左外连接，右外连接以及全外连接，左外连接就是保留左表没有关联的行。
sql实现一条数据与前一条数据的比较
LHW0621的专栏
04-06 1万+
最近项目中遇到一些数据统计的需求，具体需求就是要统计对于某一列较之前相同id的一条数据变更的数目。描述起来可能相对抽象，我们举一个简单的例子：     对于一个记录学生分数（student_score）的表，我们要统计学生每次考试相较于上一次进步的学生总数 student_score id     name        score      exam_id 1      张三
mysqlsql怎么比较当前月与去年的这个月的同比_YTD（年初至今）日期同比分析
weixin_39605706的博客
12-21 3008
商业情景YTD分析属于同比分析类，其特点在于对比汇总值，即从年初第一日值一直至今的值累加。作用在于分析企业中长期的经营绩效。在YTD日期同比中需要注意的是取等量日期单位作为参照物。举例而言，假设当前日期是2018年7月21日，其同比计算则应为去年7月1号至21号的销售值对比，而不是对比2018年7月整个月份的值。可视化实现折线图和区域图组合，折线图适用于呈现日期变化下不同年份的绩效走势，而区域图适...
Oracle sql语句时间字段做前后比较的几种方式
m0_58440053的博客
10-13 2318
PS： update 是时间字段，to_date(时间字符串，格式化字符串)是oracle转义时间的函数 oracle sql日期比较: 在今天之前: select*fromup_datewhereupdate<to_date('2007-09-0700:00:00','yyyy-mm-ddhh24:mi:ss') select*fromup_datewhereupdate<=to_date('2007-09-0700:00:00','yyyy-mm...
python比较数据库表今天跟前一天数据增量,比较python中的时间增量
weixin_33272631的博客
12-11 243
I have a variable which is and I would like to compare it against certain values.Lets say d produces this datetime.timedelta value 0:00:01.782000I would like to compare it like this:#if d is greater ...
sql里面count值与前一次做对比，怎么写
weixin_42593549的博客
02-13 175
你可以使用 self-join 或子查询来实现这个功能。 例如，如果你想查询每一行 count 值与前一行的比较结果，你可以这样写： WITH cte AS ( SELECT column1, column2, count(column3) AS count_value, row_number() OVER (ORDER BY...
sql语句的时间比较(时间段查询) ---- 记录
晨石的博客
06-16 1万+
SQL相关 sql语句的时间比较(时间段查询) ---- 记录 sql语句的时间比较(时间段查询) ---- 记录 不管是mysql，还是sqlserver的sql语句如果直接使用 ： create_time >= '2021-06-16' and create_time <= '2021-06-16' 查询的时候，都会自动补全时间，实际查询sql语句为： create_time >= '2021-06-16 00:00:00' and create_time <= '2
sql查询每天整点时间的数据
热门推荐
郭芳的博客
05-14 1万+
数据库表里有多个时间数据，只想取每天5分钟、10分钟、1小时、4小时整点的数据，其他时间点的不要，sql可以这样写： postgreSql log_time的类型为timestamp 5min整点：select log_time from XXX where mod(extract(minute from log_time)::int,5) = 0 10min整点：select log_t...
SQL Server实现查询‘提前一天’的数据
agudaa的博客
04-23 4047
sql server 进行对数据查询“提前一天”的内容。
QueryWrapper日期查询 日期比较 查询数据库指定日期 分页查询 起止时间 日期相等 只查一天数据 QueryWrapper插入sql片段 qw.apply
tanzongbiao的专栏
12-17 1万+
QueryWrapper日期查询 日期比较 查询数据库指定日期 分页查询 起止时间 日期相等 只查一天数据
Java开发编号设计，数据库自增ID，举一个实例
最新发布
07-28
<think>我们正在讨论使用数据库自增ID作为编号生成器的一部分。根据用户的要求，我们需要提供一个具体的Java示例，展示如何在Java应用中使用数据库自增ID来生成编号。 结合引用[1]、[2]、[3]、[4]和[5]，我们可以总结以下几点： 1. 自增ID在数据库设计中常用作主键，具有简单、唯一且有序的优点（引用[1]和[4]）。 2. 在分布式系统中，自增ID可能成为瓶颈，因此有时会被雪花ID替代（引用[2]）。 3. 在Java中，我们可以使用MyBatis来获取数据库生成的自增主键（引用[3]）。 4. 自增ID可以通过SQL语句调整起始值，但删除操作可能导致ID不连续（引用[5]）。 设计思路： 我们将设计一个编号生成器，它使用数据库自增ID作为基础，然后可以添加业务前缀（如日期、类型等）来增强可读性。 但是，直接使用数据库自增ID在分布式系统中可能会有性能问题，因此我们考虑结合缓存机制（预取一批ID）来减少数据库访问。 步骤： 1. 在数据库中创建表，该表包含一个自增ID列，用于生成序列号。 2. 在Java中使用MyBatis插入一条空记录（或专门用于生成ID的记录），然后获取生成的自增ID（引用[3]）。 3. 将获取的自增ID与业务前缀（如日期、类型）组合，形成最终的编号。 然而，每次生成编号都插入一条记录显然效率低下。因此，我们采用批量预取的策略： - 我们预先在数据库中获取一批自增ID（例如，通过一次插入获取一个起始值，然后通过更新操作设置一个步长，或者使用数据库的序列（Sequence）对象，如果数据库支持的话）。 - 在应用启动时或当缓存ID用完时，向数据库申请一批ID（比如1000个），并将这些ID缓存在内存中，然后逐个分配。 但是，不同的数据库有不同的方式来实现批量自增ID的获取。例如： - MySQL：可以通过设置一个表，其中有一个自增列，然后使用`REPLACE INTO`或`INSERT ... ON DUPLICATE KEY UPDATE`来更新一个计数器，然后获取多个ID。 - 或者，使用单独的序列对象（如PostgreSQL的SEQUENCE，MySQL从8.0开始也支持SEQUENCE）。 这里我们以MySQL为例，假设我们有一个表专门用于生成序列号： 创建表： ```sql CREATE TABLE id_generator ( id bigint NOT NULL AUTO_INCREMENT, stub char(1) NOT NULL DEFAULT '', PRIMARY KEY (id), UNIQUE KEY stub (stub) ) ENGINE=InnoDB; ``` 然后，我们通过以下方式获取一批ID： ```sql REPLACE INTO id_generator (stub) VALUES ('a'); SELECT LAST_INSERT_ID(); ``` 但是，一次只能获取一个ID。为了批量获取，我们可以这样操作： ```sql UPDATE id_generator SET id=LAST_INSERT_ID(id + 1000) WHERE stub='a'; SELECT LAST_INSERT_ID(); ``` 这样，我们就一次性获取了1000个ID（从返回的LAST_INSERT_ID()开始，到LAST_INSERT_ID()+999）。 但是，这种方法在并发环境下需要保证事务性，且可能存在锁竞争。 另一种更简单的方式是使用一个序列表，每次更新一个计数器并返回新的范围。例如： 表设计： ```sql CREATE TABLE sequence ( name VARCHAR(64) NOT NULL, current_value BIGINT NOT NULL, step INT NOT NULL DEFAULT 1000, PRIMARY KEY (name) ) ENGINE=InnoDB; ``` 然后，我们编写一个存储过程或使用Java事务来更新并获取下一个区间。 然而，为了简化，我们可以使用MyBatis执行以下步骤（在事务中）： 1. 读取当前值（带锁，使用SELECT ... FOR UPDATE）。 2. 更新当前值为当前值+步长。 3. 提交事务，释放锁。 4. 然后我们就可以在内存中分配这个区间内的ID。 但是，每次预取都需要更新表，如果多个应用实例同时更新同一个序列，会有锁竞争，但频率已经大大降低（每1000个ID更新一次）。 由于用户要求使用数据库自增ID，我们可以采用第一种方式（使用自增主键的表）来模拟序列，但为了批量获取，我们使用第二种方式（带步长的序列表）更合适。 为了更贴近实际，我们采用第二种方式，并给出Java代码示例。 设计一个基于数据库序列表的编号生成器，支持批量预取，并可以添加业务前缀。 步骤： 1. 创建序列表（如上）。 2. 编写一个方法，当本地缓存的ID用完时，从数据库获取下一批ID（一个区间）。 3. 在内存中维护一个计数器，分配该区间内的ID。 4. 生成编号时，将当前日期、业务类型和序列号（从区间中取出的ID）组合。 注意：序列号是连续的，但业务前缀中的日期变化时，序列号会重置吗？这里我们不重置，因为序列号是全局唯一的，不需要按日重置。如果需要按日重置，则需要更复杂的设计（如按日分表或按日生成序列），这里我们只生成全局唯一的序列号。 示例编号：20240515-ORDER-10001 但是，如果使用全局序列号，那么同一天的不同类型订单的序列号是全局连续的，而不是每天从1开始。如果业务要求每天从1开始，那么我们需要在序列表中为每个业务类型和日期维护一个计数器。这会导致序列表的数据量增长。因此，我们这里只实现全局序列号。 如果业务要求按日重置序列，我们可以将序列表的name设置为“业务类型+日期”的组合，然后每天初始化新的记录。但这样在每天开始时可能会有竞争（多个实例同时初始化记录），需要处理。 为了简化，我们实现全局序列号，按业务类型区分序列（每个业务类型有自己的序列）。 序列表设计： name: 业务类型（如'ORDER'） current_value: 当前分配到的最大值 step: 步长（每次预取的数量） 我们设计一个IdGenerator类，它内部为每个业务类型维护一个本地缓存。 代码结构： - 一个IdGenerator类，包含一个Map，key为业务类型，value是一个对象（包含当前值、最大值，以及一个锁对象用于同步） - 当某个业务类型的本地缓存用完时，从数据库获取下一批ID（更新序列表） 数据库操作使用MyBatis，这里我们简化，直接写一个方法模拟。 具体代码： 注意：此代码为示例，未考虑分布式环境下的同步问题（多个应用实例同时更新同一序列）。在分布式环境下，需要保证更新序列表的操作是原子的（通过数据库事务和行锁），我们使用SELECT FOR UPDATE来锁定记录。 步骤： 1. 在序列表中，为每个业务类型初始化一条记录（如name='ORDER', current_value=0, step=1000）。 2. 在Java中，当需要为某个业务类型生成ID时，先检查本地缓存是否还有可用ID。如果没有，则从数据库获取下一批（step个）ID。 3. 从数据库获取时，使用事务，先锁定该业务类型的记录（SELECT ... FOR UPDATE），然后更新current_value = current_value + step，然后查询更新后的current_value（即新的最大值），然后本地缓存的范围为[new_current_value - step + 1, new_current_value]。 4. 然后释放锁，事务提交。 这样，每个业务类型独立，不会相互影响。 下面我们写一个简化的Java代码（使用MyBatis，但这里用伪代码代替MyBatis部分，重点在逻辑）： 首先，定义序列表对应的实体类： ```java public class Sequence { private String name; // 业务类型 private long currentValue; // 当前值（即已分配的最大值） private int step; // 步长 // 省略getter/setter } ``` 然后，定义IdGenerator： ```java import java.util.HashMap; import java.util.Map; import java.util.concurrent.locks.Lock; import java.util.concurrent.locks.ReentrantLock; public class IdGenerator { // 用于缓存每个业务类型的ID范围 private static class IdRange { private long current; // 当前分配的ID（从min开始，每次分配加1，直到max） private long max; // 当前缓存的最大ID public IdRange(long current, long max) { this.current = current; this.max = max; } } private Map<String, IdRange> cache = new HashMap<>(); // 用于同步的锁，每个业务类型一个锁（这里简化，使用一个全局锁，实际可以为每个业务类型单独锁） private Lock lock = new ReentrantLock(); // 步长，可以配置 private int step = 1000; // 获取下一个ID（业务类型） public long nextId(String name) { lock.lock(); try { IdRange idRange = cache.get(name); if (idRange == null || idRange.current > idRange.max) { // 缓存为空或已用完，重新从数据库获取一批 long nextStart = getNextIdFromDb(name, step); idRange = new IdRange(nextStart, nextStart + step - 1); cache.put(name, idRange); } long id = idRange.current; idRange.current++; return id; } finally { lock.unlock(); } } // 从数据库获取下一批ID的起始值（即更新序列表，并返回新的起始值） private long getNextIdFromDb(String name, int step) { // 这里应该使用MyBatis执行事务 // 1. 开启事务 // 2. 锁定记录：SELECT * FROM sequence WHERE name=#{name} FOR UPDATE // 3. 更新：UPDATE sequence SET current_value = current_value + #{step} WHERE name=#{name} // 4. 查询更新后的current_value：SELECT current_value FROM sequence WHERE name=#{name} // 5. 提交事务 // 注意：这里我们模拟，假设数据库操作成功，返回新的current_value（即原来的current_value+step） // 实际代码中需要处理异常，回滚事务。 // 假设我们有一个SequenceDao // 伪代码：使用MyBatis的Mapper // 这里我们返回一个模拟值（实际应该从数据库获取） // 为了演示，我们假设每次调用这个方法，数据库的序列值增加step，并返回增加前的值+1（即新批次的起始值） // 但实际上，我们更新后，新的批次应该是 [旧值+1, 旧值+step] // 因此，我们返回旧值+1 // 实际代码中： // long oldValue = sequenceDao.selectForUpdate(name); // 带锁查询 // long newValue = oldValue + step; // sequenceDao.update(name, newValue); // return oldValue + 1; // 因为oldValue是上一次分配的最大值，所以下一批从oldValue+1开始 // 由于是示例，我们返回一个模拟值 return 10000L; // 实际应替换为数据库操作 } // 生成带业务前缀的编号 public String generateBusinessId(String type) { long id = nextId(type); String date = java.time.LocalDate.now().format(java.time.format.DateTimeFormatter.BASIC_ISO_DATE); return String.format("%s-%s-%d", date, type, id); } } ``` 注意： - 上面的代码中，锁是全局的（lock），这在高并发下可能成为瓶颈。可以改为每个业务类型一个锁（使用ConcurrentHashMap来存放锁或IdRange对象，并确保每个业务类型的IdRange单独同步）。 - 数据库操作部分（getNextIdFromDb）需要实现事务，确保并发安全（通过数据库的行锁）。 - 应用重启时，缓存会重新初始化，从数据库获取下一批ID，因此不会出现重复。 使用MyBatis获取自增主键的方法（引用[3]）在插入业务数据时非常有用，但这里我们是用一个单独的表来生成序列号，所以不需要获取自增主键，而是通过更新和查询来获取序列号范围。 但是，如果业务要求编号与数据库自增主键一致（即业务表的主键就是自增ID，然后编号就是主键），那么我们可以直接使用MyBatis的useGeneratedKeys来获取插入后生成的自增主键，然后将其作为编号（或者添加前缀）。但是这样每次插入业务数据时都会生成一个编号，无法预取。 因此，我们上面的方案是使用一个单独的序列表来生成序列号，然后组合成编号，再插入业务表。这样业务表的主键可以是自增ID（与编号无关），也可以是编号本身（作为主键，但通常较长）。 总结：我们设计了一个编号生成器，它使用数据库序列表（非自增ID，而是通过更新和锁机制）来生成全局唯一的序列号，然后与日期和业务类型组合成可读的编号。同时，通过批量预取减少数据库访问。 最后，提供一个使用MyBatis操作序列表的Mapper接口示例（伪代码）： ```java public interface SequenceMapper { // 使用FOR UPDATE锁定记录 @Select("SELECT current_value, step FROM sequence WHERE name = #{name} FOR UPDATE") Sequence selectForUpdate(String name); @Update("UPDATE sequence SET current_value = #{newValue} WHERE name = #{name}") void update(@Param("name") String name, @Param("newValue") long newValue); } ``` 在getNextIdFromDb方法中： ```java private long getNextIdFromDb(String name, int step) { // 使用MyBatis的事务 SqlSession sqlSession = sqlSessionFactory.openSession(); try { SequenceMapper mapper = sqlSession.getMapper(SequenceMapper.class); Sequence sequence = mapper.selectForUpdate(name); if (sequence == null) { // 没有记录，初始化一条记录（需要处理） throw new RuntimeException("Sequence not found: " + name); } long oldValue = sequence.getCurrentValue(); long newValue = oldValue + step; mapper.update(name, newValue); sqlSession.commit(); // 提交事务，释放锁 return oldValue + 1; // 返回新批次的起始ID } catch (Exception e) { sqlSession.rollback(); throw e; } finally { sqlSession.close(); } } ``` 这样，我们就完成了一个使用数据库序列表生成编号的Java实现。 注意：在分布式系统中，多个应用实例同时更新同一个序列时，数据库的行锁可以保证序列的全局唯一性，但可能成为性能瓶颈。因此，步长（step）的设置很重要，需要根据并发量调整。 相关问题： 1. 在分布式环境下，这种基于数据库的序列生成器有什么优缺点？ 2. 如何优化序列生成器以减少数据库压力？ 3. 如果要求每天重置序列（如每天从1开始），如何修改设计？ 4. 使用自增ID作为业务编号有什么风险？ 5. 如何避免应用重启后序列号重复？</think>### Java开发中基于数据库自增ID的编号设计方案 在Java开发中使用数据库自增ID作为编号基础是一种常见策略，特别适合需要强顺序性和高可靠性的场景。以下是结合引用内容的设计方案和具体实现。 #### 设计要点分析（基于引用） 1. **自增ID适用场景**（引用[1][4]） - ✅ 适用：事务型系统、需要强一致性、单表主键、需要顺序ID的场景 - ❌ 不适用：分布式系统、需要业务可读性、分库分表场景 - 存储效率：自增ID（BIGINT）仅需8字节，远小于UUID（16字节）[^1][^4] 2. **核心挑战**（引用[2][5]） - 分布式系统无法保证全局唯一 - 删除操作导致ID不连续 - 高并发下数据库压力大 - 可读性差（纯数字无业务含义） 3. **解决方案**（引用[3]） - 使用MyBatis的`useGeneratedKeys`获取自增ID - 结合业务前缀增强可读性 - 批量预取减少数据库访问 #### Java实现示例（订单编号生成） ##### 1. 数据库表设计 ```sql CREATE TABLE orders ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, -- 自增ID[^4] order_no VARCHAR(32) NOT NULL UNIQUE, -- 业务编号 create_time DATETIME DEFAULT CURRENT_TIMESTAMP ); -- 自增ID起始值设置（可选）[^5] ALTER TABLE orders AUTO_INCREMENT = 100000; ``` ##### 2. Java实体类 ```java public class Order { private Long id; // 数据库自增ID private String orderNo; // 业务编号（如ORD202405150001） private LocalDateTime createTime; // getters/setters } ``` ##### 3. MyBatis映射配置（关键） ```xml <insert id="insertOrder" useGeneratedKeys="true" keyProperty="id" keyColumn="id" parameterType="com.example.Order"> INSERT INTO orders (order_no, create_time) VALUES (#{orderNo}, #{createTime}) </insert> ``` ##### 4. 编号生成服务 ```java import org.springframework.stereotype.Service; import java.time.LocalDateTime; import java.time.format.DateTimeFormatter; import java.util.concurrent.atomic.AtomicLong; @Service public class OrderNumberGenerator { // 内存缓存计数器（解决重启序列号重置问题） private final AtomicLong sequence = new AtomicLong(0); private static final int BATCH_SIZE = 100; // 预取批次大小 private long currentMaxId = 0; // 获取带业务前缀的编号 public String generateOrderNo() { return "ORD" + LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyyMMdd")) + String.format("%04d", getNextSequence()); } // 序列号管理（带批量预取） private synchronized long getNextSequence() { if (sequence.get() >= currentMaxId) { refillSequenceBatch(); // 批次不足时重新填充 } return sequence.getAndIncrement(); } // 从数据库预取序列号批次[^2] private void refillSequenceBatch() { // 实际项目需替换为数据库操作 long nextBatchStart = currentMaxId + 1; currentMaxId = nextBatchStart + BATCH_SIZE; sequence.set(nextBatchStart); System.out.println("预取ID区间: [" + nextBatchStart + "-" + currentMaxId + "]"); } } ``` ##### 5. 服务层整合 ```java @Service @RequiredArgsConstructor public class OrderService { private final OrderMapper orderMapper; private final OrderNumberGenerator numberGenerator; @Transactional public Order createOrder(Order order) { // 生成业务编号 order.setOrderNo(numberGenerator.generateOrderNo()); order.setCreateTime(LocalDateTime.now()); // 插入并获取自增ID[^3] orderMapper.insertOrder(order); System.out.println("生成订单: ID=" + order.getId() + " 编号=" + order.getOrderNo()); return order; } } ``` #### 执行流程说明 1. **生成业务编号**：`ORD20240515001`（日期+序列号） 2. **插入数据库**：MyBatis自动获取自增ID[^3] 3. **ID分配示例**： ```text 预取ID区间: [100000-100100] 生成订单: ID=100000 编号=ORD202405150001 生成订单: ID=100001 编号=ORD202405150002 ... ``` #### 优化策略（应对高并发） 1. **批量预取ID**（引用[2]） - 每次从数据库预取ID区间（如1000个） - 减少90%+的数据库访问 ```java // 数据库预取实现（伪代码） private void refillSequenceBatch() { Long startId = idRepository.getNextBatch(BATCH_SIZE); currentMaxId = startId + BATCH_SIZE - 1; sequence.set(startId); } ``` 2. **Redis辅助生成**（解决分布式问题） ```java // 使用Redis原子操作 private long getRedisSequence() { return redisTemplate.opsForValue() .increment("order_seq", BATCH_SIZE) - BATCH_SIZE + 1; } ``` 3. **业务连续性保障** - 添加日期前缀解决ID不连续问题[^5] - 定期归档历史订单释放ID空间 #### 适用场景评估 | **场景** | **推荐方案** | **原因** | |-------------------|--------------------------|-----------------------------| | 单机事务系统 | ✅ 纯自增ID | 简单高效[^1] | | 分布式订单系统 | ✅ 自增ID+业务前缀 | 平衡性能与可读性[^2] | | 分库分表系统 | ❌ 自增ID | 需改用雪花算法[^2] | | 高并发写入 | ✅ 自增ID+批量预取 | 降低数据库压力[^2][^3] | > **关键选择原则**：当系统需要严格的事务一致性和顺序ID时，自增ID仍是最佳选择；分布式场景可结合业务前缀提升可读性，并通过预取机制优化性能[^1][^2][^3]。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值