数据库操作常用技巧

无论是开发、测试，还是DBA，都难免会涉及到数据库的操作，比如：创建某张表，添加某个字段、添加数据、更新数据、删除数据、查询数据等等。
正常情况下还好，但如果操作数据库时出现失误，比如：

1. 删除订单数据时where条件写错了，导致多删了很多用户订单。
2. 更新会员有效时间时，一次性把所有会员的有效时间都更新了。
3. 修复线上数据时，改错了，想还原。

还有很多很多场景，我就不一一列举了。
如果出现线上环境数据库误操作怎么办？有没有后悔药？
答案是有的，请各位看官仔细往下看。

1.不要用聊天工具发sql语句

通常开发人员写好sql语句之后，习惯通过聊天工具，比如：qq、钉钉、或者腾讯通等，发给团队老大或者DBA、运维人员在线上环境执行。但由于有些聊天工具，对部分特殊字符会自动转义，而且有些消息由于内容太长，会被自动分成多条消息。
这样会导致团队老大或者DBA复制出来的sql不一定是正确的。
他们需要手动拼接成一条完整的sql，有时甚至需要把转义后的字符替换回以前的特殊字符，无形之中会浪费很多额外的时间。即使最终sql拼接好了，真正执行sql的人，心里一定很虚。
所以，强烈建议把要在线上执行的sql语句用邮件发过去，可以避免使用聊天工具的一些弊端，减少一些误操作的机会。而且有个存档，方便今后有问题的时候回溯原因。很多聊天工具只保留最近7天的历史记录，邮件会保留更久一些。
别用聊天工具发sql语句！

2.操作数据之前先select一下

尤其是修改和删除数据。
很多时候，由于我们人为失误，把where条件写错了。但没有怎么仔细检查，就把sql语句直接执行了。影响范围小还好，如果影响几万、几十万，甚至几百万行数据，我们可能要哭了。
针对这种情况，在操作数据之前，把sql先改成select count(*)语句，比如：

update table_lee set status=1 where status=0;

改成：

select count(*) from table_lee where status=0;

查一下该sql执行后影响的记录行数，做到自己心中有数。也给自己一次测试sql是否正确，确认是否执行的机会。

3.操作数据之前注意量级

即使通过上面的select语句确认了sql语句没有问题，执行后影响的记录行数是对的也不要着急立即执行。
首先确认一下你要操作数据的量级，如果只是几千条（这个量级要看具体看你的服务器性能以及你的数据库量级），直接执行不会有太大影响，如果你updae的是几十上百万的数据，可能你一波操作直接资源吃满。
建议分批次进行update，比如你现在查出来有1000w的数据量，可以使用以下操作：

update table_lee set status=1 where status=0 and rownum < 2001 ;
commit；

每次只更新2000条数据，这样你只需要执行5000次就可以了。什么？你说5000次会累死？那你会不会写个循环让它自己跑

DECLARE
V_ALL         NUMBER;
V_UPDAATE_NUM NUMBER;
V_NUM         NUMBER;
BEGIN
  V_UPDAATE_NUM := 2000;
  SELECT COUNT(*) INTO V_ALL FROM table_lee;
  V_NUM := CEIL(V_ALL/V_UPDAATE_NUM);
  FOR i IN 1..V_NUM LOOP
    UPDATE table_lee SET status = 1 WHERE status=0 AND ROWNUM < 2001 ;
    COMMIT;
    END LOOP;    
END;

4.update时更新修改人和修改时间

很多人写update语句时，如果要修改状态，就只更新状态，不管其他的字段。比如：

update table_lee set status=1 where status=0;

这条sql会把status等于0的数据，全部更新成1。
后来发现业务逻辑有问题，不应该这么更新，需要把status状态回滚。
这时你可能会很自然想到这条sql：

update order set status=0 where status=1;

