1
drop table if exists t1,t2,t3;
5
name CHAR(32) NOT NULL,
7
INDEX indexIDname (ID(8),name(8))
10
('keyword','indexdir','/export/home/local/www/database/indexes/keyword');
11
INSERT INTO t1 VALUES ('keyword','urlprefix','text/ /text');
12
INSERT INTO t1 VALUES ('keyword','urlmap','/text/ /');
13
INSERT INTO t1 VALUES ('keyword','attr','personal employee company');
15
('emailgids','indexdir','/export/home/local/www/database/indexes/emailgids');
16
INSERT INTO t1 VALUES ('emailgids','urlprefix','text/ /text');
17
INSERT INTO t1 VALUES ('emailgids','urlmap','/text/ /');
18
INSERT INTO t1 VALUES ('emailgids','attr','personal employee company');
19
SELECT value FROM t1 WHERE ID='emailgids' AND name='attr';
21
personal employee company
24
price int(5) DEFAULT '0' NOT NULL,
25
area varchar(40) DEFAULT '' NOT NULL,
26
type varchar(40) DEFAULT '' NOT NULL,
27
transityes enum('Y','N') DEFAULT 'Y' NOT NULL,
28
shopsyes enum('Y','N') DEFAULT 'Y' NOT NULL,
29
schoolsyes enum('Y','N') DEFAULT 'Y' NOT NULL,
30
petsyes enum('Y','N') DEFAULT 'Y' NOT NULL,
31
KEY price (price,area,type,transityes,shopsyes,schoolsyes,petsyes)
33
INSERT INTO t1 VALUES (900,'Vancouver','Shared/Roomate','N','N','N','N');
34
INSERT INTO t1 VALUES (900,'Vancouver','Shared/Roomate','N','N','N','N');
35
INSERT INTO t1 VALUES (900,'Vancouver','Shared/Roomate','','','','');
37
Warning 1265 Data truncated for column 'transityes' at row 1
38
Warning 1265 Data truncated for column 'shopsyes' at row 1
39
Warning 1265 Data truncated for column 'schoolsyes' at row 1
40
Warning 1265 Data truncated for column 'petsyes' at row 1
41
INSERT INTO t1 VALUES (900,'Vancouver','Shared/Roomate','Y','Y','Y','Y');
42
INSERT INTO t1 VALUES (900,'Vancouver','Shared/Roomate','Y','Y','Y','Y');
43
INSERT INTO t1 VALUES (900,'Vancouver','Shared/Roomate','Y','Y','Y','Y');
44
INSERT INTO t1 VALUES (900,'Vancouver','Shared/Roomate','Y','Y','Y','Y');
45
INSERT INTO t1 VALUES (900,'Vancouver','Shared/Roomate','Y','Y','Y','Y');
46
SELECT * FROM t1 WHERE area='Vancouver' and transityes='y' and schoolsyes='y' and ( ((type='1 Bedroom' or type='Studio/Bach') and (price<=500)) or ((type='2 Bedroom') and (price<=550)) or ((type='Shared/Roomate') and (price<=300)) or ((type='Room and Board') and (price<=500)) ) and price <= 400;
47
price area type transityes shopsyes schoolsyes petsyes
49
CREATE TABLE t1 (program enum('signup','unique','sliding') not null, type enum('basic','sliding','signup'), sites set('mt'), PRIMARY KEY (program));
50
ALTER TABLE t1 modify program enum('signup','unique','sliding');
53
name varchar(50) DEFAULT '' NOT NULL,
54
author varchar(50) DEFAULT '' NOT NULL,
55
category decimal(10,0) DEFAULT '0' NOT NULL,
63
timeout decimal(10,0),
65
creation decimal(10,0),
66
livinguntil decimal(10,0),
70
subtype decimal(10,0),
73
capacity decimal(10,0),
75
CCident varchar(50) DEFAULT '' NOT NULL,
76
PRIMARY KEY (name,author,category)
79
('patnom','patauteur',0,'p.favre@cryo-networks.fr',NULL,NULL,'#p2sndnq6ae5g1u6t','essai salut','scol://195.242.78.119:patauteur.patnom',NULL,NULL,NULL,950036174,-882087474,NULL,3,0,3,'1','Pub/patnom/futur_divers.scs',NULL,'pat','CC1');
81
('LeNomDeMonSite','Marc',0,'m.barilley@cryo-networks.fr',NULL,NULL,NULL,NULL,'scol://195.242.78.119:Marc.LeNomDeMonSite',NULL,NULL,NULL,950560434,-881563214,NULL,3,0,3,'1','Pub/LeNomDeMonSite/domus_hibere.scs',NULL,'Marq','CC1');
82
select * from t1 where name='patnom' and author='patauteur' and category=0;
83
name author category email password proxy bitmap msg urlscol urlhttp timeout nbcnx creation livinguntil lang type subcat subtype reg scs capacity userISP CCident
84
patnom patauteur 0 p.favre@cryo-networks.fr NULL NULL #p2sndnq6ae5g1u6t essai salut scol://195.242.78.119:patauteur.patnom NULL NULL NULL 950036174 -882087474 NULL 3 0 3 1 Pub/patnom/futur_divers.scs NULL pat CC1
88
name_id int not null auto_increment,
90
INDEX name_idx (name(5)),
93
INSERT t1 VALUES(NULL,'/');
94
INSERT t1 VALUES(NULL,'[T,U]_axpby');
95
SELECT * FROM t1 WHERE name='[T,U]_axpy';
97
SELECT * FROM t1 WHERE name='[T,U]_axpby';
102
name_id int not null auto_increment,
103
name char(255) binary,
104
INDEX name_idx (name(5)),
105
primary key (name_id)
107
INSERT t2 select * from t1;
108
SELECT * FROM t2 WHERE name='[T,U]_axpy';
110
SELECT * FROM t2 WHERE name='[T,U]_axpby';
113
CREATE TABLE t3 SELECT * FROM t2 WHERE name='[T,U]_axpby';
114
SELECT * FROM t2 WHERE name='[T,U]_axpby';
120
SEQNO numeric(12 ) not null,
121
MOTYPEID numeric(12 ) not null,
122
MOINSTANCEID numeric(12 ) not null,
123
ATTRID numeric(12 ) not null,
124
VALUE varchar(120) not null,
125
primary key (SEQNO, MOTYPEID, MOINSTANCEID, ATTRID, VALUE )
127
INSERT INTO t1 VALUES (1, 1, 1, 1, 'a');
128
INSERT INTO t1 VALUES (1, 1, 1, 1, 'b');
129
INSERT INTO t1 VALUES (1, 1, 1, 1, 'a');
130
ERROR 23000: Duplicate entry '1-1-1-1-a' for key 'PRIMARY'
134
b tinyint(3) unsigned NOT NULL default '0',
135
PRIMARY KEY (a(32),b)
137
INSERT INTO t1 VALUES ('a',1),('a',2);
138
SELECT * FROM t1 WHERE a='a' AND b=2;
141
SELECT * FROM t1 WHERE a='a' AND b in (2);
144
SELECT * FROM t1 WHERE a='a' AND b in (1,2);
149
create table t1 (a int not null unique, b int unique, c int, d int not null primary key, key(c), e int not null unique);
151
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment
152
t1 0 PRIMARY 1 d A 0 NULL NULL BTREE
153
t1 0 a 1 a A 0 NULL NULL BTREE
154
t1 0 e 1 e A 0 NULL NULL BTREE
155
t1 0 b 1 b A NULL NULL NULL YES BTREE
156
t1 1 c 1 c A NULL NULL NULL YES BTREE
158
CREATE TABLE t1 (c CHAR(10) NOT NULL,i INT NOT NULL AUTO_INCREMENT,
160
INSERT INTO t1 (c) VALUES (NULL),(NULL);
162
Warning 1048 Column 'c' cannot be null
163
Warning 1048 Column 'c' cannot be null
168
INSERT INTO t1 (c) VALUES ('a'),('a');
175
DROP TABLE IF EXISTS t1;
176
CREATE TABLE t1 (c CHAR(10) NULL, i INT NOT NULL AUTO_INCREMENT,
178
INSERT INTO t1 (c) VALUES (NULL),(NULL);
183
INSERT INTO t1 (c) VALUES ('a'),('a');
191
create table t1 (i int, a char(200), b text, unique (a), unique (b(300))) charset utf8;
192
insert t1 values (1, repeat('a',210), repeat('b', 310));
194
Warning 1265 Data truncated for column 'a' at row 1
195
insert t1 values (2, repeat(0xD0B1,215), repeat(0xD0B1, 310));
197
Warning 1265 Data truncated for column 'a' at row 1
198
select i, length(a), length(b), char_length(a), char_length(b) from t1;
199
i length(a) length(b) char_length(a) char_length(b)
202
select i from t1 where a=repeat(_utf8 'a',200);
205
select i from t1 where a=repeat(_utf8 0xD0B1,200);
208
select i from t1 where b=repeat(_utf8 'b',310);
212
CREATE TABLE t1 (id int unsigned auto_increment, name char(50), primary key (id)) engine=myisam;
213
insert into t1 (name) values ('a'), ('b'),('c'),('d'),('e'),('f'),('g');
214
explain select 1 from t1 where id =2;
215
id select_type table type possible_keys key key_len ref rows Extra
216
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 Using index
217
explain select 1 from t1 where id =2 or id=3;
218
id select_type table type possible_keys key key_len ref rows Extra
219
1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 7 Using where; Using index
220
explain select name from t1 where id =2;
221
id select_type table type possible_keys key key_len ref rows Extra
222
1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1
223
ALTER TABLE t1 DROP PRIMARY KEY, ADD INDEX (id);
224
explain select 1 from t1 where id =2;
225
id select_type table type possible_keys key key_len ref rows Extra
226
1 SIMPLE t1 ref id id 4 const 1 Using index
228
CREATE TABLE t1 (numeropost mediumint(8) unsigned NOT NULL default '0', numreponse int(10) unsigned NOT NULL auto_increment, PRIMARY KEY (numeropost,numreponse), UNIQUE KEY numreponse (numreponse));
229
INSERT INTO t1 (numeropost,numreponse) VALUES ('1','1'),('1','2'),('2','3'),('2','4');
230
SELECT numeropost FROM t1 WHERE numreponse='1';
233
EXPLAIN SELECT numeropost FROM t1 WHERE numreponse='1';
234
id select_type table type possible_keys key key_len ref rows Extra
235
1 SIMPLE t1 const numreponse numreponse 4 const 1 Using index
237
SELECT numeropost FROM t1 WHERE numreponse='1';
241
create table t1 (c varchar(30) character set utf8, t text character set utf8, unique (c(2)), unique (t(3))) engine=myisam;
242
show create table t1;
244
t1 CREATE TABLE `t1` (
245
`c` varchar(30) CHARACTER SET utf8 DEFAULT NULL,
246
`t` text CHARACTER SET utf8,
247
UNIQUE KEY `c` (`c`(2)),
248
UNIQUE KEY `t` (`t`(3))
249
) ENGINE=MyISAM DEFAULT CHARSET=latin1
250
insert t1 values ('cccc', 'tttt'),
251
(0xD0B1212223D0B1D0B1D0B1D0B1D0B1, 0xD0B1D0B1212223D0B1D0B1D0B1D0B1),
252
(0xD0B1222123D0B1D0B1D0B1D0B1D0B1, 0xD0B1D0B1222123D0B1D0B1D0B1D0B1);
253
insert t1 (c) values ('cc22');
254
ERROR 23000: Duplicate entry 'cc' for key 'c'
255
insert t1 (t) values ('ttt22');
256
ERROR 23000: Duplicate entry 'ttt' for key 't'
257
insert t1 (c) values (0xD0B1212322D0B1D0B1D0B1D0B1D0B1);
258
ERROR 23000: Duplicate entry 'б!' for key 'c'
259
insert t1 (t) values (0xD0B1D0B1212322D0B1D0B1D0B1D0B1);
260
ERROR 23000: Duplicate entry 'бб!' for key 't'
261
select c from t1 where c='cccc';
264
select t from t1 where t='tttt';
267
select c from t1 where c=0xD0B1212223D0B1D0B1D0B1D0B1D0B1;
270
select t from t1 where t=0xD0B1D0B1212223D0B1D0B1D0B1D0B1;
274
DROP TABLE IF EXISTS t1;
276
Note 1051 Unknown table 't1'
283
INSERT INTO t1 VALUES (1,'\Z\Z\Z\Z');
284
INSERT INTO t1 VALUES (2,'\Z\Z\Z\Z\Z\Z');
285
INSERT INTO t1 VALUES (3,'\Z\Z\Z\Z');
286
select c1 from t1 where c2='\Z\Z\Z\Z';
290
DELETE FROM t1 WHERE (c1 = 1);
292
Table Op Msg_type Msg_text
293
test.t1 check status OK
294
select c1 from t1 where c2='\Z\Z\Z\Z';
297
DELETE FROM t1 WHERE (c1 = 3);
299
Table Op Msg_type Msg_text
300
test.t1 check status OK
301
select c1 from t1 where c2='\Z\Z\Z\Z';
304
insert into t1 values(1,"aaaa"),(2,"aaab"),(3,"aaac"),(4,"aaccc");
305
delete from t1 where c1=3;
306
delete from t1 where c1=1;
307
delete from t1 where c1=4;
309
Table Op Msg_type Msg_text
310
test.t1 check status OK
312
create table t1 (c char(10), index (c(0)));
313
ERROR HY000: Key part 'c' length cannot be 0
314
create table t1 (c char(10), index (c,c));
315
ERROR 42S21: Duplicate column name 'c'
316
create table t1 (c1 char(10), c2 char(10), index (c1,c2,c1));
317
ERROR 42S21: Duplicate column name 'c1'
318
create table t1 (c1 char(10), c2 char(10), index (c1,c1,c2));
319
ERROR 42S21: Duplicate column name 'c1'
320
create table t1 (c1 char(10), c2 char(10), index (c2,c1,c1));
321
ERROR 42S21: Duplicate column name 'c1'
322
create table t1 (c1 char(10), c2 char(10));
323
alter table t1 add key (c1,c1);
324
ERROR 42S21: Duplicate column name 'c1'
325
alter table t1 add key (c2,c1,c1);
326
ERROR 42S21: Duplicate column name 'c1'
327
alter table t1 add key (c1,c2,c1);
328
ERROR 42S21: Duplicate column name 'c1'
329
alter table t1 add key (c1,c1,c2);
330
ERROR 42S21: Duplicate column name 'c1'
338
Field Type Null Key Default Extra
339
i1 int(11) NO PRI NULL
340
i2 int(11) NO UNI NULL
341
show create table t1;
343
t1 CREATE TABLE `t1` (
344
`i1` int(11) NOT NULL,
345
`i2` int(11) NOT NULL,
346
UNIQUE KEY `i1idx` (`i1`),
347
UNIQUE KEY `i2idx` (`i2`)
348
) ENGINE=MyISAM DEFAULT CHARSET=latin1
352
c2 varchar(20) not null,
356
insert into t1 values (1,'');
357
insert into t1 values (2,' \t\tTest String');
358
insert into t1 values (3,' \n\tTest String');
359
update t1 set c2 = 'New Test String' where c1 = 1;
367
create table t1 (a varchar(10), b varchar(10), key(a(10),b(10)));
368
show create table t1;
370
t1 CREATE TABLE `t1` (
371
`a` varchar(10) DEFAULT NULL,
372
`b` varchar(10) DEFAULT NULL,
374
) ENGINE=MyISAM DEFAULT CHARSET=latin1
375
alter table t1 modify b varchar(20);
376
show create table t1;
378
t1 CREATE TABLE `t1` (
379
`a` varchar(10) DEFAULT NULL,
380
`b` varchar(20) DEFAULT NULL,
382
) ENGINE=MyISAM DEFAULT CHARSET=latin1
383
alter table t1 modify a varchar(20);
384
show create table t1;
386
t1 CREATE TABLE `t1` (
387
`a` varchar(20) DEFAULT NULL,
388
`b` varchar(20) DEFAULT NULL,
390
) ENGINE=MyISAM DEFAULT CHARSET=latin1
392
create table t1 (a int not null primary key, b varchar(20) not null unique);
394
Field Type Null Key Default Extra
395
a int(11) NO PRI NULL
396
b varchar(20) NO UNI NULL
398
create table t1 (a int not null primary key, b int not null unique);
400
Field Type Null Key Default Extra
401
a int(11) NO PRI NULL
402
b int(11) NO UNI NULL
404
create table t1 (a int not null primary key, b varchar(20) not null, unique (b(10)));
406
Field Type Null Key Default Extra
407
a int(11) NO PRI NULL
408
b varchar(20) NO UNI NULL
410
create table t1 (a int not null primary key, b varchar(20) not null, c varchar(20) not null, unique(b(10),c(10)));
412
Field Type Null Key Default Extra
413
a int(11) NO PRI NULL
414
b varchar(20) NO MUL NULL
415
c varchar(20) NO NULL
427
index i5 (c1, c2, c3, c4),
428
primary key (c2, c3),
430
show create table t1;
432
t1 CREATE TABLE `t1` (
433
`c1` int(11) DEFAULT NULL,
434
`c2` char(12) NOT NULL DEFAULT '',
435
`c3` varchar(123) NOT NULL DEFAULT '',
436
`c4` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
437
PRIMARY KEY (`c2`,`c3`),
438
UNIQUE KEY `i4` (`c4`),
443
KEY `i5` (`c1`,`c2`,`c3`,`c4`),
445
) ENGINE=MyISAM DEFAULT CHARSET=latin1
446
alter table t1 drop index c1;
447
alter table t1 add index (c1);
448
alter table t1 add index (c1);
449
alter table t1 drop index i3;
450
alter table t1 add index i3 (c3);
451
alter table t1 drop index i2, drop index i4;
452
alter table t1 add index i2 (c2), add index i4 (c4);
453
alter table t1 drop index i2, drop index i4, add index i6 (c2, c4);
454
alter table t1 add index i2 (c2), add index i4 (c4), drop index i6;
455
alter table t1 drop index i2, drop index i4, add unique i4 (c4);
456
alter table t1 add index i2 (c2), drop index i4, add index i4 (c4);
457
alter table t1 drop index c2, add index (c2(4),c3(7));
458
alter table t1 drop index c2, add index (c2(4),c3(7));
459
alter table t1 add primary key (c1, c2), drop primary key;
460
alter table t1 drop primary key;
461
alter table t1 add primary key (c1, c2), drop primary key;
462
ERROR 42000: Can't DROP 'PRIMARY'; check that column/key exists
463
show create table t1;
465
t1 CREATE TABLE `t1` (
466
`c1` int(11) NOT NULL DEFAULT '0',
467
`c2` char(12) NOT NULL DEFAULT '',
468
`c3` varchar(123) NOT NULL DEFAULT '',
469
`c4` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
471
KEY `i5` (`c1`,`c2`,`c3`,`c4`),
477
KEY `c2` (`c2`(4),`c3`(7))
478
) ENGINE=MyISAM DEFAULT CHARSET=latin1
479
insert into t1 values(1, 'a', 'a', NULL);
480
insert into t1 values(1, 'b', 'b', NULL);
481
alter table t1 drop index i3, drop index i2, drop index i1;
482
alter table t1 add index i3 (c3), add index i2 (c2), add unique index i1 (c1);
483
ERROR 23000: Duplicate entry '1' for key 'i1'
485
CREATE TABLE t1( a TINYINT, KEY(a) ) ENGINE=MyISAM;
486
INSERT INTO t1 VALUES( 1 );
487
ALTER TABLE t1 DISABLE KEYS;
488
EXPLAIN SELECT MAX(a) FROM t1 FORCE INDEX(a);
489
id select_type table type possible_keys key key_len ref rows Extra
490
1 SIMPLE t1 system NULL NULL NULL NULL 1
493
a INTEGER auto_increment PRIMARY KEY,
499
a INTEGER auto_increment PRIMARY KEY,
510
INDEX (b, d, e, f, g, h, i, j, c),
513
INSERT INTO t2 VALUES
514
(NULL, 1, 254, '1000-01-01 00:00:00', 257, 0, 0, 0, 0, 0),
515
(NULL, 2, 1, '2004-11-30 12:00:00', 1, 0, 0, 0, 0, 0),
516
(NULL, 3, 1, '2004-11-30 12:00:00', 1, 0, 0, 2, -21600, 0),
517
(NULL, 4, 1, '2004-11-30 12:00:00', 1, 0, 0, 2, -10800, 0),
518
(NULL, 5, 1, '2004-11-30 12:00:00', 1, 0, 0, 5, -10800, 0),
519
(NULL, 6, 1, '2004-11-30 12:00:00', 102, 0, 0, 0, 0, 0),
520
(NULL, 7, 1, '2004-11-30 12:00:00', 105, 2, 0, 0, 0, 0),
521
(NULL, 8, 1, '2004-11-30 12:00:00', 105, 10, 0, 0, 0, 0);
522
INSERT INTO t1 (b, c, d) VALUES
523
(3388000, -553000, NULL),
524
(3388000, -553000, NULL);
526
create table t1(a int not null, key aa(a),
527
b char(10) not null, unique key bb(b(1)),
528
c char(4) not null, unique key cc(c));
530
Field Type Null Key Default Extra
531
a int(11) NO MUL NULL
532
b char(10) NO UNI NULL
533
c char(4) NO PRI NULL
534
show create table t1;
536
t1 CREATE TABLE `t1` (
537
`a` int(11) NOT NULL,
538
`b` char(10) NOT NULL,
539
`c` char(4) NOT NULL,
540
UNIQUE KEY `cc` (`c`),
541
UNIQUE KEY `bb` (`b`(1)),
543
) ENGINE=MyISAM DEFAULT CHARSET=latin1
545
create table t1(a int not null, key aa(a),
546
b char(10) not null, unique key bb(b(1)),
549
Field Type Null Key Default Extra
550
a int(11) NO MUL NULL
551
b char(10) NO UNI NULL
553
alter table t1 add unique key cc(c);
555
Field Type Null Key Default Extra
556
a int(11) NO MUL NULL
557
b char(10) NO UNI NULL
558
c char(4) NO PRI NULL
559
show create table t1;
561
t1 CREATE TABLE `t1` (
562
`a` int(11) NOT NULL,
563
`b` char(10) NOT NULL,
564
`c` char(4) NOT NULL,
565
UNIQUE KEY `cc` (`c`),
566
UNIQUE KEY `bb` (`b`(1)),
568
) ENGINE=MyISAM DEFAULT CHARSET=latin1
571
DROP TABLE IF EXISTS t1;
572
CREATE TABLE t1 (a INT PRIMARY KEY AUTO_INCREMENT);
573
INSERT INTO t1 VALUES (), (), ();
579
GROUP BY GREATEST(LAST_INSERT_ID(), t1.a) ASC
586
CREATE TABLE t1 (a INT, b INT, INDEX (a,b));
587
INSERT INTO t1 (a, b)
589
(1,1), (1,2), (1,3), (1,4), (1,5),
590
(2,2), (2,3), (2,1), (3,1), (4,1), (4,2), (4,3), (4,4), (4,5), (4,6);
591
EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE
592
(SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12;
593
id select_type table type possible_keys key key_len ref rows Extra
594
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE
595
2 SUBQUERY t1 range NULL a 5 NULL 8 Using index for group-by
596
SELECT 1 as RES FROM t1 AS t1_outer WHERE
597
(SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12;
added
removed
mysql-test/r/key.result
