本文转载自微信公众号《苏三说技术》，作者因热爱而坚持ing。转载本文请联系苏三硕科技公众号。前言无论是开发、测试，还是DBA，都不可避免地会涉及到数据库操作，比如：创建表、添加字段、添加数据、更新数据、删除数据、查询数据等等。一般情况下是可以的，但是如果在操作数据库的时候出现错误，比如：在删除订单数据的时候，where条件写错了，导致很多用户订单被删除。更新成员有效时间时，一次性更新所有成员的有效时间。修复在线数据时出错，想恢复。场景还有很多，就不一一列举了。线上环境数据库误操作怎么办？有后悔药吗？答案是肯定的，请仔细阅读。1、不要使用聊天工具发送sql语句。通常，开发人员写好sql语句后，习惯通过QQ、钉钉、腾讯通讯等在线聊天工具发给团队老大或DBA执行。但是由于一些聊天工具，一些特殊字符会被自动转义，一些消息会因为内容过长而自动分成多条消息。这样就会导致团队老大或者DBA复制出来的sql不一定正确。他们需要手动拼接成一个完整的sql，有时甚至需要将转义字符替换回之前的特殊字符，无形中会浪费很多额外的时间。就算最后完成了sql拼接，实际执行sql的人肯定是心虚的。因此，强烈建议大家将需要在线执行的sql语句通过邮件发送，这样可以避免使用聊天工具的一些弊端，减少一些误操作的几率。并且有存档，方便以后出现问题的时候追溯原因。许多聊天工具只保留最近7天的历史记录，而电子邮件会保留更长的时间。不要使用聊天工具发送SQL语句！不要使用聊天工具发送SQL语句！不要使用聊天工具发送SQL语句！重要的事情说三遍，确实可以减少一些误操作。2、将sql语句压缩成一行有时候，开发人员写的sql语句很长，使用各种join和union，使用美化工具将一个sql变成多行。复制sql时，不能确定sql是否完整。（为了装逼，我也骗了自己，哈哈哈）有时候在线上环境下，需要通过命令行连接数据库，比如：mysql，你复制sql语句后，在命令行执行界面，因为屏幕滚动太快，此时无法判断sql是否执行成功。对于这类问题，强烈建议将SQL语句压缩成一行，去掉多余的换行和空格，可以有效减少一些误操作。推荐使用sql压缩工具：https://tool.lu/sql/3。在操作数据之前选择。特别说明：本文中的操作数据主要是指修改和删除数据。很多时候，由于我们的人为错误，where条件写错了。但是没有仔细检查，直接执行了sql语句。影响范围小固然好，但如果影响到几万、几十万，甚至几百万行的数据，我们可能会哭。针对这种情况，在操作数据之前，将sql改成一条selectcount(*)语句，例如：updateordersetstatus=1wherestatus=0;将其更改为：selectcount(*)fromorderwherestatus=0；查看sql执行影响的记录行数，让你知道的就知道了。也给自己一个机会测试sql是否正确，确认是否执行。4、操作数据sqlpluslimit即使上面的select语句确认sql语句没有问题，但是执行后受影响的记录行数是正确的。还建议您不要立即执行它。建议在执行时加上limit+select输出的记录行数。例如：updateordersetstatus=1wherestatus=0limit1000；假设一次更新的数据过多，所有相关的记录行都会被锁住，导致长期锁等待，用户请求超时。另外，加入limit可以避免一次操作过多的数据，影响服务器的CPU。还有一个最重要的原因：加上限制后，操作数据的影响范围是完全可控的。5.更新时更新修改人和修改时间。很多人在写update语句的时候，如果要修改state，只更新state，不管其他字段。例如：updateordersetstatus=1wherestatus=0；这条sql会将status等于0的所有数据更新为1，后来发现是业务逻辑有问题。不应该这样更新，status状态需要回滚。这时候你可能很自然地想到这个sql：updateordersetstatus=0wherestatus=1;但是仔细一想，不对劲。这不是把之前状态为1的部分数据更新为0了吗？这次真的要哭了，呜呜呜。这个时候我就给大家一个好习惯：在更新数据的时候，同时更新修饰符和修改时间字段。updateordersetstatus=1,edit_date=now(),edit_user='admin'wherestatus=0;这样在恢复数据的时候就可以通过修改的人和修改的时间字段来过滤数据。后面要用到的修改时间可以通过这个sql语句很容易找到：selectedit_user,edit_datefrom`order`orderbyedit_datedesclimit50;当然，如果是高并发系统，不推荐这种批量更新的方式，可能会锁表一段时间，导致请求超时。可能有同学会问：为什么要同时更新修改人，而只更新修改时间呢？主要原因有以下几点：为了识别异常的用户操作，方便后期的统计和定位问题。在某些情况下，SQL语句执行过程中，普通用户产生的数据修改时间可能和你的完全一致，导致回滚时检查数据过多。6.多用逻辑删除，少用物理删除。在业务开发中，删除数据是必不可少的业务场景。一些开发者习惯于将表设计成物理删除，只需要一条基于主键的delete语句就可以轻松完成。他们给出的理由是：为了节省数据库的存储空间。想法很好，但现实很残酷。误删了一些极其重要的数据，想要恢复怎么办？此时只剩下八个字：无数据，无恢复。（PS：或许可以通过binlog二进制文件恢复）如果之前设计表的时候使用了逻辑删除，上面的问题就会变得好办一些。删除数据时，只更新删除状态即可，例如：updateordersetdel_status=1,edit_date=now(),edit_user='admin'whereid=123；如果有异常，恢复数据，恢复id的删除状态，例如：updateordersetdel_status=0,edit_date=now(),edit_user='admin'whereid=123;7、操作数据前做好备份。如果只修改了少量数据，或者只执行了一两条SQL语句，通过上面的修改人和修改时间字段，在需要回滚的时候可以快速定位到正确的数据。但是如果修改记录很多，执行多条SQL，就会产生很多修改时间。这时候你可能会犯难，你无法一次性找出哪些数据需要回滚。为了解决这类问题，可以备份表。您可以使用以下sql备份：createtableorder_bak_2021031721like`order`；insertintoorder_bak_2021031721select*from`order`;首先创建一个相同的表，然后将数据复制到新表中。也可以简化成一条sql：createtableorder_bak_2021031722select*from`order`;创建表时将数据复制到新表中。另外，建议表名加上bak和time。一方面是通过表名快速识别哪些表是备份表，另一方面也便于区分多个备份。因为有时候需要多次执行SQL来恢复数据。这种情况下，建议多次备份表。如果出现异常，将数据回滚到最新的备份，可以节省大量重复操作的时间。恢复数据时，将sql语句改为select语句，先在备份数据库中找到相关数据，每条数据对应一条update语句，恢复到旧表。8.将中间结果写入临时表。有时候，我们需要用一个sql查询要更新的记录的id，然后通过这些id来更新数据。批量更新后，如果出现问题，需要回滚数据。但是因为更新了一些数据，所以这次用了同样的sql，同样的条件，但是找不到和上次一样的id。这时，我们开始恐慌了。对于这种情况，我们可以先将第一次查询的id存储到一个临时表中，然后将临时表中的id作为查询条件来更新数据。如果要恢复数据，只需要以临时表中的id作为查询条件，更新数据即可。修改后，3天后，如果没有问题，就可以删除临时表了。9.数据库名必须在表名前。为了方便，我们在写SQL的时候习惯性的不包含数据库名。例如：updateordersetstatus=1,edit_date=now(),edit_user='admin'wherestatus=0;如果有多个表顺序相同的数据库，表结构完全一样，只是数据不同。由于执行sql语句的人犯了一个小错误，进错了数据库。使用贸易1；然后执行了这条sql语句，结果就悲剧了。一个非常有效的防止此类问题的方法是添加数据库名称：update`trade2`.`order`setstatus=1,edit_date=now(),edit_user='admin'wherestatus=0;这样即使sql语句执行出错，数据库没了，也没关系。10、增删改字段的限制在很多情况下，我们都离不开对表字段的操作，比如：增删改字段，但每个情况都不一样。新添加的字段必须允许空字段新添加的字段必须允许空字段。为什么要这样设计？一般情况下，如果程序中增加了一个新的字段，通常是先将字段添加到数据库中，然后再发送程序的最新代码。为什么是这个顺序？因为如果先发送程序，然后将字段添加到数据库中。在程序刚刚部署成功，但是数据库中还没有新增字段的这段时间，在最新的程序中，所有使用新增字段的增删改查SQL都会报异常，这些字段不存在。好吧，按照先向数据库添加字段，再发送程序的顺序。如果数据库中新添加的字段不为空，最新的程序还没有发布，老代码还在线上运行，此时如果有插入操作，会报该字段无法插入的异常空的。因为新加的非空字段，旧的代码无法赋值。因此，新添加的字段必须允许为空。另外，这种设计更多的是考虑了程序发布失败时的回滚操作。如果新添加的字段允许为空，就不用回滚数据库，只需要回滚代码，是不是很方便？不允许删除字段。不允许删除字段，尤其是必填字段更不能删除。你为什么这么说？假设开发者把程序改成不使用deleted字段，接下来怎么部署呢？如果先部署程序，没有时间删除数据库相关的表字段。当有插入请求时，由于数据库中必填该字段，所以会报该必填字段不能为空的异常。如果先删除数据库中的相关表字段，程序将无法及时发布。此时所有涉及删除字段的增删改查都会报该字段不存在的异常。因此，线上环境中的必填字段一定不能删掉。修改字段根据实际情况分为三种情况：1.修改字段名修改字段名也是不允许的，类似于删除必填字段的问题。如果部署了程序，没有时间去修改数据库中的表字段名。此时所有涉及该字段的增删改查都会报该字段不存在的异常。如果先更改数据库中的字段名称，程序将无法及时发布。此时所有涉及该字段的增删改查，查询也会报该字段不存在的异常。所以线上环境字段名一定不能修改。2.修改字段类型修改字段类型时，一定要兼容之前的数据。例如：tinyint可以改成int，但int改成tinyint时需要慎重衡量。可以将varchar转为text，但是在将text转为varchar时需要慎重权衡。3.修改字段长度建议增加字段长度，但一般情况下不建议改小。如果一定要改小，首先要确认字段中可能出现的最大长度，避免插入操作时出现字段过长的异常。另外，建议设置合理的长度，避免浪费数据库资源。小结本文分享10种减少数据库误操作的方法，并不是所有场景都适合你。尤其是在一些高并发或者单表数据量非常大的场景下，需要根据实际情况进行选择。但我相信，看完这篇文章，你一定会有所收获，因为大部分的方法都适用于你，说不定会让你少走很多弯路。强烈推荐收藏。
