heap.result 18.8 KB
Newer Older
1
drop table if exists t1,t2,t3;
unknown's avatar
unknown committed
2
create table t1 (a int not null,b int not null, primary key (a)) engine=heap comment="testing heaps" avg_row_length=100 min_rows=1 max_rows=100;
unknown's avatar
unknown committed
3 4 5
insert into t1 values(1,1),(2,2),(3,3),(4,4);
delete from t1 where a=1 or a=0;
show keys from t1;
6
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment
7
t1	0	PRIMARY	1	a	NULL	3	NULL	NULL		HASH	
unknown's avatar
unknown committed
8
select * from t1;
9 10 11 12
a	b
2	2
3	3
4	4
unknown's avatar
unknown committed
13
select * from t1 where a=4;
14 15
a	b
4	4
unknown's avatar
unknown committed
16 17 18 19
update t1 set b=5 where a=4;
update t1 set b=b+1 where a>=3;
replace t1 values (3,3);
select * from t1;
20 21 22 23
a	b
2	2
3	3
4	6
unknown's avatar
unknown committed
24 25
alter table t1 add c int not null, add key (c,a);
drop table t1;
unknown's avatar
unknown committed
26
create table t1 (a int not null,b int not null, primary key (a)) engine=memory comment="testing heaps";
unknown's avatar
unknown committed
27 28 29 30 31
insert into t1 values(1,1),(2,2),(3,3),(4,4);
delete from t1 where a > 0;
select * from t1;
a	b
drop table t1;
unknown's avatar
unknown committed
32
create table t1 (a int not null,b int not null, primary key (a)) engine=heap comment="testing heaps";
unknown's avatar
unknown committed
33
insert into t1 values(1,1),(2,2),(3,3),(4,4);
unknown's avatar
unknown committed
34
alter table t1 modify a int not null auto_increment, engine=myisam, comment="new myisam table";
unknown's avatar
unknown committed
35
select * from t1;
36 37 38 39 40
a	b
1	1
2	2
3	3
4	4
unknown's avatar
unknown committed
41
drop table t1;
unknown's avatar
unknown committed
42
create table t1 (a int not null) engine=heap;
43
insert into t1 values (869751),(736494),(226312),(802616),(728912);
unknown's avatar
unknown committed
44
select * from t1 where a > 736494;
45 46 47
a
869751
802616
unknown's avatar
unknown committed
48 49
alter table t1 add unique uniq_id(a);
select * from t1 where a > 736494;
50 51 52
a
869751
802616
unknown's avatar
unknown committed
53
select * from t1 where a = 736494;
54 55
a
736494
unknown's avatar
unknown committed
56
select * from t1 where a=869751 or a=736494;
57 58 59
a
736494
869751
unknown's avatar
unknown committed
60
select * from t1 where a in (869751,736494,226312,802616);
61 62 63 64 65
a
226312
736494
802616
869751
unknown's avatar
unknown committed
66
alter table t1 engine=myisam;
unknown's avatar
unknown committed
67
explain select * from t1 where a in (869751,736494,226312,802616);
unknown's avatar
unknown committed
68
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
69
1	SIMPLE	t1	range	uniq_id	uniq_id	4	NULL	4	Using where; Using index
unknown's avatar
unknown committed
70
drop table t1;
71
create table t1 (x int not null, y int not null, key x (x), unique y (y))
unknown's avatar
unknown committed
72
engine=heap;
unknown's avatar
unknown committed
73 74
insert into t1 values (1,1),(2,2),(1,3),(2,4),(2,5),(2,6);
select * from t1 where x=1;
75 76 77
x	y
1	3
1	1
unknown's avatar
unknown committed
78
select * from t1,t1 as t2 where t1.x=t2.y;
79 80 81 82 83 84 85
x	y	x	y
1	1	1	1
2	2	2	2
1	3	1	1
2	4	2	2
2	5	2	2
2	6	2	2
unknown's avatar
unknown committed
86
explain select * from t1,t1 as t2 where t1.x=t2.y;
unknown's avatar
unknown committed
87
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
88
1	SIMPLE	t1	ALL	x	NULL	NULL	NULL	6	
89
1	SIMPLE	t2	eq_ref	y	y	4	test.t1.x	1	
unknown's avatar
unknown committed
90
drop table t1;
unknown's avatar
unknown committed
91
create table t1 (a int) engine=heap;
unknown's avatar
unknown committed
92 93
insert into t1 values(1);
select max(a) from t1;
94 95
max(a)
1
unknown's avatar
unknown committed
96
drop table t1;
unknown's avatar
unknown committed
97
CREATE TABLE t1 ( a int not null default 0, b int not null default 0,  key(a),  key(b)  ) ENGINE=HEAP;
unknown's avatar
unknown committed
98 99
insert into t1 values(1,1),(1,2),(2,3),(1,3),(1,4),(1,5),(1,6);
select * from t1 where a=1;
100 101 102 103 104 105 106
a	b
1	6
1	5
1	4
1	3
1	2
1	1
unknown's avatar
unknown committed
107 108
insert into t1 values(1,1),(1,2),(2,3),(1,3),(1,4),(1,5),(1,6);
select * from t1 where a=1;
109 110 111 112 113 114 115 116 117 118 119 120 121
a	b
1	6
1	5
1	4
1	3
1	2
1	1
1	6
1	5
1	4
1	3
1	2
1	1
unknown's avatar
unknown committed
122
drop table t1;
unknown's avatar
unknown committed
123
create table t1 (id int unsigned not null, primary key (id)) engine=HEAP;
unknown's avatar
unknown committed
124 125
insert into t1 values(1);
select max(id) from t1;
126 127
max(id)
1
unknown's avatar
unknown committed
128 129
insert into t1 values(2);
select max(id) from t1;
130 131
max(id)
2
unknown's avatar
unknown committed
132 133
replace into t1 values(1);
drop table t1;
unknown's avatar
unknown committed
134
create table t1 (n int) engine=heap;
unknown's avatar
unknown committed
135
drop table t1;
unknown's avatar
unknown committed
136
create table t1 (n int) engine=heap;
unknown's avatar
unknown committed
137 138
drop table if exists t1;
CREATE table t1(f1 int not null,f2 char(20) not 
unknown's avatar
unknown committed
139
null,index(f2)) engine=heap;
unknown's avatar
unknown committed
140 141 142 143 144 145 146 147 148 149 150
INSERT into t1 set f1=12,f2="bill";
INSERT into t1 set f1=13,f2="bill";
INSERT into t1 set f1=14,f2="bill";
INSERT into t1 set f1=15,f2="bill";
INSERT into t1 set f1=16,f2="ted";
INSERT into t1 set f1=12,f2="ted";
INSERT into t1 set f1=12,f2="ted";
INSERT into t1 set f1=12,f2="ted";
INSERT into t1 set f1=12,f2="ted";
delete from t1 where f2="bill";
select * from t1;
151 152 153 154 155 156
f1	f2
16	ted
12	ted
12	ted
12	ted
12	ted
unknown's avatar
unknown committed
157
drop table t1;
unknown's avatar
unknown committed
158
create table t1 (btn char(10) not null, key(btn)) engine=heap;
unknown's avatar
unknown committed
159 160
insert into t1 values ("hello"),("hello"),("hello"),("hello"),("hello"),("a"),("b"),("c"),("d"),("e"),("f"),("g"),("h"),("i");
explain select * from t1 where btn like "q%";
unknown's avatar
unknown committed
161
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
162
1	SIMPLE	t1	ALL	btn	NULL	NULL	NULL	14	Using where
unknown's avatar
unknown committed
163
select * from t1 where btn like "q%";
164
btn
unknown's avatar
unknown committed
165
alter table t1 add column new_col char(1) not null, add key (btn,new_col), drop key btn;
unknown's avatar
unknown committed
166
update t1 set new_col=left(btn,1);
unknown's avatar
unknown committed
167
explain select * from t1 where btn="a";
unknown's avatar
unknown committed
168
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
169
1	SIMPLE	t1	ALL	btn	NULL	NULL	NULL	11	Using where
unknown's avatar
unknown committed
170
explain select * from t1 where btn="a" and new_col="a";
unknown's avatar
unknown committed
171
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
172
1	SIMPLE	t1	ref	btn	btn	11	const,const	2	Using where
unknown's avatar
unknown committed
173
drop table t1;
174 175 176 177 178
CREATE TABLE t1 (
a int default NULL,
b int default NULL,
KEY a (a),
UNIQUE b (b)
unknown's avatar
unknown committed
179
) engine=heap;
180 181 182 183
INSERT INTO t1 VALUES (NULL,99),(99,NULL),(1,1),(2,2),(1,3);
SELECT * FROM t1 WHERE a=NULL;
a	b
explain SELECT * FROM t1 WHERE a IS NULL;
unknown's avatar
unknown committed
184
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
185
1	SIMPLE	t1	ref	a	a	5	const	2	Using where
186 187 188 189 190 191
SELECT * FROM t1 WHERE a<=>NULL;
a	b
NULL	99
SELECT * FROM t1 WHERE b=NULL;
a	b
explain SELECT * FROM t1 WHERE b IS NULL;
unknown's avatar
unknown committed
192
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
193
1	SIMPLE	t1	ref	b	b	5	const	1	Using where
194 195 196 197
SELECT * FROM t1 WHERE b<=>NULL;
a	b
99	NULL
INSERT INTO t1 VALUES (1,3);
198
ERROR 23000: Duplicate entry '3' for key 1
199
DROP TABLE t1;
200 201 202
CREATE TABLE t1 (
a int default NULL,
key a (a)
unknown's avatar
unknown committed
203
) ENGINE=HEAP;
204 205
INSERT INTO t1 VALUES (10), (10), (10);
EXPLAIN SELECT * FROM t1 WHERE a=10;
unknown's avatar
unknown committed
206
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
207
1	SIMPLE	t1	ref	a	a	5	const	3	Using where
208 209 210 211 212 213
SELECT * FROM t1 WHERE a=10;
a
10
10
10
DROP TABLE t1;
unknown's avatar
unknown committed
214
CREATE TABLE t1 (a int not null, primary key(a)) engine=heap;
215 216 217 218 219
INSERT into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11);
DELETE from t1 where a < 100;
SELECT * from t1;
a
DROP TABLE t1;
unknown's avatar
unknown committed
220 221 222 223 224
CREATE TABLE `job_titles` (
`job_title_id` int(6) unsigned NOT NULL default '0',
`job_title` char(18) NOT NULL default '',
PRIMARY KEY  (`job_title_id`),
UNIQUE KEY `job_title_id` (`job_title_id`,`job_title`)
unknown's avatar
unknown committed
225
) ENGINE=HEAP;
unknown's avatar
unknown committed
226 227 228 229
SELECT MAX(job_title_id) FROM job_titles;
MAX(job_title_id)
NULL
DROP TABLE job_titles;
230 231 232 233 234 235
CREATE TABLE t1 (a INT NOT NULL, B INT, KEY(B)) ENGINE=HEAP;
INSERT INTO t1 VALUES(1,1), (1,NULL);
SELECT * FROM t1 WHERE B is not null;
a	B
1	1
DROP TABLE t1;
236 237 238 239 240 241 242
CREATE TABLE t1 (pseudo char(35) PRIMARY KEY, date int(10) unsigned NOT NULL) ENGINE=HEAP;
INSERT INTO t1 VALUES ('massecot',1101106491),('altec',1101106492),('stitch+',1101106304),('Seb Corgan',1101106305),('beerfilou',1101106263),('flaker',1101106529),('joce8',5),('M4vrick',1101106418),('gabay008',1101106525),('Vamp irX',1101106291),('ZoomZip',1101106546),('rip666',1101106502),('CBP ',1101106397),('guezpard',1101106496);
DELETE FROM t1 WHERE date<1101106546;
SELECT * FROM t1;
pseudo	date
ZoomZip	1101106546
DROP TABLE t1;
243 244 245 246 247 248 249 250 251
create table t1(a char(2)) engine=memory;
insert into t1 values (NULL), (NULL);
delete from t1 where a is null;
insert into t1 values ('2'), ('3');
select * from t1;
a
3
2
drop table t1;
252 253 254 255 256 257
set storage_engine=HEAP;
create table t1 (v varchar(10), c char(10), t varchar(50));
insert into t1 values('+ ', '+ ', '+ ');
set @a=repeat(' ',20);
insert into t1 values (concat('+',@a),concat('+',@a),concat('+',@a));
Warnings:
258
Note	1265	Data truncated for column 'v' at row 1
259 260 261 262 263 264 265 266 267 268
select concat('*',v,'*',c,'*',t,'*') from t1;
concat('*',v,'*',c,'*',t,'*')
*+ *+*+ *
*+         *+*+                    *
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `v` varchar(10) default NULL,
  `c` char(10) default NULL,
  `t` varchar(50) default NULL
