大表上新增字段问题--相关解决方案

一、

这个问题是比较复杂,特别是访问比较频繁的表。
可能需要分成几个小步骤来实现:
1、先增加字段,不要设置默认值。
2、然后给该字段赋值,update方式
3、最后修改表结构,设置默认值。

 

二、

1、检查此表是否有被其它对象引用,如果引用对象过多且并发操作多,则此操作一定要避免在业务高峰期操作,如果引用量特别多且相应对象并发操作多,则可能要考虑短暂停止此表及相关引用对象相关模块的服务(如果可以分离的话),如果不能够,则要考虑整个业务暂时停止。
2、正式操作的时候采用以下顺序可以降低操作对系统的影响:
   1)添加字段,不加默认值
   2)添加默认值
   ---1,2步要分开来做的原因是如果直接加含有默认值的字段则原来的记录上都会更新成默认值,这样系统很大机会出现不可用甚至崩溃(redo,undo,数据库连接,相关对象失效导致访问的进程相互编译导致等待等。。。),而分开操作,则原来记录上为null值,默认值只会在新加记录上生效,对系统影响很小
   3)如果有引用对象失效,立即编译失效对象,避免影响业务
   4)采用批量更新的方式对新加字段的值更新为默认值,根据业务对此表的DML频繁度来控制批量,如果频繁度高,则批量降低,避免锁定造成堵塞,另外更新的时候还要再次确认是null值才设置成默认值。 
      一个比较合适的方式大致如下(如果系统很繁忙,为了安全还可以考虑批量操作过程中再增加休息(用dbms_lock.sleep),保证系统影响度低,安全可靠):
      cursor vCur_update is select rowid from 表名 where 新加字段 is null;
        begin
             open vCur_update;
             fetch  vCur_update bulk collect
                     into vRowid_table limit  1000;
            forall i in 1 .. vRowid_table .count
              update 表名 set 新加字段=默认值 where rowid=vRowid_table(i) and 新加字段 is null;--避免新加字段在其它业务操作中已变成非null值且不是默认值
              commit;
             exit when vCur_update%notfound;
            end loop;
           close vCur_update;
        end;
     
   --批量更新null值至默认值的操作要在设置完默认值后才做,原因是如果在添加字段后立即更新,则在更新过程中新进记录新加字段还是为null值,这样需要在添加默认值后再更新一次才能够保证符合真正的业务需求,否则仍然有可能存在null值

 

三、

你可以先建一个相同的表结构,加一个自增的字段啊,然后把这个大表的数据导入啊。然后删除大表,把这个表名修改成原来的那个表不就行了

 

四、

先把表里的数据转移,select * into XX
清空表
再增加字段
回填数据

 

五、

1.用脚本去增加
alter table tabname add id int identity

alter table Tablename add columnname int default(0) not null
2.通过转换到临时表创建自增列

 

六、

如果添加新字段的话,有可能会引起额外的开销。建议这样,先做一个和该表A结构完全相同的表B,然后在表B上进行,开有没有额外的开销,如果没有,那么就可以做,如果有的话,那就要看情况而定了

 

 

