有什么办法可以在Go中使用MySQL临时表？

Question

I have stored procedures that create temp tables. I would like to then execute a query that joins with these temp tables.

The problem is that with Golang's database/sql design, the only way to ensure you get the same connection for subsequent queries is to create a transaction.

Am I asking for trouble if I wrap the majority of my SELECTs in a transaction for the purpose of accessing a temp table? I understand that I will lose some performance/scalability because I'll be holding onto connections from the pool rather than allowing them to go back between queries. But I'm wondering if I'll start seeing locking or other serious issues with this strategy.

The reason I need to do this is because the MySQL execution plan for many of my tables is very poor (I'm doing several joins across large tables). I'd like to execute some intermediate queries and store their results in temp tables to avoid this issue.

Answer 1

You can create your own pseudo temp tables that can be accessed by multiple processes, and connections.

The idea is to simply create memory tables, run your operations, and cleanup afterwards.

You can create a memory table with the following sql;

CREATE TABLE mydb.temp_32rfd293 (
  id int(11) auto_increment,
  content varchar(50),
  PRIMARY KEY  (`id`)
) ENGINE=MEMORY;

Do something useful, then drop it using;

DROP TABLE temp_32rfd293:

Scheduled event to remove mydb.temp_% tables older than 1 day

You'll want to clean up the occasional abandoned temp table, you can create a scheduled event in mysql to do this. If you choose to do this consider using a dedicated schema for temp tables to prevent accidental removals.

Note: You need event_scheduler=ON in your my.ini for this to work.

DELIMITER $$

CREATE
  EVENT `cleanup_custom_temps`
  ON SCHEDULE EVERY 1 DAY STARTS '2000-01-01 01:00:00'
  DO BEGIN


  ---------------------------------------------------
  -- Process to delete all tables with
  -- prefix 'temp_', and older than 1 day
  SET @tbls = (
    SELECT GROUP_CONCAT(TABLE_NAME)
      FROM information_schema.TABLES
      WHERE TABLE_SCHEMA = 'mydb'
        AND TABLE_NAME LIKE 'temp_%'
          AND CREATE_TIME < NOW() - INTERVAL 1 DAY
  );
  SET @delStmt = CONCAT('DROP TABLE ',  @tbls);
  PREPARE stmt FROM @delStmt;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
  ---------------------------------------------------

  END */$$

DELIMITER ;