create temporary table t1 (i int) engine = innodb;
insert into t1 values (1), (2), (3), (4);
select * from t1;
i
1
2
3
4
select * from t1 where i = 4;
i
4
drop table t1;
create temporary table t1 (i int) engine = innodb;
insert into t1 values (1), (2), (3), (4);
select * from t1;
i
1
2
3
4
select * from t1 where i = 4;
i
4
drop table t1;
create temporary table t2 (i int) engine = innodb;
insert into t2 values (1), (2), (3), (4);
select * from t2;
i
1
2
3
4
select * from t2 where i = 4;
i
4
drop table t2;
create temporary table t1
(keyc int, c1 char(100), c2 char(100),
primary key(keyc)) engine = innodb;
create procedure populate_t1()
begin
declare i int default 1;
while (i <= 200) DO
insert into t1 values (i, 'a', 'b');
set i = i + 1;
end while;
end|
set autocommit=0;
select count(*) from t1;
count(*)
0
call populate_t1();
select count(*) from t1;
count(*)
200
select * from t1 limit 10;
keyc	c1	c2
1	a	b
2	a	b
3	a	b
4	a	b
5	a	b
6	a	b
7	a	b
8	a	b
9	a	b
10	a	b
set autocommit=1;
truncate table t1;
select count(*) from t1;
count(*)
0
drop table t1;
create temporary table t1 (i int) engine = innodb;
insert into t1 values (1), (2), (3), (4);
select * from t1;
i
1
2
3
4
select * from t1 where i = 4;
i
4
drop table t1;
create temporary table t1
(keyc int, c1 char(100), c2 char(100),
primary key(keyc))
engine = innodb;
begin;
select count(*) from t1;
count(*)
0
call populate_t1();
select count(*) from t1;
count(*)
200
rollback;
select count(*) from t1;
count(*)
0
begin;
call populate_t1();
commit;
select count(*) from t1;
count(*)
200
truncate table t1;
select count(*) from t1;
count(*)
0
drop table t1;
drop procedure populate_t1;
create temporary table t1 (t1_i int, t1_f float) engine = innodb;
insert into t1 values (1, 1.1), (2, 2.2), (3, 2.2), (4, 4.4);
explain select * from t1 where t1_i = 1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	Using where
alter table t1 add unique index pri_index(t1_i);
explain select * from t1 where t1_i = 1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	const	pri_index	pri_index	5	const	1	
select * from t1 where t1_i = 1;
t1_i	t1_f
1	1.1
alter table t1 add unique index sec_index(t1_f);
ERROR 23000: Duplicate entry '2.2' for key 'sec_index'
alter table t1 add index sec_index(t1_f);
explain select * from t1 where t1_f > 2.2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	sec_index	NULL	NULL	NULL	4	Using where
select * from t1 where t1_f > 2.2;
t1_i	t1_f
2	2.2
3	2.2
4	4.4
alter table t1 add column (t1_c char(10));
select * from t1;
t1_i	t1_f	t1_c
1	1.1	NULL
2	2.2	NULL
3	2.2	NULL
4	4.4	NULL
insert into t1 values (5, 5.5, 'krunal');
alter table t1 drop column t1_f;
show create table t1;
Table	Create Table
t1	CREATE TEMPORARY TABLE `t1` (
  `t1_i` int(11) DEFAULT NULL,
  `t1_c` char(10) DEFAULT NULL,
  UNIQUE KEY `pri_index` (`t1_i`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
select * from t1 where t1_f > 2.2;
ERROR 42S22: Unknown column 't1_f' in 'where clause'
alter table t1 add index sec_index2(t1_c), algorithm=inplace;
ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY
drop table t1;
create temporary table t1 (i int, f float) engine = innodb;
insert into t1 values (10, 1.1), (20, 2.2);
select * from t1;
i	f
10	1.1
20	2.2
alter table t1 discard tablespace;
ERROR HY000: Cannot DISCARD/IMPORT tablespace associated with temporary table
alter table t1 import tablespace;
ERROR HY000: Cannot DISCARD/IMPORT tablespace associated with temporary table
drop table t1;
create temporary table t1 (i int) engine=innodb;
insert into t1 values (1), (2), (3);
select * from t1;
i
1
2
3
alter table t1 rename t2;
select * from t1;
ERROR 42S02: Table 'test.t1' doesn't exist
select * from t2;
i
1
2
3
insert into t2 values (1), (2), (6), (7);
select * from t2;
i
1
2
3
1
2
6
7
drop table t2;
SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
create temporary table t (
a int not null,
b blob not null,
index sk (b(3021))
) row_format = dynamic engine=innodb;
drop table t;
create temporary table t (
a int not null,
b blob not null,
index sk (b(3021))
) row_format = dynamic engine=innodb;
drop table t;
create temporary table t (
a int not null,
b blob not null,
index sk (b(3021))
) row_format = dynamic engine=innodb;
drop table t;
SET innodb_strict_mode=OFF;
create temporary table t (
a int not null,
b blob not null,
index sk (b(3021))
) row_format = compact engine=innodb;
ERROR HY000: Index column size too large. The maximum column size is 767 bytes
create temporary table t (
a int not null,
b blob not null,
index sk (b(3021))
) row_format = dynamic engine=innodb;
drop table t;
create temporary table t (
a int not null,
b blob not null,
index sk (b(3021))
) row_format = compressed engine=innodb;
drop table t;
create temporary table t (
a int not null,
b blob not null,
index sk (b(3021))
) row_format = compact engine=innodb;
ERROR HY000: Index column size too large. The maximum column size is 767 bytes
create temporary table t (
a int not null,
b blob not null,
index sk (b(3021))
) row_format = dynamic engine=innodb;
drop table t;
CREATE TABLE t1 ( i INT ) ENGINE = Innodb;
CREATE TEMPORARY TABLE t2 ( i INT ) ENGINE = Innodb;
SELECT COUNT(*) FROM information_schema.INNODB_SYS_DATAFILES WHERE PATH LIKE '%test%t_';
COUNT(*)
0
SELECT COUNT(*) FROM information_schema.INNODB_SYS_TABLES WHERE NAME LIKE '%test%t_';
COUNT(*)
1
CREATE TEMPORARY table t3 ( i INT ) ENGINE = Innodb;
SELECT COUNT(*) FROM information_schema.INNODB_SYS_DATAFILES WHERE PATH LIKE '%test%t_';
COUNT(*)
0
SELECT COUNT(*) FROM information_schema.INNODB_SYS_TABLES WHERE NAME LIKE '%test%t_';
COUNT(*)
1
DROP TABLE t1,t2,t3;
SELECT COUNT(*) FROM information_schema.INNODB_SYS_DATAFILES WHERE PATH LIKE '%test%t_';
COUNT(*)
0
SELECT COUNT(*) FROM information_schema.INNODB_SYS_TABLES WHERE NAME LIKE '%test%t_';
COUNT(*)
0