但仔细想想又有些不对。
这样不是会把有部分以前status就是1的数据更新成0？
这回真的要哭了，呜呜呜。
这时，送你一个好习惯：在更新数据的时候，同时更新修改人和修改时间字段。

update order set status=1,edit_date=now(),edit_user='admin' where status=0;

这样在恢复数据时就能通过修改人和修改时间字段过滤数据了。
后面需要用到的修改时间通过这条sql语句可以轻松找到：

select edit_user ,edit_date from table_lee order by edit_date desc limit 50;

当然，如果是高并发系统不建议这种批量更新方式，可能会锁表一定时间，造成请求超时。
有些同学可能会问：为什么要同时更新修改人，只更新修改时间不行吗？
主要有如下的原因：

1. 为了标识非正常用户操作，方便后面统计和定位问题。
2. 有些情况下，在执行sql语句的过程中，正常用户产生数据的修改时间跟你的可能一模一样，导致回滚时数据查多了。

5.多用逻辑删除，少用物理删除

在业务开发中，删除数据是必不可少的一种业务场景。
有些人开发人员习惯将表设计成物理删除，根据主键只用一条delete语句就能轻松搞定。
他们给出的理由是：节省数据库的存储空间。
想法是好的，但是现实很残酷。
如果有条极重要的数据删错了，想恢复怎么办？
此时只剩八个字：没有数据，恢复不了。
如果之前设计表的时候用的逻辑删除，上面的问题就变得好办了。删除数据时，只需update删除状态即可，例如：

update order set del_status=1,edit_date=now(),edit_user='admin' where id=123;

假如出现异常，要恢复数据，把该id的删除状态还原即可，例如：

update order set del_status=0,edit_date=now(),edit_user='admin' where id=123;

6.操作数据之前先做备份

切记生产环节第一准则，备份
切记生产环节第二准则，备份
切记生产环节第三准则，备份
如果只是修改了少量的数据，或者只执行了一两条sql语句，通过上面的修改人和修改时间字段，在需要回滚时，能快速的定位到正确的数据。
但是如果修改的记录行数很多或者执行了删除操作，并且执行了多条sql，产生了很多修改时间。这时，你可能就要犯难了，没法一次性找出哪些数据需要回滚。
为了解决这类问题，可以将表做备份。
可以使用如下sql备份：

create table order_bak_2021031722 select * from`order`;

创建表的同时复制数据到新表中。
此外，建议在表名中加上bak和时间，一方面是为了通过表名快速识别出哪些表是备份表，另一方面是为了备份多次时好做区分。因为有时需要执行多次sql才能把数据修复好，这种情况建议把表备份多次，如果出现异常，把数据回滚到最近的一次备份，可以节省很多重复操作的时间。
恢复数据时，把sql语句改成select语句，先在备份库找出相关数据，每条数据对应一条update语句，还原到老表中。

7.表名前面一定要带库名

我们在写sql时为了方便，习惯性不带数据库名称。比如：

update order set status=1,edit_date=now(),edit_user='admin' where status=0;

假如有多个数据库中有相同的表order，表结构一模一样，只是数据不一样。
由于执行sql语句的人一个小失误，进错数据库了。
然后执行了这条sql语句，结果悲剧了。
有个非常有效的预防这类问题的方法是加数据库名：

update lee.table_lee set status=1;

这样即使执行sql语句前进错数据库了，也没什么影响。

8.新加字段一定要注意非空或者主键限制

如果你要新加一个字段，并且设置为了非空操作，你就会遇到以下错误：

SQL> ALTER TABLE lee ADD (aaa VARCHAR2(10) NOT NULL);
ORA-01430: column being added already exists in table

此时你就需要给一个默认值：

ALTER TABLE lee ADD bbb VARCHAR2(10) DEFAULT 0  NOT NULL ;

涉及到主键限制的，先把表备份，再清掉数据。

后续持续更新，如果您觉得对您有那么一点点作用记得一键三连，欢迎留言私信。