269
) ENGINE=MEMORY DEFAULT CHARSET=latin1
270 271 272 273 274 275 276
create table t2 like t1;
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `v` varchar(10) default NULL,
  `c` char(10) default NULL,
  `t` varchar(50) default NULL
277
) ENGINE=MEMORY DEFAULT CHARSET=latin1
278 279 280 281 282 283 284
create table t3 select * from t1;
show create table t3;
Table	Create Table
t3	CREATE TABLE `t3` (
  `v` varchar(10) default NULL,
  `c` char(10) default NULL,
  `t` varchar(50) default NULL
285
) ENGINE=MEMORY DEFAULT CHARSET=latin1
286 287 288 289 290 291 292
alter table t1 modify c varchar(10);
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `v` varchar(10) default NULL,
  `c` varchar(10) default NULL,
  `t` varchar(50) default NULL
293
) ENGINE=MEMORY DEFAULT CHARSET=latin1
294 295 296 297 298 299 300
alter table t1 modify v char(10);
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `v` char(10) default NULL,
  `c` varchar(10) default NULL,
  `t` varchar(50) default NULL
301
) ENGINE=MEMORY DEFAULT CHARSET=latin1
302 303 304 305 306 307 308 309 310
alter table t1 modify t varchar(10);
Warnings:
Warning	1265	Data truncated for column 't' at row 2
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `v` char(10) default NULL,
  `c` varchar(10) default NULL,
  `t` varchar(10) default NULL
