是一个“黑洞”吗？表恶？
阅读这个问题我刚刚了解到黑洞的存在< /code> 表技巧：基本上包括使用单个表插入数据，然后使用触发器将数据拆分到许多其他表中。
我想知道一旦从事该项目的开发人员意识到这一点，这是否会导致问题。
这种技术的优点和缺点是什么？？
编辑： 当我看到这个例子时，我想到的眨眼是关于交易的：如果由于某种原因交易失败，你会发现带有原始数据的 blackhole 行，用于历史目的，也许还有帮助通过调试-但这似乎是我在黑洞中能看到的唯一+1。有想法吗？
Reading to this question i've just learned the existence of the blackhole table trick: basically consist in using a single table to insert data, and then a trigger that split the data in many other tables.
Im wondering if this could cause problems, once the developers whos working on the project are aware of that.
What are the pro and cons of this tecnique?
Edit:
The blink I got in mind when I saw the example, is about transactions: if for some reason the transaction fail, you'll find the blackhole row with the original data, for historical purpose and maybe a help with debug - but this seems to be the only +1 i can see with blackholes. Ideas?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（8）
我不认为黑洞有任何真正的优点。
编写触发器代码来移动数据可能并不比编写代码将数据插入到正确的位置要少很多工作。
正如 Christian Oudard 所写，它并没有降低复杂性 - 只是将其移至真正难以调试的地方。
缺点是：
“副作用”在软件开发中通常是一个坏主意。触发器是副作用 - 我打算做一件事（在表中插入数据），但它实际上做了很多其他事情。现在，当我调试代码时，我也必须将所有副作用牢记在心——而且副作用本身可能会产生副作用。
大多数软件在维护上花费的时间远远多于在开发上花费的时间。将新的开发人员引入团队并解释黑洞技巧可能会增加学习曲线 - 收益可以忽略不计（在我看来）。
因为触发器是副作用，并且如果不小心的话，相对容易引发大量触发器，所以我一直尝试在不依赖触发器的情况下设计数据库；如果触发器显然是正确的方法，我只让最有经验的开发人员创建它们。黑洞技巧使触发器成为一种正常、常规的工作方式。当然，这是个人观点。
I don't think blackhole has any real pros.
Writing the trigger code to move data around is probably not noticably less work than writing the code to insert the data in the right place in the first place.
As Christian Oudard writes, it doesn't reduce complexity - just moves it to a place where it's really hard to debug.
On the downside:
"Side effects" are usually a bad idea in software development. Triggers are side effects - I intend to do one thing (insert data in a table), and it actually does lots of other things. Now, when I'm debugging my code, I have to keep all the side effects in my head too - and the side effects could themselves have side effects.
most software spends far more time in maintenance than it does in development. Bringing new developers into the team and explaining the black hole trick is likely to increase the learning curve - for negligible benefit (in my view).
Because triggers are side effects, and it's relatively easy to set off a huge cascade of triggers if you're not careful, I've always tried to design my databases without a reliance on triggers; where triggers are clearly the right way to go, I've only let my most experienced developers create them. The black hole trick makes triggers into a normal, regular way of working. This is a personal point of view, of course.
提示您的原始问题并没有触及MySQL“黑洞”的核心。
什么是黑洞？
在 MySQL 中，
BLACKHOLE是一个 存储引擎简单地丢弃插入其中的所有数据，类似于空设备。使用此后端的原因有很多，但它们往往有点深奥：请参阅 文档 和 此处 和 此处。
例如，测量二进制日志记录的开销，而不用担心存储引擎开销
请参阅此处。
如果您不知道为什么需要伪装成表的数据接收器，请不要使用它。
你问的技术是什么？
考虑中的使用似乎是：
因此，“邪恶”或优点/缺点问题的答案与可插入/可更新视图的这些问题的答案相同（实现的常见方法#1)、基于触发器的审计日志记录（大多数人如何做#2）和一般行为覆盖/对抗（有多种方法可以实现#3）。
那么，答案是什么呢？
答案当然是“有时这些技术是合适的，有时是不合适的”。 :) 你知道你为什么这样做吗？应用程序是否更适合此功能？抽象是否太脆弱、太漏洞、太僵化等等？
The original question that prompted yours does not get at the heart of MySQL's "blackholes."
What is a BLACKHOLE?
In MySQL-speak,
BLACKHOLEis a storage engine that simply discards all data INSERTed into it, analogous to a null device. There are a number of reasons to use this backend, but they tend to be a bit abstruse:See the docs, and here and here.
E.g., measuring the overhead of binary logging without worrying about storage engine overhead
See here.
If you don't know why you need a data sink masquerading as a table, don't use it.
What is the technique you are asking about?
The use under consideration seems to be to:
Thus the answer to the question of "evilness" or pros/cons is the same as the answer to those questions for insertable/updatable VIEWs (the common way to implement #1), trigger-based audit logging (how most people do #2) and behavioral overrides/counteractions generally (there are a number of ways to accomplish #3).
So, what is the answer?
The answer is, of course, "sometimes these techniques are appropriate and sometimes not." :) Do you know why you're doing it? Is the application a better place for this functionality? Is the abstraction too brittle, too leaky, too rigid, etc.?
这看起来不是一个好主意。如果您想保持前端代码简单，为什么不直接使用存储过程呢？如果不是为了保持前端代码简单，我根本不明白这一点。
This doesn't look like a good idea. If you're trying to keep the front end code simple, why not just use a stored procedure? If it's not to keep the front end code simple, I don't understand the point at all.
有趣的是，我今天也了解到了黑洞的存在。
可以说，这里的问题实际上是一个更广泛的问题，即业务逻辑是否应该嵌入到数据库触发器中。在这种情况下，黑洞表本质上被用作黑洞表上的触发器可以使用的瞬态数据存储。应该首先使用触发器吗？对我来说，这才是问题的实质。
我个人认为触发器的使用应该仅限于日志记录和 DBA 特定的任务，并且不应包含应牢牢属于应用程序层的业务逻辑（或任何与此相关的逻辑）。似乎有很多关于数据库触发器是否邪恶的观点。我认为你的问题也属于这一类。
在数据库触发器中嵌入应用程序层逻辑可能存在风险。
最终可能会在应用程序之间分割业务逻辑
代码和数据库。这确实会让人非常困惑
有人试图支持并深入研究代码库。
如果触发器和存储过程中的逻辑过多，那么数据库服务器上很容易出现性能问题，这些问题本可以通过在应用程序之间分配繁重的处理任务（即复杂的业务逻辑）来解决。服务器并让数据库服务器自由用于其主要目的，即提供数据。
当然只是我的两块钱！
Funnily enough I learnt about the existence of blackholes today too.
Arguably the question here is actually a broader one i.e. whether or not business logic should be embedded in database triggers or not. In this instance the blackhole table is essentially being used as a transient data store that the trigger on the blackhole table can make use of. Should the trigger be used in the first place? To me that is the real meat of the question.
Personally I feel that the use of triggers should be restricted to logging and DBA-specific tasks only and should not contain business logic (or any logic for that matter) that should belong firmly in the application layer. It appears as though there have been quite a few opinions expressed about whether database triggers are evil or not. I think your question kinda falls into that category too.
Embedding application layer logic in database triggers can be risky.
It is likely to end up splitting business logic between application
code and the database. This can be very confusing indeed for
somebody trying to support and get their head into a code base.
If you end up with too much logic in triggers, and indeed stored procedures, you can easily end up with performance issues on your database server that could have, indeed should have been addressed by distributing the heavy duty processing tasks i.e. complex business logic among application servers and leaving the database server free for its primary purpose i.e. serving data.
Just my two bits' worth of course!
每次向表中插入一行时，很可能您正在写入硬盘驱动器的同一区域或同一页面（在 MS-SQL 世界中，我不了解 postgresql），因此该技术可能会导致争用和锁定，因为所有事务现在都在竞争写入同一个表。
此外，这也会使刀片性能减半，因为刀片需要两个刀片而不是一个。
这是非规范化，因为现在有两个数据副本而不是一个。
Each time you insert a row into a table, the odds are that you are writing to the same area of the hard drive or the same page (in MS-SQL world, I don't know about postgresql), so this technique will likely lead to contention and locking as all transactions are now competing to write to the same table.
Also this will halve insert performance since inserts require two inserts instead of one.
And this is denormalization since there are now two copies of the data instead of one.
请不要这样做。这并没有降低复杂性，只是移动了它。这种逻辑属于应用层，您可以使用 PHP、Python 或 Ruby 等更好的语言来实现它。
Please don't do this. This doesn't reduce complexity, it just moves it around. This sort of logic belongs in the application layer, where you can use a nicer language like PHP, Python, or Ruby to implement it.
不要这样做。事实上，它被称为一个技巧，而不是做某事的标准方法，这对我来说已经足够了。
这完全破坏了关系模型的正常使用模式。不确定它是否真的会杀死正常形式，因为您仍然可以将其全部就位。它只是扰乱了数据到达目标表的方式。看起来就像维护噩梦之上的性能噩梦。例如，想象一个表有一个触发器，必须触发 1,800 多次表插入。那只会让我感到恶心。
这是一个有趣的客厅把戏而已。
Don't do this. The fact that it's called a trick and not a standard way of doing something says enough for me.
This totally kills the normal usage pattern of the relational model. Not sure that it actually kills normal form as you can still have that all in place. It's just messing with the way data is making it to the destination tables. Looks like a performance nightmare on top of a maintenance nightmare. Imagine one table having a trigger that has to fire for 1,800 plus table inserts for example. That just makes me feel sick.
This is a interesting parlor trick nothing more.
我认为这会非常慢，因为无法利用“批量插入”的优点。
I would suppose that this would be quite slow, as the advantages of "bulk inserts" cannot be used.