key.result 19.5 KB
Newer Older
unknown's avatar
unknown committed
1
drop table if exists t1,t2,t3;
unknown's avatar
unknown committed
2
SET SQL_WARNINGS=1;
unknown's avatar
unknown committed
3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
CREATE TABLE t1 (
ID CHAR(32) NOT NULL,
name CHAR(32) NOT NULL,
value CHAR(255),
INDEX indexIDname (ID(8),name(8))
) ;
INSERT INTO t1 VALUES
('keyword','indexdir','/export/home/local/www/database/indexes/keyword');
INSERT INTO t1 VALUES ('keyword','urlprefix','text/ /text');
INSERT INTO t1 VALUES ('keyword','urlmap','/text/ /');
INSERT INTO t1 VALUES ('keyword','attr','personal employee company');
INSERT INTO t1 VALUES
('emailgids','indexdir','/export/home/local/www/database/indexes/emailgids');
INSERT INTO t1 VALUES ('emailgids','urlprefix','text/ /text');
INSERT INTO t1 VALUES ('emailgids','urlmap','/text/ /');
INSERT INTO t1 VALUES ('emailgids','attr','personal employee company');
SELECT value FROM t1 WHERE ID='emailgids' AND name='attr';
20 21
value
personal employee company
unknown's avatar
unknown committed
22 23 24 25 26 27 28 29 30 31 32 33 34 35
drop table t1;
CREATE TABLE t1 (
price int(5) DEFAULT '0' NOT NULL,
area varchar(40) DEFAULT '' NOT NULL,
type varchar(40) DEFAULT '' NOT NULL,
transityes enum('Y','N') DEFAULT 'Y' NOT NULL,
shopsyes enum('Y','N') DEFAULT 'Y' NOT NULL,
schoolsyes enum('Y','N') DEFAULT 'Y' NOT NULL,
petsyes enum('Y','N') DEFAULT 'Y' NOT NULL,
KEY price (price,area,type,transityes,shopsyes,schoolsyes,petsyes)
);
INSERT INTO t1 VALUES (900,'Vancouver','Shared/Roomate','N','N','N','N');
INSERT INTO t1 VALUES (900,'Vancouver','Shared/Roomate','N','N','N','N');
INSERT INTO t1 VALUES (900,'Vancouver','Shared/Roomate','','','','');
unknown's avatar
unknown committed
36
Warnings:
unknown's avatar
unknown committed
37 38 39 40
Warning	1265	Data truncated for column 'transityes' at row 1
Warning	1265	Data truncated for column 'shopsyes' at row 1
Warning	1265	Data truncated for column 'schoolsyes' at row 1
Warning	1265	Data truncated for column 'petsyes' at row 1
unknown's avatar
unknown committed
41 42 43 44 45 46
INSERT INTO t1 VALUES (900,'Vancouver','Shared/Roomate','Y','Y','Y','Y');
INSERT INTO t1 VALUES (900,'Vancouver','Shared/Roomate','Y','Y','Y','Y');
INSERT INTO t1 VALUES (900,'Vancouver','Shared/Roomate','Y','Y','Y','Y');
INSERT INTO t1 VALUES (900,'Vancouver','Shared/Roomate','Y','Y','Y','Y');
INSERT INTO t1 VALUES (900,'Vancouver','Shared/Roomate','Y','Y','Y','Y');
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
unknown's avatar
unknown committed
48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78
drop table t1;
CREATE TABLE t1 (program enum('signup','unique','sliding') not null,  type enum('basic','sliding','signup'),  sites set('mt'),  PRIMARY KEY (program));
ALTER TABLE t1 modify program enum('signup','unique','sliding');
drop table t1;
CREATE TABLE t1 (
name varchar(50) DEFAULT '' NOT NULL,
author varchar(50) DEFAULT '' NOT NULL,
category decimal(10,0) DEFAULT '0' NOT NULL,
email varchar(50),
password varchar(50),
proxy varchar(50),
bitmap varchar(20),
msg varchar(255),
urlscol varchar(127),
urlhttp varchar(127),
timeout decimal(10,0),
nbcnx decimal(10,0),
creation decimal(10,0),
livinguntil decimal(10,0),
lang decimal(10,0),
type decimal(10,0),
subcat decimal(10,0),
subtype decimal(10,0),
reg char(1),
scs varchar(255),
capacity decimal(10,0),
userISP varchar(50),
CCident varchar(50) DEFAULT '' NOT NULL,
PRIMARY KEY (name,author,category)
);
INSERT INTO t1 VALUES
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');
unknown's avatar
unknown committed
80 81 82
INSERT INTO t1 VALUES
('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');
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
unknown's avatar
unknown committed
85 86 87 88 89 90 91 92 93 94 95
drop table t1;
create table t1
(
name_id int not null auto_increment,
name blob,
INDEX name_idx (name(5)),
primary key (name_id)
);
INSERT t1 VALUES(NULL,'/');
INSERT t1 VALUES(NULL,'[T,U]_axpby');
SELECT * FROM t1 WHERE name='[T,U]_axpy';
96
name_id	name
unknown's avatar
unknown committed
97
SELECT * FROM t1 WHERE name='[T,U]_axpby';
98 99
name_id	name
2	[T,U]_axpby
unknown's avatar
unknown committed
100 101 102 103 104 105 106 107 108
create table t2
(
name_id int not null auto_increment,
name char(255) binary,
INDEX name_idx (name(5)),
primary key (name_id)
);
INSERT t2 select * from t1;
SELECT * FROM t2 WHERE name='[T,U]_axpy';
109
name_id	name
unknown's avatar
unknown committed
110
SELECT * FROM t2 WHERE name='[T,U]_axpby';
111 112
name_id	name
2	[T,U]_axpby
113 114 115 116 117
CREATE TABLE t3 SELECT * FROM t2 WHERE name='[T,U]_axpby';
SELECT * FROM t2 WHERE name='[T,U]_axpby';
name_id	name
2	[T,U]_axpby
drop table t1,t2,t3;
unknown's avatar
unknown committed
118 119 120 121 122 123 124 125 126 127 128 129
create table t1
(
SEQNO                         numeric(12 ) not null,
MOTYPEID                 numeric(12 ) not null,
MOINSTANCEID     numeric(12 ) not null,
ATTRID                       numeric(12 ) not null,
VALUE                         varchar(120) not null,
primary key (SEQNO, MOTYPEID, MOINSTANCEID, ATTRID, VALUE )
);
INSERT INTO t1 VALUES (1, 1, 1, 1, 'a');
INSERT INTO t1 VALUES (1, 1, 1, 1, 'b');
INSERT INTO t1 VALUES (1, 1, 1, 1, 'a');
130
ERROR 23000: Duplicate entry '1-1-1-1-a' for key 'PRIMARY'
unknown's avatar
unknown committed
131 132 133 134 135
drop table t1;
CREATE TABLE t1 (
a tinytext NOT NULL,
b tinyint(3) unsigned NOT NULL default '0',
PRIMARY KEY (a(32),b)
unknown's avatar
unknown committed
136
) ENGINE=MyISAM;
unknown's avatar
unknown committed
137 138
INSERT INTO t1 VALUES ('a',1),('a',2);
SELECT * FROM t1 WHERE a='a' AND b=2;
139 140
a	b
a	2
unknown's avatar
unknown committed
141
SELECT * FROM t1 WHERE a='a' AND b in (2);
142 143
a	b
a	2
unknown's avatar
unknown committed
144
SELECT * FROM t1 WHERE a='a' AND b in (1,2);
145 146 147
a	b
a	1
a	2
unknown's avatar
unknown committed
148
drop table t1;
149 150
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);
show keys from t1;
151 152 153 154 155 156
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
t1	0	PRIMARY	1	d	A	0	NULL	NULL		BTREE		
t1	0	a	1	a	A	0	NULL	NULL		BTREE		
t1	0	e	1	e	A	0	NULL	NULL		BTREE		
t1	0	b	1	b	A	NULL	NULL	NULL	YES	BTREE		
t1	1	c	1	c	A	NULL	NULL	NULL	YES	BTREE		
157
drop table t1;
unknown's avatar
unknown committed
158 159 160
CREATE TABLE t1 (c CHAR(10) NOT NULL,i INT NOT NULL AUTO_INCREMENT,
UNIQUE (c,i));
INSERT INTO t1 (c) VALUES (NULL),(NULL);
unknown's avatar
unknown committed
161
Warnings:
162 163
Warning	1048	Column 'c' cannot be null
Warning	1048	Column 'c' cannot be null
unknown's avatar
unknown committed
164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190
SELECT * FROM t1;
c	i
	1
	2