311
) ENGINE=MEMORY DEFAULT CHARSET=latin1
312 313 314 315 316 317 318 319 320 321 322 323 324 325 326
select concat('*',v,'*',c,'*',t,'*') from t1;
concat('*',v,'*',c,'*',t,'*')
*+*+*+ *
*+*+*+         *
drop table t1,t2,t3;
create table t1 (v varchar(10), c char(10), t varchar(50), key(v), key(c), key(t(10)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `v` varchar(10) default NULL,
  `c` char(10) default NULL,
  `t` varchar(50) default NULL,
  KEY `v` (`v`),
  KEY `c` (`c`),
  KEY `t` (`t`(10))
327
) ENGINE=MEMORY DEFAULT CHARSET=latin1
328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369
select count(*) from t1;
count(*)
270
insert into t1 values(concat('a',char(1)),concat('a',char(1)),concat('a',char(1)));
select count(*) from t1 where v='a';
count(*)
10
select count(*) from t1 where c='a';
count(*)
10
select count(*) from t1 where t='a';
count(*)
10
select count(*) from t1 where v='a  ';
count(*)
10
select count(*) from t1 where c='a  ';
count(*)
10
select count(*) from t1 where t='a  ';
count(*)
10
select count(*) from t1 where v between 'a' and 'a ';
count(*)
10
select count(*) from t1 where v between 'a' and 'a ' and v between 'a  ' and 'b\n';
count(*)
10
select count(*) from t1 where v like 'a%';
count(*)
11
select count(*) from t1 where c like 'a%';
count(*)
11
select count(*) from t1 where t like 'a%';
count(*)
11
select count(*) from t1 where v like 'a %';
count(*)
9
explain select count(*) from t1 where v='a  ';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
370
1	SIMPLE	t1	ref	v	v	13	const	10	Using where
371 372
explain select count(*) from t1 where c='a  ';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
373
1	SIMPLE	t1	ref	c	c	11	const	10	Using where
374 375
explain select count(*) from t1 where t='a  ';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
376
1	SIMPLE	t1	ref	t	t	13	const	10	Using where
377 378 379 380 381
explain select count(*) from t1 where v like 'a%';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	v	NULL	NULL	NULL	271	Using where
explain select count(*) from t1 where v between 'a' and 'a ';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
382
1	SIMPLE	t1	ref	v	v	13	const	10	Using where
383 384
explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a  ' and 'b\n';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
385
1	SIMPLE	t1	ref	v	v	13	const	10	Using where
386 387
alter table t1 add unique(v);
ERROR 23000: Duplicate entry '{ ' for key 1
388
select concat('*',v,'*',c,'*',t,'*') as qq from t1 where v='a' order by length(concat('*',v,'*',c,'*',t,'*'));
389 390
qq
*a*a*a*
391 392 393 394 395 396 397 398 399
*a *a*a *
*a  *a*a  *
*a   *a*a   *
*a    *a*a    *
*a     *a*a     *
*a      *a*a      *
*a       *a*a       *
*a        *a*a        *
*a         *a*a         *
400 401
explain select * from t1 where v='a';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
402
1	SIMPLE	t1	ref	v	v	13	const	10	Using where
403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438
select v,count(*) from t1 group by v limit 10;
v	count(*)
a	1
a	10
b	10
c	10
d	10
e	10
f	10
g	10
h	10
i	10
select v,count(t) from t1 group by v limit 10;
v	count(t)
a	1
a	10
b	10
c	10
d	10
e	10
f	10
g	10
h	10
i	10
select v,count(c) from t1 group by v limit 10;
v	count(c)
a	1
a	10
b	10
c	10
d	10
e	10
f	10
g	10
h	10
i	10
439 440
select sql_big_result trim(v),count(t) from t1 group by v limit 10;
trim(v)	count(t)
441
a	1
442 443 444 445 446 447 448
a	10
b	10
c	10
d	10
e	10
f	10
g	10
449
h	10
450 451 452
i	10
select sql_big_result trim(v),count(c) from t1 group by v limit 10;
trim(v)	count(c)
453
a	1
454 455 456 457 458 459 460
a	10
b	10
c	10
d	10
e	10
f	10
g	10
461
h	10
462
i	10
463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522
select c,count(*) from t1 group by c limit 10;
c	count(*)
a	1
a	10
b	10
c	10
d	10
e	10
f	10
g	10
h	10
i	10
select c,count(t) from t1 group by c limit 10;
c	count(t)
a	1
a	10
b	10
c	10
d	10
e	10
f	10
g	10
h	10
i	10
select sql_big_result c,count(t) from t1 group by c limit 10;
c	count(t)
a	1
a	10
b	10
c	10
d	10
e	10
f	10
g	10
h	10
i	10
select t,count(*) from t1 group by t limit 10;
t	count(*)
a	1
a	10
b	10
c	10
d	10
e	10
f	10
g	10
h	10
i	10
select t,count(t) from t1 group by t limit 10;
t	count(t)
a	1
a	10
b	10
c	10
d	10
e	10
f	10
g	10
h	10
i	10
523 524
select sql_big_result trim(t),count(t) from t1 group by t limit 10;
trim(t)	count(t)
525
a	1
526 527 528 529 530 531 532
a	10
b	10
c	10
d	10
e	10
f	10
g	10
533
h	10
534
i	10
535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558
drop table t1;
create table t1 (a char(10), unique (a));
insert into t1 values ('a');
insert into t1 values ('a ');
ERROR 23000: Duplicate entry 'a' for key 1
alter table t1 modify a varchar(10);
insert into t1 values ('a '),('a  '),('a   '),('a         ');
ERROR 23000: Duplicate entry 'a ' for key 1
insert into t1 values ('a     ');
ERROR 23000: Duplicate entry 'a     ' for key 1
insert into t1 values ('a          ');
ERROR 23000: Duplicate entry 'a         ' for key 1
insert into t1 values ('a ');
ERROR 23000: Duplicate entry 'a ' for key 1
update t1 set a='a      ' where a like 'a ';
update t1 set a='a  ' where a like 'a      ';
drop table t1;
create table t1 (v varchar(10), c char(10), t varchar(50), key using btree (v), key using btree (c), key using btree (t(10)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `v` varchar(10) default NULL,
  `c` char(10) default NULL,
  `t` varchar(50) default NULL,
unknown's avatar
unknown committed
559 560 561
  KEY `v` USING BTREE (`v`),
  KEY `c` USING BTREE (`c`),
  KEY `t` USING BTREE (`t`(10))
562
) ENGINE=MEMORY DEFAULT CHARSET=latin1
563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604
select count(*) from t1;
count(*)
270
insert into t1 values(concat('a',char(1)),concat('a',char(1)),concat('a',char(1)));
select count(*) from t1 where v='a';
count(*)
10
select count(*) from t1 where c='a';
count(*)
10
select count(*) from t1 where t='a';
count(*)
10
select count(*) from t1 where v='a  ';
count(*)
10
select count(*) from t1 where c='a  ';
count(*)
10
select count(*) from t1 where t='a  ';
count(*)
10
select count(*) from t1 where v between 'a' and 'a ';
count(*)
10
select count(*) from t1 where v between 'a' and 'a ' and v between 'a  ' and 'b\n';
count(*)
10
explain select count(*) from t1 where v='a  ';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	v	v	13	const	#	Using where
explain select count(*) from t1 where c='a  ';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	c	c	11	const	#	Using where
explain select count(*) from t1 where t='a  ';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	t	t	13	const	#	Using where
explain select count(*) from t1 where v like 'a%';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	v	v	13	NULL	#	Using where
explain select count(*) from t1 where v between 'a' and 'a ';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
605
1	SIMPLE	t1	ref	v	v	13	const	#	Using where
606 607
explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a  ' and 'b\n';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
608
1	SIMPLE	t1	ref	v	v	13	const	#	Using where
609 610
alter table t1 add unique(v);
ERROR 23000: Duplicate entry '{ ' for key 1
611
select concat('*',v,'*',c,'*',t,'*') as qq from t1 where v='a' order by length(concat('*',v,'*',c,'*',t,'*'));
612 613 614 615 616 617 618 619 620 621 622 623 624
qq
*a*a*a*
*a *a*a *
*a  *a*a  *
*a   *a*a   *
*a    *a*a    *
*a     *a*a     *
*a      *a*a      *
*a       *a*a       *
*a        *a*a        *
*a         *a*a         *
explain select * from t1 where v='a';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
625
1	SIMPLE	t1	ref	v	v	13	const	#	Using where
626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652
drop table t1;
create table t1 (a char(10), unique using btree (a)) engine=heap;
insert into t1 values ('a');
insert into t1 values ('a ');
ERROR 23000: Duplicate entry 'a' for key 1
alter table t1 modify a varchar(10);
insert into t1 values ('a '),('a  '),('a   '),('a         ');
ERROR 23000: Duplicate entry 'a ' for key 1
insert into t1 values ('a     ');
ERROR 23000: Duplicate entry 'a     ' for key 1
insert into t1 values ('a          ');
ERROR 23000: Duplicate entry 'a         ' for key 1
insert into t1 values ('a ');
ERROR 23000: Duplicate entry 'a ' for key 1
update t1 set a='a      ' where a like 'a ';
update t1 set a='a  ' where a like 'a      ';
drop table t1;
create table t1 (v varchar(10), c char(10), t varchar(50), key(v(5)), key(c(5)), key(t(5)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `v` varchar(10) default NULL,
  `c` char(10) default NULL,
  `t` varchar(50) default NULL,
  KEY `v` (`v`(5)),
  KEY `c` (`c`(5)),
  KEY `t` (`t`(5))
653
) ENGINE=MEMORY DEFAULT CHARSET=latin1
654 655 656 657 658 659 660
drop table t1;
create table t1 (v varchar(65530), key(v(10)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `v` varchar(65530) default NULL,
  KEY `v` (`v`(10))
661
) ENGINE=MEMORY DEFAULT CHARSET=latin1
662 663 664 665 666 667
insert into t1 values(repeat('a',65530));
select length(v) from t1 where v=repeat('a',65530);
length(v)
65530
drop table t1;
set storage_engine=MyISAM;
668 669
create table t1 (a bigint unsigned auto_increment primary key, b int,
key (b, a)) engine=heap;
670
insert t1 (b) values (1),(1),(1),(1),(1),(1),(1),(1);
671 672 673 674 675 676 677 678 679 680 681 682 683
select * from t1;
a	b
1	1
2	1
3	1
4	1
5	1
6	1
7	1
8	1
drop table t1;
create table t1 (a int not null, b int not null auto_increment,
primary key(a, b), key(b)) engine=heap;
684
insert t1 (a) values (1),(1),(1),(1),(1),(1),(1),(1);
685 686 687 688 689 690 691 692 693 694 695
select * from t1;
a	b
1	1
1	2
1	3
1	4
1	5
1	6
1	7
1	8
drop table t1;
696 697 698
create table t1 (a int not null, b int not null auto_increment,
primary key(a, b)) engine=heap;
ERROR 42000: Incorrect table definition; there can be only one auto column and it must be defined as a key
699 700 701
create table t1 (c char(255), primary key(c(90)));
insert into t1 values ("abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz");
insert into t1 values ("abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz");
702
ERROR 23000: Duplicate entry 'abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijkl' for key 1
703
drop table t1;
unknown's avatar
unknown committed
704 705 706 707 708 709 710 711
CREATE TABLE t1 (a int, key(a)) engine=heap;
insert delayed into t1 values (0);
delete from t1;
select * from t1;
a
insert delayed into t1 values (0), (1);
select * from t1 where a = 0;
a
unknown's avatar
unknown committed
712
0
unknown's avatar
unknown committed
713
drop table t1;
714 715 716 717
create table t1 (c char(10)) engine=memory;
create table t2 (c varchar(10)) engine=memory;
show table status like 't_';
Name	Engine	Version	Row_format	Rows	Avg_row_length	Data_length	Max_data_length	Index_length	Data_free	Auto_increment	Create_time	Update_time	Check_time	Collation	Checksum	Create_options	Comment
718 719
t1	MEMORY	10	Fixed	0	11	0	#	0	0	NULL	NULL	NULL	NULL	latin1_swedish_ci	NULL		
t2	MEMORY	10	Fixed	0	12	0	#	0	0	NULL	NULL	NULL	NULL	latin1_swedish_ci	NULL		
720
drop table t1, t2;