如果需要的话,MySQL 存储过程要 INSERT DELAYED 但先 CREATE TABLE 吗?

发布于 2024-09-05 22:36:08 字数 183 浏览 6 评论 0原文

我计划在 MyISAM 表中执行大量 INSERT DELAYED 操作。但这些表可能还不存在。假设每天都会有一个新表。

因此,与其检测客户端中是否存在表并创建然后重试插入,这似乎是存储过程(“函数”)的一个好例子。

这可能吗?它会是什么样子? 这种方法有什么缺点吗?

I'm planning on doing a lot of INSERT DELAYED into MyISAM tables. But the tables might not exist yet. Let's say e.g. a new table will exist for each day.

So instead of detecting absence of table in my client and creating then retrying the insert, it seems like a good case for a stored procedure ("function").

Is this possible, and what would it look like?
Are there any downsides to this approach?

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(3

酷炫老祖宗 2024-09-12 22:36:08

每次插入时检查表是否存在似乎是浪费时间。如果您需要日常表(在我看来,它们违背了数据库中通常存在的大部分逻辑,因此我会尽量避免使用它们):

  1. 创建默认表以插入“my_logging_table” 。
  2. 在该表上执行所有插入/更新/删除操作。
  3. 运行每日 cronjob 来创建昨天的表 ('my_logging_table-2010-06-15'),并在其中插入昨天的所有记录,然后将它们从主表中删除。
  4. 如果这对您的应用程序来说需要重写,您可以使用当前表名+日期(“my_logging_table-2010-06-16”)创建一个可更新视图,该视图仅插入“my_logging_table”

另一种方法是合并表(参见http://dev.mysql.com/ doc/refman/5.0/en/merge-storage-engine.html),每日 cronjob 创建“今天”表和使用 alter table 语句更新合并表的表列表。

It seems a waste of time to check with every single insert whether the table exist. If you need day by day tables (in my opinion they defy much of the logic usually found in databases, so I try to avoid them, I'd):

  1. Create the default table to insert 'my_logging_table'.
  2. Do all inserts / updates / deletes on that table.
  3. Run a daily cronjob to create the table of yesterday ('my_logging_table-2010-06-15'), and inserting all of yesterdays records in it, after which they're deleted from the main table.
  4. If that is to much of a rewrite for your application you could make an updatable view with the current tablename+day ('my_logging_table-2010-06-16'), which just inserts in the 'my_logging_table'

Another approach would be a MERGE table (see http://dev.mysql.com/doc/refman/5.0/en/merge-storage-engine.html), with a daily cronjob creating the 'today' table & updating the tablelist for the merge table with an alter table statement.

一身软味 2024-09-12 22:36:08

创建一个每天晚上午夜之前运行的计划事件并创建第二天的日志表不是一个好主意吗?这样您就可以始终依赖在发生任何 INSERT 之前创建的表?

CREATE EVENT createLogTable
ON SCHEDULE EVERY 1 DAY  
STARTS '2010-6-20 23:59:00'   
DO
    BEGIN
        SET @tablename = CONCAT('my_logging_table-',DATE(DATE_ADD(NOW(),INTERVAL 1 DAY)));
        SET @query = CONCAT('CREATE TABLE IF NOT EXISTS`',@tablename,'` (`timestamp` INT,`event` VARCHAR( 20 )) ENGINE = MYISAM');
        PREPARE createStatement FROM @query;
        EXECUTE createStatement;
        DEALLOCATE PREPARE createStatement;
    END

Wouldn't it be an idea to create a scheduled event that runs every night before midnight, and create the log-table for the next day. That way you could always rely on the table beeing created before any INSERT would occur?

CREATE EVENT createLogTable
ON SCHEDULE EVERY 1 DAY  
STARTS '2010-6-20 23:59:00'   
DO
    BEGIN
        SET @tablename = CONCAT('my_logging_table-',DATE(DATE_ADD(NOW(),INTERVAL 1 DAY)));
        SET @query = CONCAT('CREATE TABLE IF NOT EXISTS`',@tablename,'` (`timestamp` INT,`event` VARCHAR( 20 )) ENGINE = MYISAM');
        PREPARE createStatement FROM @query;
        EXECUTE createStatement;
        DEALLOCATE PREPARE createStatement;
    END
红墙和绿瓦 2024-09-12 22:36:08

你可以制定一个好的计划来实现同样的目标。

例如,如果您有一个每天都会创建的表“Logged_users”,您可以将一列附加到用户表中,或者创建一个包含 ID_USER, DAY 列的额外表(这将是一个更好的选择)。然后,您可以编写一个 CRON 作业来删除过去几天的每一行,并在每天中午 12:00 执行该作业。

You may make a good scheme to accomplish the same.

For example, if you have a table "Logged_users", which will be created every day, you could just append a column to the users table, or make an extra table with ID_USER, DAY columns (that would be a better choice). Then, you may program a CRON job to delete every rows of past days, and execute that job every day at 12:00 am.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文