INSERT INTO t1 (c) VALUES ('a'),('a');
SELECT * FROM t1;
c	i
	1
	2
a	1
a	2
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (c CHAR(10) NULL, i INT NOT NULL AUTO_INCREMENT,
UNIQUE (c,i));
INSERT INTO t1 (c) VALUES (NULL),(NULL);
SELECT * FROM t1;
c	i
NULL	1
NULL	2
INSERT INTO t1 (c) VALUES ('a'),('a');
SELECT * FROM t1;
c	i
NULL	1
NULL	2
a	1
a	2
drop table t1;
unknown's avatar
unknown committed
191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211
create table t1 (i int, a char(200), b text, unique (a), unique (b(300))) charset utf8;
insert t1 values (1, repeat('a',210), repeat('b', 310));
Warnings:
Warning	1265	Data truncated for column 'a' at row 1
insert t1 values (2, repeat(0xD0B1,215), repeat(0xD0B1, 310));
Warnings:
Warning	1265	Data truncated for column 'a' at row 1
select i, length(a), length(b), char_length(a), char_length(b) from t1;
i	length(a)	length(b)	char_length(a)	char_length(b)
1	200	310	200	310
2	400	620	200	310
select i from t1 where a=repeat(_utf8 'a',200);
i
1
select i from t1 where a=repeat(_utf8 0xD0B1,200);
i
2
select i from t1 where b=repeat(_utf8 'b',310);
i
1
drop table t1;
212 213 214 215 216 217 218
CREATE TABLE t1 (id int unsigned auto_increment, name char(50), primary key (id)) engine=myisam;
insert into t1 (name) values ('a'), ('b'),('c'),('d'),('e'),('f'),('g');
explain select 1 from t1 where id =2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	Using index
explain select 1 from t1 where id =2 or id=3;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
219
1	SIMPLE	t1	index	PRIMARY	PRIMARY	4	NULL	7	Using where; Using index
220 221 222 223 224 225
explain select name from t1 where id =2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	const	PRIMARY	PRIMARY	4	const	1	
ALTER TABLE t1 DROP PRIMARY KEY, ADD INDEX (id);
explain select 1 from t1 where id =2;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
226
1	SIMPLE	t1	ref	id	id	4	const	1	Using index
227
drop table t1;
228 229 230 231 232 233 234 235 236 237 238 239 240
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));
INSERT INTO t1 (numeropost,numreponse) VALUES ('1','1'),('1','2'),('2','3'),('2','4');
SELECT numeropost FROM t1 WHERE numreponse='1';
numeropost
1
EXPLAIN SELECT numeropost FROM t1 WHERE numreponse='1';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	const	numreponse	numreponse	4	const	1	Using index
FLUSH TABLES;
SELECT numeropost FROM t1 WHERE numreponse='1';
numeropost
1
drop table t1;
241 242 243 244
create table t1 (c varchar(30) character set utf8, t text character set utf8, unique (c(2)), unique (t(3))) engine=myisam;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
245 246
  `c` varchar(30) CHARACTER SET utf8 DEFAULT NULL,
  `t` text CHARACTER SET utf8,