<think>我们参考用户提供的引用资料,特别是引用[2]和引用[3]中的方法,来实现在没有主键的新增主键并对所有字段进行排序。 步骤: 1. 备份原(结构和数据) 2. 创建一个新,结构类似于原,但增加一个主键字段(自增) 3. 将原的数据插入到新中(此时主键字段会自动生成) 4. 删除原 5. 将新重命名为原名 但是,用户还要求“对所有字段进行排序”。这里需要注意,数据库中的记录本身是无序的,我们通常通过ORDER BY子句来排序。如果用户希望数据按照某种顺序存储(虽然这通常不是必要的,因为查询时排序更常见),我们可以通过插入数据时按指定顺序插入来实现。 然而,在InnoDB中,的数据存储顺序通常由主键决定(聚集索引)。因此,如果我们按照某个字段的顺序插入数据,并且该是使用主键作为聚集索引,那么数据在物理存储上会按照主键顺序排列。但实际应用中,我们更关注查询时的排序。 所以,我们可以这样理解用户的需求: 1. 添加一个自增主键(这样主键值就是顺序的,并且物理存储会按照主键顺序) 2. 如果用户希望数据按照其他字段排序,那么可以在插入新时使用ORDER BY,这样自增主键就会按照那个顺序生成,从而在物理存储上接近该顺序(但注意,自增主键是连续的,但物理存储顺序可能因为后续的删除和插入而改变,所以通常不依赖物理顺序)。 但是,引用[2]中并没有在插入数据时排序,而是直接插入。因此,我们可以在插入数据时使用ORDER BY来按照用户指定的字段排序,这样生成的主键就会按照这个顺序递增,从而在物理存储上致按照该顺序(聚集索引按主键排序)。 具体步骤: 假设原名为`student`,我们想要按照字段`name`进行排序(这里只是示例,用户可以根据需要选择排序字段)。 步骤: 1. 备份结构:创建新,结构同原,并增加一个自增主键字段(放在第一列)。 2. 备份数据:将原的数据按照指定字段(如`name`)排序后插入新(这样新的主键会按照排序顺序生成)。 3. 删除原。 4. 重命名新为原名。 注意:如果中有外键约束,操作会复杂一些,需要先删除外键约束,操作完再添加。这里假设没有外键约束。 具体SQL操作: 1. 创建新(结构同原,并增加自增主键): ```sql CREATE TABLE new_student LIKE student; ``` 2. 修改新,删除原有的主键(如果有的话,但原没有主键,所以可能没有)并添加新的自增主键字段(例如`id`)作为第一个字段: ```sql ALTER TABLE new_student DROP PRIMARY KEY; -- 如果原有主键(但用户说没有,所以可能不需要),但为了安全,如果原有主键约束,则删除 ALTER TABLE new_student ADD id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST; ``` 但是,由于原没有主键,第一步创建的新也不会有主键,所以我们可以直接添加主键字段。 然而,引用[2]中先创建了一个相同结构的(没有主键),然后删除可能存在的字段(这里假设原没有主键字段,所以不需要删除),然后添加自增主键字段。 3. 插入数据,并按照指定字段排序(例如按照`name`字段升序): ```sql INSERT INTO new_student (col1, col2, ...) SELECT col1, col2, ... FROM student ORDER BY name; -- 这里按照name排序,其他字段按需列出 ``` 注意:这里需要列出原的所有字段(除了新增的id字段,因为id是自增的,不需要插入值)。 4. 删除原: ```sql DROP TABLE student; ``` 5. 重命名新: ```sql ALTER TABLE new_student RENAME TO student; ``` 但是,上述步骤中,第一步创建的结构是原结构(没有主键),然后我们添加了主键字段。在插入数据时,我们按照指定字段排序,这样新中的数据就会按照排序顺序生成自增主键,从而在物理存储上致按照该顺序(因为主键是递增的,聚集索引会按主键顺序存储)。 然而,引用[2]中的步骤是先备份结构和数据,然后在新中删除可能存在的旧主键字段,再添加新的自增主键字段。但原没有主键,所以可以省略删除主键字段的步骤。 另外,引用[4]中提到排序字段的设计问题,但这里我们使用自增主键作为排序的依据,并且由于主键是自增的,每次插入都会递增,所以删除记录后再插入,新记录的主键会继续递增,不会重复使用已删除的主键值。这样,物理存储顺序会按照主键顺序,而主键顺序就是我们插入时的顺序(即我们指定的排序顺序)。 但是,如果用户希望以后插入数据时也能自动排序,那么需要在插入数据时指定位置(比如在中间插入),但自增主键无法在中间插入,所以通常我们会让新插入的数据排在最后(自增主键的最值)。如果用户需要动态调整顺序,可能需要额外的排序字段(如一个位置字段),并在每次插入或移动时更新该字段,然后根据这个字段排序(引用[4]中讨论的排序字段设计)。但用户当前需求是“对所有字段进行排序”,可能只是希望初始时按照某个顺序,所以上述方法可以满足。 注意:在执行这些操作前,请务必备份整个数据库,以防操作失误。 下面是一个具体的例子: 假设原`student`有字段:`name`, `age`, `score`,没有主键。 步骤: 1. 创建新`new_student`,结构同原: ```sql CREATE TABLE new_student LIKE student; ``` 2. 在新中添加自增主键字段`id`(放在第一列): ```sql ALTER TABLE new_student ADD id INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST; ``` 3. 将原数据按照`name`排序后插入新(注意:不插入id,id会自动生成): ```sql INSERT INTO new_student (name, age, score) SELECT name, age, score FROM student ORDER BY name; -- 按照name升序排序 ``` 4. 删除原: ```sql DROP TABLE student; ``` 5. 重命名新: ```sql ALTER TABLE new_student RENAME TO student; ``` 这样,`student`就有主键`id`,并且数据按照`name`排序后插入,所以主键值从1开始递增,且物理存储致按照`name`的顺序(因为主键顺序和插入顺序一致)。 注意:如果原数据量很,插入操作可能会比较慢,并且会占用额外的磁盘空间。 另外,如果原有外键引用,那么需要先删除外键约束,完成后再重新添加。同时,如果有视图、存储过程等依赖于原,也需要重新创建。 因此,在实际操作中,建议在数据库维护时段进行,并确保有完整备份。 最后,生成相关问题:</think>### 在无主键新增主键并排序字段的实现步骤 #### 1. **备份原数据** ```sql -- 创建结构相同的备份 CREATE TABLE backup_table LIKE original_table; -- 复制数据到备份 INSERT INTO backup_table SELECT * FROM original_table; ``` > 📌 操作前务必备份数据,防止意外丢失[^2]。 --- #### 2. **添加自增主键字段** ```sql -- 添加自增主键列(作为第一列) ALTER TABLE original_table ADD COLUMN id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST; ``` > ✨ 主键特性: > - 唯一标识每条记录(`PRIMARY KEY`) > - 自动按插入顺序生成连续编号(`AUTO_INCREMENT`) > - 物理存储按主键值排序(聚集索引特性)[^3][^5] --- #### 3. **按需求排序字段** ```sql -- 重建数据(按指定字段排序) CREATE TABLE sorted_table LIKE original_table; -- 插入时按 name 字段升序排序 INSERT INTO sorted_table SELECT * FROM original_table ORDER BY name ASC; -- 替换为实际排序字段 ``` --- #### 4. **替换原并验证** ```sql -- 删除原 DROP TABLE original_table; -- 重命名排序后的 ALTER TABLE sorted_table RENAME TO original_table; -- 验证主键和排序 SHOW CREATE TABLE original_table; SELECT * FROM original_table LIMIT 10; ``` > ⚠️ **关键注意事项** > 1. **操作**:数据量较时建议分批操作,避免锁 > 2. **外键依赖**:若存在外键引用,需先解除约束 > 3. **自增值重置**:最后执行 `ALTER TABLE original_table AUTO_INCREMENT=1;` 重置计数 > 4. **排序逻辑**:物理存储顺序仅影响查询效率,业务排序仍需用 `ORDER BY`[^4] --- ### 主键设计建议(引用总结) | 方案 | 优点 | 缺点 | |---------------|-------------------------------|-------------------------------| | **自增主键** | 高效易用,避免冲突[^5] | 分布式系统难扩展 | | **业务主键** | 天然唯一(如ISBN) | 字段较长影响索引效率 | | **组合主键** | 解决多维度唯一性 | 外键引用复杂 | > 推荐优先使用自增主键,除非有明确业务标识需求[^1][^5] ---
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值