Mysql allows the creation of tables per session, also called temporary tables. these TEMPORARY tables are visible only for the current session and are dropped automatically once the session ends.
Two different sessions can create a temporary table with the same name without conflict.
DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb;
USE AppleDb;
CREATE TEMPORARY TABLE apple (apple_id BIGINT, apple_name VARCHAR(20), available CHAR(1), available_date DATE, PRIMARY KEY (apple_id));
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (1, "Macintosh", 'Y', '2022-01-1');
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (2,"Fuji", 'Y', '2022-02-2');
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (3,"Gala", 'Y', '2022-03-3');
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (4,"Jonagold", 'N', '2022-04-4');
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (5,"GrannySmith", 'Y', '2022-05-5');
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (6,"PinkLady", 'N', '2022-06-6');
select * from AppleDb.apple;A temporary table requires a database but is not dependent on the database, ie dropping a database doesn't drop the Temporary table created within the database.
DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb;
USE AppleDb;
CREATE TEMPORARY TABLE apple (apple_id BIGINT, apple_name VARCHAR(20), available CHAR(1), available_date DATE, PRIMARY KEY (apple_id));
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (1, "Macintosh", 'Y', '2022-01-1');
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (2,"Fuji", 'Y', '2022-02-2');
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (3,"Gala", 'Y', '2022-03-3');
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (4,"Jonagold", 'N', '2022-04-4');
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (5,"GrannySmith", 'Y', '2022-05-5');
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (6,"PinkLady", 'N', '2022-06-6');
DROP DATABASE AppleDb;
select * from apple;temporary tables can be created using the schema of another table as well.
DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb;
USE AppleDb;
CREATE TABLE apple (apple_id BIGINT, apple_name VARCHAR(20), available CHAR(1), available_date DATE, PRIMARY KEY (apple_id));
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (1, "Macintosh", 'Y', '2022-01-1');
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (2,"Fuji", 'Y', '2022-02-2');
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (3,"Gala", 'Y', '2022-03-3');
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (4,"Jonagold", 'N', '2022-04-4');
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (5,"GrannySmith", 'Y', '2022-05-5');
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (6,"PinkLady", 'N', '2022-06-6');
CREATE TEMPORARY TABLE apple_temp SELECT * FROM apple limit 4;Temporary tables are deleted automatically as the session ends, but if required these tables can be manually dropped as well. drop command automatically detects whether the table is a temporary table or a TEMPORARY prefix and makes clear its a TEMPORARY table.
DROP DATABASE IF EXISTS AppleDb;
CREATE DATABASE AppleDb;
USE AppleDb;
CREATE TABLE apple (apple_id BIGINT, apple_name VARCHAR(20), available CHAR(1), available_date DATE, PRIMARY KEY (apple_id));
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (1, "Macintosh", 'Y', '2022-01-1');
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (2,"Fuji", 'Y', '2022-02-2');
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (3,"Gala", 'Y', '2022-03-3');
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (4,"Jonagold", 'N', '2022-04-4');
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (5,"GrannySmith", 'Y', '2022-05-5');
INSERT INTO apple (apple_id, apple_name, available, available_date ) VALUES (6,"PinkLady", 'N', '2022-06-6');
CREATE TEMPORARY TABLE apple_temp SELECT * FROM apple;
DROP TABLE apple_temp;
DROP TEMPORARY TABLE apple_temp;
