关于数据库自增ID数据写入问题的解决

本文介绍了一个关于SQL中自增字段显式值设置的具体示例,通过使用SETIDENTITY_INSERT命令,演示了如何在特定情况下开启和关闭自增字段的自动增长功能。

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

------------自增字段加显式值
SET IDENTITY_INSERT GoTone_Health.dbo.[Boss_Sysc_Integral2] ON
GO


SET IDENTITY_INSERT GoTone_Health.dbo.[Boss_Sysc_Integral2] OFF
GO

<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、付费专栏及课程。

余额充值