如何清理MySQL中错误导入的数据？使用DELETE语句和事务回滚的方法

说实话，我以前也做过几次这样的清理MySQL错误数据的事情，每次都像引爆炸弹一样有趣。
今天我给大家讲一下我的实践经历，可能有些偏激，但都是血泪的教训。
1 . 在这个阶段，你需要像侦探一样寻找线索，以获取正确的问题数据。
上次导入工具出错的时候，出错数据都集中在凌晨2 点到3 点之间。
当时我直接使用 WHERE create_ at BETWEEN '2 02 2 -08 -2 6 02 :00:00' 和 '2 02 2 -08 -2 6 03 :00:00' 来锁定范围。
这个技巧几乎永远不会起作用，但关键是获取有效的时间戳字段。
有趣的是，仅仅看时钟是不够的。
我曾经遇到过一段垃圾数据，其中age字段只有9 9 9 ，我直接使用WHERE Age > 1 5 0找到了它。
但是，有一个陷阱需要注意。
例如，一个特定系统使用 -1 来指示无效条件。
您需要更改 WHERE status_code NOT IN ('valid1 ', 'valid2 ') 的语法。
为了获得更高的准确性，请直接检查 t.status_code = -1
这种验证外键的方法给我留下了深刻的印象。
有一次，从相关表中删除了一大块数据，在原始表中留下了数千条相关_id 指向 NULL 的记录。
使用 LEFT JOIN 生成的 SQL 非常丑陋，但结果却令人惊讶： 广场 选择 t1 来自您的 main_table t1 左连接关联表 t2 ON t1 .关联_id = t2 .id 其中 t2 .id 为空。
LIMIT 1 000;
一次仅检查 1 000 个项目，以避免数据库拥塞。
日志导出也是一项独特的功能。
我记得在binlog中看到导入工具卡在了某行数据上，我用mysqlbinlog回来找到了问题文件和行号。
2 . 谈到事情，一开始我总是很困惑。
我曾经因为忘记BEGIN而删除了数万条记录。
我删除了太多，很快就ROLLBACK，我的手在颤抖。
后来我意识到我应该先写： 广场 开始购物； -
执行删除操作 DELETE 条件 FROM 表 WHERE; -
检查，如果没有问题就COMMIT COMMIT;
提交阶段不能省略。
我建议使用： 广场 SELECT COUNT() FROM table WHERE your_condition;
如果返回 0，则为纯值。
有一个小技巧。
REPEATABLE 在验证之前锁定数据您可以使用 READ 隔离级别： 广场 设置事务隔离级别重复读； 开始购物； -
执行取消和确认 COMMIT;
这段时间无人可以修改数据，使得验证更加准确。
3 . 在有用的策略中，我特别推荐更新。
当我发现导入时用户的性别填写为“A”时，我只是使用了： 广场 UPDATE your_table SET性别 = case WHEN 性别 = 'A' THEN '男' END ELSE 性别;
几行代码就可以完成，比删除和重复简单得多。
我仅在数据量特别大（例如数十万或更多）时进行清理导出。
这是我们学到的一个教训：导出 CSV 时不要忘记添加 --fields-termminate-by=，否则数据将被损坏。
我通常也使用临时表+联接策略，特别是 INSERT INTO...SELECT... 解决方案： 广场 -
首先保存一个临时表 创建临时表排列作为 SELECT FROM input_data； -
校准清洁 INSERT INTO main_table (col1 , col2 ) FROM SET col1 , col2 WHERE col3 IS NOT NULL,
备份和恢复是最后一道防线。
我的一个客户曾经搞砸过并删除了主表数据。
幸运的是，他前一天已经进行了完整备份。
恢复过程花了3 个小时，最后我意识到我必须手动填写很多与业务相关的信息。
这是一个惨痛的教训。
这些方法在我使用的时候是有效的，但无论如何都必须灵活。
例如，有一个项目使用触发器来验证您何时提交。
虽然不能完全消除，但至少可以减少7 0%的错误。
请记住，数据清理不是一次性事件。
应建立持续的监控系统，例如定期运行： 广场 SELECT COUNT() FROM ORDERS WHERE QUANTITY <= 0 OR WHERE customer_id IS NOT (SELECT ID FROMcustomers);
这种类型的 SQL 验证可以发现许多隐藏的问题。

MySQL中误设置的自增列如何删除？通过ALTER TABLE MODIFY COLUMN修复

上周试过这个。

要删除 MySQL 中的自动增量列，请使用 ALTER TABLE MODIFY COLUMN。

例如用户表的id列。

首先确保它是错误的。

使用用户SHOW CREATE TABLE；查看。

如果id列设置为AUTO_INCRMENT，则不应将其用于商业目的。

例如，当您要导入数据时，就会出现问题。

然后使用用户ALTER TABLE MODIFY COLUMN id INT PRIMARY KEY;
删除 AUTO_INCRMENT。

请注意，保留了主键限制。

手术后有几点需要注意。

原来的自我拓展价值依然存在。

但是新数据需要手动填写标识符。

不要随意填写，必须是唯一的。

例如，INSERT INTO user(id,name) VALUES(1 001 ,'Alice');
确保号码 1 001 未被使用。

如果 id 是主键，您将不得不找到另一种方法来创建唯一值。

例如，使用UUID。

或者雪花算法。

或者编写您自己的逻辑生成。

不建议删除重建列。

太麻烦了。

可能会影响外键。

或查看。

容易出错。

预防措施很重要。

启动前检查代码。

检查 ALTER TABLE 语句。

编写自动化测试。

检查表结构是否正确。

限制权限。

不允许对工作库进行意外更改。

完成审批流程。

例如，订购系统。

确定设计特征。

解决使用自增的情况。

无论如何。

删除您自己添加的 ALTER TABLE MODIFY COLUMN。

但请注意，手动保证是唯一的。

必须采取预防措施。

忘记它吧。

如何解决MySQL中字段不显示的问题mysql不显示字段