Are MySQL in-memory tables global?

Question

I have a MySQL stored routine where I'd like to use a temporary data table to store intermediate results. To avoid having to create a "normal" table, I want to use an in-memory table that goes away when routine execution has completed. I'm a MySQL newbie, but I suppose this is the way to go:

DROP TABLE IF EXISTS MyInMemoryTable;
CREATE TABLE MyInMemoryTable ( numberField int ) ENGINE = MEMORY;
...
DROP TABLE IF EXISTS MyInMemoryTable;

My routine works for a single user, but I am nervous about what happens if multiple users run the routine at the same time?

Is MyInMemoryTable local to each user session executing the routine, so there can be many instances of this table (without conflicts) at the same time? Or is MyInMemoryTable a single global table for all users, thereby risking different users' data getting mixed-up in the table? Or will one user risk getting a database error if another user is busy running the routine?

To me, the documentation is confusing: It says the MEMORY engine associates each table with a disk file, but also that "MEMORY tables are never converted to disk tables."

Have I misunderstood MySQL in-memory tables? Perhaps there's a better approach?

Willams

Answer 1

Create a temporary table.

CREATE TEMPORARY TABLE ...;

Those tables aren't shared and are deleted when your connection ends.

The standard in memory tables are probably shared and global.

Maffick

Oh, that's what I suspected. I got it all wrong. Thanks for helping! – Willams

Answer 2

These are two different concepts: table engine and table temporary property. In theory, a table with any engine can be temporary (InnoDB, MyISAM, Memory). Memory engine is perhaps least understood storage engine in MySQL. Under the hood, it's an incrementally growing hash map. So, yes, in-memory tables can be session local - but they have to be temporary for that:

mysql> show create table tmp; CREATE TEMPORARY TABLE `tmp` (   `a` int(11) DEFAULT NULL ) ENGINE=MEMORY DEFAULT CHARSET=latin1 | 1 row in set (0.00 sec)

mysql> drop table tmp; Query OK, 0 rows affected (0.01 sec)

mysql> show create table tmp; CREATE TABLE `tmp` (   `example` int(11) NOT NULL,   `id` int(11) NOT NULL AUTO_INCREMENT,   PRIMARY KEY (`example`),   UNIQUE KEY `id` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=latin1 |

Kilgore