1
drop table if exists t1;
2
set @@session.max_heap_table_size=16*1024*1024*24;
3
create temporary table t1 (a int not null, b int, c varchar(400), d varchar(400), primary key (a), key (b)) engine=MEMORY comment="testing heaps";
4
show table status like "t1";
5
Session Schema Name Type Engine Version Rows Avg_row_length Table_size Auto_increment
6
# test t1 TEMPORARY MEMORY # # # # #
7
insert into t1 values (1,1,'012',NULL), (2,2,'0123456789',NULL), (3,3,'012345678901234567890123456789',NULL), (4,4,NULL,'0123456789012345678901234567890123456789012345678901234567890123456789');
12
3 3 012345678901234567890123456789 NULL
13
4 4 NULL 0123456789012345678901234567890123456789012345678901234567890123456789
14
delete from t1 where a = 3;
19
4 4 NULL 0123456789012345678901234567890123456789012345678901234567890123456789
20
insert into t1 values (5,5,NULL,'0123'), (6,6,NULL,'0123');
26
4 4 NULL 0123456789012345678901234567890123456789012345678901234567890123456789
28
update t1 set c = '012345678901234567890123456789' where a = 2;
32
2 2 012345678901234567890123456789 NULL
34
4 4 NULL 0123456789012345678901234567890123456789012345678901234567890123456789
36
update t1 set c = '0123456789' where a = 2;
42
4 4 NULL 0123456789012345678901234567890123456789012345678901234567890123456789
44
insert into t1 values (7,7,'0123',NULL), (8,8,'0123',NULL);
50
4 4 NULL 0123456789012345678901234567890123456789012345678901234567890123456789
57
select count(*) from t1;
60
insert into t1 values (100000,100000,NULL,'0123'), (100000,100000,NULL,'0123');
61
ERROR 23000: Duplicate entry '100000' for key 'PRIMARY'
62
show table status like "t1";
63
Session Schema Name Type Engine Version Rows Avg_row_length Table_size Auto_increment
64
# test t1 TEMPORARY MEMORY # # # # #
65
select count(*) from t1;
68
set @@session.max_heap_table_size=default;