key.result 7.33 KB
Newer Older
1
drop table if exists t1,t2,t3;
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
2
SET SQL_WARNINGS=1;
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
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','','','','');
venu@myvenu.com's avatar
venu@myvenu.com committed
36
Warnings:
monty@mysql.com's avatar
monty@mysql.com 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
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
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 79 80 81 82
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
('patnom','patauteur',0,'p.favre@cryo-networks.fr',NULL,NULL,'#p2sndnq6ae5g1u6t','essai\nsalut','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');
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 84 85
name	author	category	email	password	proxy	bitmap	msg	urlscol	urlhttp	timeout	nbcnx	creation	livinguntil	lang	type	subcat	subtype	reg	scs	capacity	userISP	CCident
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
86 87 88 89 90 91 92 93 94 95 96
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';
97
name_id	name
98
SELECT * FROM t1 WHERE name='[T,U]_axpby';
99 100
name_id	name
2	[T,U]_axpby
101 102 103 104 105 106 107 108 109
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';
110
name_id	name
111
SELECT * FROM t2 WHERE name='[T,U]_axpby';
112 113
name_id	name
2	[T,U]_axpby
114 115 116 117 118 119 120 121 122 123 124 125 126
drop table t1,t2;
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');
127
ERROR 23000: Duplicate entry '1-1-1-1-a' for key 1
128 129 130 131 132
drop table t1;
CREATE TABLE t1 (
a tinytext NOT NULL,
b tinyint(3) unsigned NOT NULL default '0',
PRIMARY KEY (a(32),b)
133
) ENGINE=MyISAM;
134 135
INSERT INTO t1 VALUES ('a',1),('a',2);
SELECT * FROM t1 WHERE a='a' AND b=2;
136 137
a	b
a	2
138
SELECT * FROM t1 WHERE a='a' AND b in (2);
139 140
a	b
a	2
141
SELECT * FROM t1 WHERE a='a' AND b in (1,2);
142 143 144
a	b
a	1
a	2
145
drop table t1;
146 147 148 149 150 151 152 153 154
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;
155 156 157
CREATE TABLE t1 (c CHAR(10) NOT NULL,i INT NOT NULL AUTO_INCREMENT,
UNIQUE (c,i));
INSERT INTO t1 (c) VALUES (NULL),(NULL);
venu@myvenu.com's avatar
venu@myvenu.com committed
158
Warnings:
monty@mysql.com's avatar
monty@mysql.com committed
159 160
Warning	1263	Data truncated, NULL supplied to NOT NULL column 'c' at row 1
Warning	1263	Data truncated, NULL supplied to NOT NULL column 'c' at row 2
161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187
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;
serg@serg.mylan's avatar
serg@serg.mylan committed
188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208
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;