key.result 13.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 1
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 151 152 153 154 155 156 157
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;
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	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	
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:
unknown's avatar
unknown committed
162 163
Warning	1263	Column set to default value; NULL supplied to NOT NULL column 'c' at row 1
Warning	1263	Column set to default value; NULL supplied to NOT NULL column 'c' at row 2
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 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273
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` (
  `c` varchar(30) character set utf8 default NULL,
  `t` text character set utf8,
  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');
ERROR 23000: Duplicate entry 'cc22' for key 1
insert t1 (t) values ('ttt22');
ERROR 23000: Duplicate entry 'ttt22' for key 2
insert t1 (c) values (0xD0B1212322D0B1D0B1D0B1D0B1D0B1);
ERROR 23000: Duplicate entry 'б!#"' for key 1
insert t1 (t) values (0xD0B1D0B1212322D0B1D0B1D0B1D0B1);
ERROR 23000: Duplicate entry 'бб!#"б' for key 2
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 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347
create table t1 (
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
348
drop table t1;
349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373
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` (
  `a` varchar(10) default NULL,
  `b` varchar(10) default NULL,
  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` (
  `a` varchar(10) default NULL,
  `b` varchar(20) default NULL,
  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` (
  `a` varchar(20) default NULL,
  `b` varchar(20) default NULL,
  KEY `a` (`a`,`b`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398
create table t1 (a int not null primary key, b varchar(20) not null unique);
desc t1;
Field	Type	Null	Key	Default	Extra
a	int(11)	NO	PRI		
b	varchar(20)	NO	UNI		
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
a	int(11)	NO	PRI		
b	int(11)	NO	UNI		
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
a	int(11)	NO	PRI		
b	varchar(20)	NO	UNI		
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
a	int(11)	NO	PRI		
b	varchar(20)	NO	MUL		
c	varchar(20)	NO			
drop table t1;