247 248 249 250 251 252 253
  UNIQUE KEY `c` (`c`(2)),
  UNIQUE KEY `t` (`t`(3))
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert t1 values ('cccc', 'tttt'),
(0xD0B1212223D0B1D0B1D0B1D0B1D0B1, 0xD0B1D0B1212223D0B1D0B1D0B1D0B1),
(0xD0B1222123D0B1D0B1D0B1D0B1D0B1, 0xD0B1D0B1222123D0B1D0B1D0B1D0B1);
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 '\0431!' for key 'c'
259
insert t1 (t) values (0xD0B1D0B1212322D0B1D0B1D0B1D0B1);
260
ERROR 23000: Duplicate entry '\0431\0431!' for key 't'
261 262 263 264 265 266 267 268 269 270 271 272 273
select c from t1 where c='cccc';
c
cccc
select t from t1 where t='tttt';
t
tttt
select c from t1 where c=0xD0B1212223D0B1D0B1D0B1D0B1D0B1;
c
?!"#?????
select t from t1 where t=0xD0B1D0B1212223D0B1D0B1D0B1D0B1;
t
??!"#????
drop table t1;
274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290
DROP TABLE IF EXISTS t1;
Warnings:
Note	1051	Unknown table 't1'
CREATE TABLE t1 (
c1 int,
c2 varbinary(240),
UNIQUE KEY (c1),
KEY (c2)
) ENGINE=MyISAM;
INSERT INTO t1 VALUES (1,'\Z\Z\Z\Z');
INSERT INTO t1 VALUES (2,'\Z\Z\Z\Z\Z\Z');
INSERT INTO t1 VALUES (3,'\Z\Z\Z\Z');
select c1 from t1 where c2='\Z\Z\Z\Z';
c1
1
3
DELETE FROM t1 WHERE (c1 = 1);
unknown's avatar
unknown committed
291 292 293
check table t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
294 295 296 297
select c1 from t1 where c2='\Z\Z\Z\Z';
c1
3
DELETE FROM t1 WHERE (c1 = 3);
unknown's avatar
unknown committed
298 299 300
check table t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
301 302
select c1 from t1 where c2='\Z\Z\Z\Z';
c1
303 304 305 306 307 308 309 310
truncate table t1;
insert into t1 values(1,"aaaa"),(2,"aaab"),(3,"aaac"),(4,"aaccc");
delete from t1 where c1=3;
delete from t1 where c1=1;
delete from t1 where c1=4;
check table t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
unknown's avatar
unknown committed
311
drop table t1;
312 313
create table t1 (c char(10), index (c(0)));
ERROR HY000: Key part 'c' length cannot be 0
314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331
create table t1 (c char(10), index (c,c));
ERROR 42S21: Duplicate column name 'c'
create table t1 (c1 char(10), c2 char(10), index (c1,c2,c1));
ERROR 42S21: Duplicate column name 'c1'
create table t1 (c1 char(10), c2 char(10), index (c1,c1,c2));
ERROR 42S21: Duplicate column name 'c1'
create table t1 (c1 char(10), c2 char(10), index (c2,c1,c1));
ERROR 42S21: Duplicate column name 'c1'
create table t1 (c1 char(10), c2 char(10));
alter table t1 add key (c1,c1);
ERROR 42S21: Duplicate column name 'c1'
alter table t1 add key (c2,c1,c1);
ERROR 42S21: Duplicate column name 'c1'
alter table t1 add key (c1,c2,c1);
ERROR 42S21: Duplicate column name 'c1'
alter table t1 add key (c1,c1,c2);
ERROR 42S21: Duplicate column name 'c1'
drop table t1;
332
create table t1 (
333 334 335 336 337 338
i1 INT NOT NULL,
i2 INT NOT NULL,
UNIQUE i1idx (i1),
UNIQUE i2idx (i2));
desc t1;
Field	Type	Null	Key	Default	Extra
339 340
i1	int(11)	NO	PRI	NULL	
i2	int(11)	NO	UNI	NULL	
unknown's avatar
unknown committed
341 342 343 344 345 346 347 348
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `i1` int(11) NOT NULL,
  `i2` int(11) NOT NULL,
  UNIQUE KEY `i1idx` (`i1`),
  UNIQUE KEY `i2idx` (`i2`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
349 350
drop table t1;
create table t1 (
351 352 353 354 355 356 357 358 359 360 361 362 363 364 365
c1 int,
c2 varchar(20) not null,
primary key (c1),
key (c2(10))
) engine=myisam;
insert into t1 values (1,'');
insert into t1 values (2,' \t\tTest String');
insert into t1 values (3,' \n\tTest String');
update t1 set c2 = 'New Test String' where c1 = 1;
select * from t1;
c1	c2
1	New Test String
2	 		Test String
3	 
	Test String
unknown's avatar
unknown committed
366
drop table t1;
367 368 369 370
create table t1 (a varchar(10), b varchar(10), key(a(10),b(10)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
371 372
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(10) DEFAULT NULL,
373 374 375 376 377 378
  KEY `a` (`a`,`b`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
alter table t1 modify b varchar(20);
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
379 380
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(20) DEFAULT NULL,
381 382 383 384 385 386
  KEY `a` (`a`,`b`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
alter table t1 modify a varchar(20);
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
387 388
  `a` varchar(20) DEFAULT NULL,
  `b` varchar(20) DEFAULT NULL,
389 390 391
  KEY `a` (`a`,`b`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
392 393 394
create table t1 (a int not null primary key, b varchar(20) not null unique);
desc t1;
Field	Type	Null	Key	Default	Extra
395 396
a	int(11)	NO	PRI	NULL	
b	varchar(20)	NO	UNI	NULL	
397 398 399 400
drop table t1;
create table t1 (a int not null primary key, b int not null unique);
desc t1;
Field	Type	Null	Key	Default	Extra
401 402
a	int(11)	NO	PRI	NULL	
b	int(11)	NO	UNI	NULL	
403 404 405 406
drop table t1;
create table t1 (a int not null primary key, b varchar(20) not null, unique (b(10)));
desc t1;
Field	Type	Null	Key	Default	Extra
407 408
a	int(11)	NO	PRI	NULL	
b	varchar(20)	NO	UNI	NULL	
409 410 411 412
drop table t1;
create table t1 (a int not null primary key, b varchar(20) not null, c varchar(20) not null, unique(b(10),c(10)));
desc t1;
Field	Type	Null	Key	Default	Extra
413 414 415
a	int(11)	NO	PRI	NULL	
b	varchar(20)	NO	MUL	NULL	
c	varchar(20)	NO		NULL	
416
drop table t1;
417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432
create table t1 (
c1 int,
c2 char(12),
c3 varchar(123),
c4 timestamp,
index (c1),
index i1 (c1),
index i2 (c2),
index i3 (c3),
unique i4 (c4),
index i5 (c1, c2, c3, c4),
primary key (c2, c3),
index (c2, c4));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
433 434 435 436
  `c1` int(11) DEFAULT NULL,
  `c2` char(12) NOT NULL DEFAULT '',
  `c3` varchar(123) NOT NULL DEFAULT '',
  `c4` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
437
  PRIMARY KEY (`c2`,`c3`),
438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465
  UNIQUE KEY `i4` (`c4`),
  KEY `c1` (`c1`),
  KEY `i1` (`c1`),
  KEY `i2` (`c2`),
  KEY `i3` (`c3`),
  KEY `i5` (`c1`,`c2`,`c3`,`c4`),
  KEY `c2` (`c2`,`c4`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
alter table t1 drop index c1;
alter table t1 add index (c1);
alter table t1 add index (c1);
alter table t1 drop index i3;
alter table t1 add index i3 (c3);
alter table t1 drop index i2, drop index i4;
alter table t1 add index i2 (c2), add index i4 (c4);
alter table t1 drop index i2, drop index i4, add index i6 (c2, c4);
alter table t1 add index i2 (c2), add index i4 (c4), drop index i6;
alter table t1 drop index i2, drop index i4, add unique i4 (c4);
alter table t1 add index i2 (c2), drop index i4, add index i4 (c4);
alter table t1 drop index c2, add index (c2(4),c3(7));
alter table t1 drop index c2, add index (c2(4),c3(7));
alter table t1 add primary key (c1, c2), drop primary key;
alter table t1 drop primary key;
alter table t1 add primary key (c1, c2), drop primary key;
ERROR 42000: Can't DROP 'PRIMARY'; check that column/key exists
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
466 467 468 469
  `c1` int(11) NOT NULL DEFAULT '0',
  `c2` char(12) NOT NULL DEFAULT '',
  `c3` varchar(123) NOT NULL DEFAULT '',
  `c4` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
470 471 472 473 474 475 476 477 478 479 480 481 482
  KEY `i1` (`c1`),
  KEY `i5` (`c1`,`c2`,`c3`,`c4`),
  KEY `c1` (`c1`),
  KEY `c1_2` (`c1`),
  KEY `i3` (`c3`),
  KEY `i2` (`c2`),
  KEY `i4` (`c4`),
  KEY `c2` (`c2`(4),`c3`(7))
) ENGINE=MyISAM DEFAULT CHARSET=latin1
insert into t1 values(1, 'a', 'a', NULL);
insert into t1 values(1, 'b', 'b', NULL);
alter table t1 drop index i3, drop index i2, drop index i1;
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'
484
drop table t1;
485 486 487 488 489 490 491
CREATE TABLE t1( a TINYINT, KEY(a) ) ENGINE=MyISAM;
INSERT INTO t1 VALUES( 1 );
ALTER TABLE t1 DISABLE KEYS;
EXPLAIN SELECT MAX(a) FROM t1 FORCE INDEX(a);
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	
drop table t1;
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 523 524 525 526 527 528 529 530 531 532
CREATE TABLE t1 (
a INTEGER auto_increment PRIMARY KEY,
b INTEGER NOT NULL,
c INTEGER NOT NULL,
d CHAR(64)
);
CREATE TABLE t2 (
a INTEGER auto_increment PRIMARY KEY,
b INTEGER NOT NULL,
c SMALLINT NOT NULL,
d DATETIME NOT NULL,
e SMALLINT NOT NULL,
f INTEGER NOT NULL,
g INTEGER NOT NULL,  
h SMALLINT NOT NULL,
i INTEGER NOT NULL,
j INTEGER NOT NULL,
UNIQUE INDEX (b),
INDEX (b, d, e, f, g, h, i, j, c),
INDEX (c)
);
INSERT INTO t2 VALUES 
(NULL, 1, 254, '1000-01-01 00:00:00', 257, 0, 0, 0, 0, 0),
(NULL, 2, 1, '2004-11-30 12:00:00', 1, 0, 0, 0, 0, 0),
(NULL, 3, 1, '2004-11-30 12:00:00', 1, 0, 0, 2, -21600, 0),
(NULL, 4, 1, '2004-11-30 12:00:00', 1, 0, 0, 2, -10800, 0),
(NULL, 5, 1, '2004-11-30 12:00:00', 1, 0, 0, 5, -10800, 0),
(NULL, 6, 1, '2004-11-30 12:00:00', 102, 0, 0, 0, 0, 0),
(NULL, 7, 1, '2004-11-30 12:00:00', 105, 2, 0, 0, 0, 0),
(NULL, 8, 1, '2004-11-30 12:00:00', 105, 10, 0, 0, 0, 0);
INSERT INTO t1 (b, c, d) VALUES
(3388000, -553000, NULL),
(3388000, -553000, NULL);
SELECT *
FROM t2 c JOIN t1 pa ON c.b = pa.a 
WHERE c.c = 1
ORDER BY c.b, c.d
;
a	b	c	d	e	f	g	h	i	j	a	b	c	d
2	2	1	2004-11-30 12:00:00	1	0	0	0	0	0	2	3388000	-553000	NULL
DROP TABLE t1, t2;
unknown's avatar
unknown committed
533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577
create table t1(a int not null, key aa(a), 
b char(10) not null, unique key bb(b(1)), 
c char(4) not null, unique key cc(c));
desc t1;
Field	Type	Null	Key	Default	Extra
a	int(11)	NO	MUL	NULL	
b	char(10)	NO	UNI	NULL	
c	char(4)	NO	PRI	NULL	
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL,
  `b` char(10) NOT NULL,
  `c` char(4) NOT NULL,
  UNIQUE KEY `cc` (`c`),
  UNIQUE KEY `bb` (`b`(1)),
  KEY `aa` (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1(a int not null, key aa(a), 
b char(10) not null, unique key bb(b(1)),
c char(4) not null);
desc t1;
Field	Type	Null	Key	Default	Extra
a	int(11)	NO	MUL	NULL	
b	char(10)	NO	UNI	NULL	
c	char(4)	NO		NULL	
alter table t1 add unique key cc(c);
desc t1;
Field	Type	Null	Key	Default	Extra
a	int(11)	NO	MUL	NULL	
b	char(10)	NO	UNI	NULL	
c	char(4)	NO	PRI	NULL	
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) NOT NULL,
  `b` char(10) NOT NULL,
  `c` char(4) NOT NULL,
  UNIQUE KEY `cc` (`c`),
  UNIQUE KEY `bb` (`b`(1)),
  KEY `aa` (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
End of 5.0 tests
578 579 580 581 582 583 584 585 586 587 588 589 590 591 592
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a INT PRIMARY KEY AUTO_INCREMENT);
INSERT INTO t1 VALUES (), (), ();
SELECT 1 AS c1
FROM t1
ORDER BY (
SELECT 1 AS c2
FROM t1
GROUP BY GREATEST(LAST_INSERT_ID(), t1.a) ASC
LIMIT 1);
c1
1
1
1
DROP TABLE t1;
unknown's avatar
unknown committed
593 594 595 596 597 598 599 600 601 602 603 604 605 606
CREATE TABLE t1 (a INT, b INT, INDEX (a,b));
INSERT INTO t1 (a, b)
VALUES
(1,1), (1,2), (1,3), (1,4), (1,5),
(2,2), (2,3), (2,1), (3,1), (4,1), (4,2), (4,3), (4,4), (4,5), (4,6);
EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE 
(SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE
2	SUBQUERY	t1	range	NULL	a	5	NULL	8	Using index for group-by
SELECT 1 as RES FROM t1 AS t1_outer WHERE 
(SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12;
RES
DROP TABLE t1;