blocks|key|828674|text|如果您可以为您的用户设置权限，请不要授予删除权限。在某些情况下，这种做法是“必须的”，就像用于统计目的的表一样。因此，如果您的表用于实现其中一个目标，那么这是一种很好的方法。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|828675|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

If you can set the permission for your user(s), don't give the delete permission. There're situations where this practice is a "must be" like the tables for statistics purpose. Thus, if your table is used to achieve one of this goal, it's a good approach.

blocks|key|5090760|text|我认为您应该在应用程序层实现此逻辑，并在放置了no-delete标志的位置插入一列。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|5090761|另一种方法是排除db用户的删除访问权限。|5090762|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

I think you should implement this logic in your application layer and insert a column where you put a no-delete flag.

Another idea would be to exclude the delete access for the db user

blocks|key|825635|text|您可能希望编写一个触发器来检测删除，然后重新插入记录，但这样做可能会出现一些问题，因此您也可以只添加一个外键约束来阻止删除。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|825636|有关这方面的一些讨论，您可以查看：http://rpbouman.blogspot.com/2011/10/mysql-hacks-preventing-deletion-of.html。|offset|length|825637|entityMap|0|LINK|mutability|MUTABLE|url|http://rpbouman.blogspot.com/2011/10/mysql-hacks-preventing-deletion-of.html^0|0|H|24|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@]|9|@$D|P|E|Q|1|R]]|A|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|A|$]]]|G|$H|$5|I|J|K|A|$L|M]]]]

You may want to write a trigger that detects the deletion, then reinserts the record, but there may be some issues with that, so you can also just add a foreign key constraint that will prevent the deletion.

For some discussions on this you can look at: <a href="http://rpbouman.blogspot.com/2011/10/mysql-hacks-preventing-deletion-of.html" rel="nofollow">http://rpbouman.blogspot.com/2011/10/mysql-hacks-preventing-deletion-of.html</a>.

blocks|key|3193119|text|另一种选择是切换到历史表的ARCHIVE引擎。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|3193120|这将防止对表执行任何删除或更新操作，并压缩数据。这种方法的一个(主要)缺点是您不能索引表上的列。|3193121|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.5/en/archive-storage-engine.html^0|D|7|0|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@$A|O|B|P|1|Q]]|C|$]]|$1|D|3|E|5|6|7|R|8|@]|9|@]|C|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|C|$]]]|G|$H|$5|I|J|K|C|$L|M]]]]

One other option is switch to the <a href="http://dev.mysql.com/doc/refman/5.5/en/archive-storage-engine.html" rel="nofollow">ARCHIVE</a> engine for historical tables.

This will prevent any DELETE or UPDATE actions on the table, and compress the data. One (major) downside to this approach is that you cannot index the columns on the table.

blocks|key|825665|text|我使用一个触发器来检测删除，并做一些非法的事情，所以整个操作失败。例如：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|825666|CREATE+TRIGGER+protect_delete+before+delete+ON+protected_table+
FOR+EACH+ROW+UPDATE+non_existing_table+SET+non_existing_column=1;|code-block|syntax|javascript|825667|因此，当有人尝试delete操作时，整个语句将失败。您还可以为non_existing_column和non_existing_table使用更好的名称。|825668|例如，可以获得如下错误消息：|825669|825670|错误1146+(42S02)：表'database.delete_restricted_on_tableX‘不存在|blockquote|825671|825672|825673|编辑:也可以创建更好的失败消息，请查看此处http://www.brokenbuild.com/blog/2006/08/15/mysql-triggers-how-do-you-abort-an-insert-update-or-delete-with-a-trigger/|offset|length|825674|entityMap|0|LINK|mutability|MUTABLE|url|http://www.brokenbuild.com/blog/2006/08/15/mysql-triggers-how-do-you-abort-an-insert-update-or-delete-with-a-trigger/^0|0|0|0|0|0|0|0|0|L|39|0|0^^$0|@$1|2|3|4|5|6|7|12|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|13|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|14|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|15|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|16|8|@]|9|@]|A|$]]|$1|L|3|M|5|N|7|17|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|18|8|@]|9|@]|A|$]]|$1|P|3|-4|5|6|7|19|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|1A|8|@]|9|@$S|1B|T|1C|1|1D]]|A|$]]|$1|U|3|-4|5|6|7|1E|8|@]|9|@]|A|$]]]|V|$W|$5|X|Y|Z|A|$10|11]]]]

I use a trigger that detects the deletion and does some illegal stuff so the whole operation fails. For example like this:

<pre><code>CREATE TRIGGER protect_delete before delete ON protected_table 
FOR EACH ROW UPDATE non_existing_table SET non_existing_column=1;
</code></pre>

So when someone will attempt a delete operation - the whole statement will fail. You also might use better names for non_existing_column and non_existing_table.

E.g it is possible to obtain an error message like this:

<blockquote>
 ERROR 1146 (42S02): Table 'database.delete_restricted_on_tableX'
 doesn't exist
</blockquote>

EDIT: also it is possible to create even better fail messages, please check here <a href="http://www.brokenbuild.com/blog/2006/08/15/mysql-triggers-how-do-you-abort-an-insert-update-or-delete-with-a-trigger/" rel="nofollow noreferrer">http://www.brokenbuild.com/blog/2006/08/15/mysql-triggers-how-do-you-abort-an-insert-update-or-delete-with-a-trigger/</a>

Once a record is entered in a particular table, I think I don't ever want it to be deleted. The records are there to keep track of things for historical purposes. I'm using MySQL, InnoDB. Is there any way I can protect that table from accidental deletes by the application layer? If so, is this a good approach?

MySQL: how to prevent deletion?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

一旦在特定表中输入了一条记录，我想我永远不会希望它被删除。这些记录的存在是为了记录历史上的事情。我用的是MySQL，InnoDB。有什么方法可以防止应用层意外删除该表吗？如果是这样的话，这是一个好的方法吗？

问MySQL:如何防止删除？
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问MySQL:如何防止删除？EN