1
drop table if exists t1,t2;
3
Period smallint(4) unsigned zerofill DEFAULT '0000' NOT NULL,
4
Varor_period smallint(4) unsigned DEFAULT '0' NOT NULL
6
INSERT INTO t1 VALUES (9410,9412);
14
auto int NOT NULL auto_increment,
15
fld1 int(6) unsigned zerofill DEFAULT '000000' NOT NULL,
16
companynr tinyint(2) unsigned zerofill DEFAULT '00' NOT NULL,
17
fld3 char(30) DEFAULT '' NOT NULL,
18
fld4 char(35) DEFAULT '' NOT NULL,
19
fld5 char(35) DEFAULT '' NOT NULL,
20
fld6 char(4) DEFAULT '' NOT NULL,
23
INSERT INTO t2 VALUES (1192,068305,00,'Colombo','hardware','colicky','');
24
INSERT INTO t2 VALUES (1193,000000,00,'nondecreasing','implant','thrillingly','');
25
select t2.fld3 from t2 where companynr = 58 and fld3 like "%imaginable%";
27
select fld3 from t2 where fld3 like "%cultivation" ;
29
select t2.fld3,companynr from t2 where companynr = 57+1 order by fld3;
31
select fld3,companynr from t2 where companynr = 58 order by fld3;
33
select fld3 from t2 order by fld3 desc limit 10;
35
select fld3 from t2 order by fld3 desc limit 5;
37
select fld3 from t2 order by fld3 desc limit 5,5;
39
select t2.fld3 from t2 where fld3 = 'honeysuckle';
41
select t2.fld3 from t2 where fld3 LIKE 'honeysuckl_';
43
select t2.fld3 from t2 where fld3 LIKE 'hon_ysuckl_';
45
select t2.fld3 from t2 where fld3 LIKE 'honeysuckle%';
47
select t2.fld3 from t2 where fld3 LIKE 'h%le';
49
select t2.fld3 from t2 where fld3 LIKE 'honeysuckle_';
51
select t2.fld3 from t2 where fld3 LIKE 'don_t_find_me_please%';
53
select t2.fld3 from t2 where fld3 >= 'honeysuckle' and fld3 <= 'honoring' order by fld3;
55
select fld1,fld3 from t2 where fld3="Colombo" or fld3 = "nondecreasing" order by fld3;
58
CREATE TABLE t1 (a VARCHAR(200), b TEXT, FULLTEXT (a,b));
59
INSERT INTO t1 VALUES('MySQL has now support', 'for full-text search'),
60
('Full-text indexes', 'are called collections'),
61
('Only MyISAM tables','support collections'),
62
('Function MATCH ... AGAINST()','is used to do a search'),
63
('Full-text search in MySQL', 'implements vector space model');
65
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment
66
t1 1 a 1 a NULL NULL NULL NULL YES FULLTEXT
67
t1 1 a 2 b NULL NULL NULL NULL YES FULLTEXT
68
select * from t1 where MATCH(a,b) AGAINST ("collections");
70
Only MyISAM tables support collections
71
Full-text indexes are called collections
72
explain extended select * from t1 where MATCH(a,b) AGAINST ("collections");
73
id select_type table type possible_keys key key_len ref rows filtered Extra
74
1 SIMPLE t1 fulltext a a 0 1 100.00 Using where
76
Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (match `test`.`t1`.`a`,`test`.`t1`.`b` against ('collections'))
77
select * from t1 where MATCH(a,b) AGAINST ("indexes");
79
Full-text indexes are called collections
80
select * from t1 where MATCH(a,b) AGAINST ("indexes collections");
82
Full-text indexes are called collections
83
Only MyISAM tables support collections
84
select * from t1 where MATCH(a,b) AGAINST ("only");
88
create table t1 (a int) engine=blackhole;
89
delete from t1 where a=10;
90
update t1 set a=11 where a=15;
91
insert into t1 values(1);
92
insert ignore into t1 values(1);
93
replace into t1 values(100);
94
create table t2 (a varchar(200)) engine=blackhole;
95
load data infile '../std_data_ln/words.dat' into table t2;
96
alter table t1 add b int;
97
alter table t1 drop b;
98
create table t3 like t1;
99
insert into t1 select * from t3;
100
replace into t1 select * from t3;
108
Log_name Pos Event_type Server_id End_log_pos Info
109
master-bin.000001 # Format_desc # # Server ver: VERSION, Binlog ver: 4
110
master-bin.000001 # Query # # use `test`; drop table t1,t2
111
master-bin.000001 # Query # # use `test`; create table t1 (a int) engine=blackhole
112
master-bin.000001 # Query # # use `test`; BEGIN
113
master-bin.000001 # Query # # use `test`; delete from t1 where a=10
114
master-bin.000001 # Query # # use `test`; COMMIT
115
master-bin.000001 # Query # # use `test`; BEGIN
116
master-bin.000001 # Query # # use `test`; update t1 set a=11 where a=15
117
master-bin.000001 # Query # # use `test`; COMMIT
118
master-bin.000001 # Query # # use `test`; BEGIN
119
master-bin.000001 # Query # # use `test`; insert into t1 values(1)
120
master-bin.000001 # Query # # use `test`; COMMIT
121
master-bin.000001 # Query # # use `test`; BEGIN
122
master-bin.000001 # Query # # use `test`; insert ignore into t1 values(1)
123
master-bin.000001 # Query # # use `test`; COMMIT
124
master-bin.000001 # Query # # use `test`; BEGIN
125
master-bin.000001 # Query # # use `test`; replace into t1 values(100)
126
master-bin.000001 # Query # # use `test`; COMMIT
127
master-bin.000001 # Query # # use `test`; create table t2 (a varchar(200)) engine=blackhole
128
master-bin.000001 # Query # # use `test`; BEGIN
129
master-bin.000001 # Begin_load_query # # ;file_id=#;block_len=581
130
master-bin.000001 # Execute_load_query # # use `test`; load data infile '../std_data_ln/words.dat' into table t2 ;file_id=#
131
master-bin.000001 # Query # # use `test`; COMMIT
132
master-bin.000001 # Query # # use `test`; alter table t1 add b int
133
master-bin.000001 # Query # # use `test`; alter table t1 drop b
134
master-bin.000001 # Query # # use `test`; create table t3 like t1
135
master-bin.000001 # Query # # use `test`; BEGIN
136
master-bin.000001 # Query # # use `test`; insert into t1 select * from t3
137
master-bin.000001 # Query # # use `test`; COMMIT
138
master-bin.000001 # Query # # use `test`; BEGIN
139
master-bin.000001 # Query # # use `test`; replace into t1 select * from t3
140
master-bin.000001 # Query # # use `test`; COMMIT
142
CREATE TABLE t1(a INT) ENGINE=BLACKHOLE;
143
INSERT DELAYED INTO t1 VALUES(1);
144
ERROR HY000: Binary logging not possible. Message: Row-based format required for this statement, but not allowed by this combination of engines
146
CREATE TABLE t1(a INT, b INT) ENGINE=BLACKHOLE;
147
DELETE FROM t1 WHERE a=10;
148
ALTER TABLE t1 ADD INDEX(a);
149
DELETE FROM t1 WHERE a=10;
150
ALTER TABLE t1 DROP INDEX a;
151
ALTER TABLE t1 ADD UNIQUE INDEX(a);
152
DELETE FROM t1 WHERE a=10;
153
ALTER TABLE t1 DROP INDEX a;
154
ALTER TABLE t1 ADD PRIMARY KEY(a);
155
DELETE FROM t1 WHERE a=10;
158
create table t1 (a int) engine=blackhole;
161
insert into t1 values(1);
164
insert into t1 values(2);
168
Log_name Pos Event_type Server_id End_log_pos Info
169
master-bin.000001 # Format_desc # # Server ver: VERSION, Binlog ver: 4
170
master-bin.000001 # Query # # use `test`; create table t1 (a int) engine=blackhole
171
master-bin.000001 # Query # # use `test`; BEGIN
172
master-bin.000001 # Query # # use `test`; insert into t1 values(1)
173
master-bin.000001 # Query # # use `test`; COMMIT
174
drop table if exists t1;
176
create table t1 (a int auto_increment, primary key (a)) engine=blackhole;
177
insert into t1 values (11), (NULL), (NULL), (NULL);
179
insert into t1 values (NULL), (33), (NULL);
181
insert into t1 values (55), (NULL);
182
show binlog events from <binlog_start>;
183
Log_name Pos Event_type Server_id End_log_pos Info
184
master-bin.000001 # Query 1 # use `test`; create table t1 (a int auto_increment, primary key (a)) engine=blackhole
185
master-bin.000001 # Query 1 # use `test`; BEGIN
186
master-bin.000001 # Intvar 1 # INSERT_ID=1
187
master-bin.000001 # Query 1 # use `test`; insert into t1 values (11), (NULL), (NULL), (NULL)
188
master-bin.000001 # Query 1 # use `test`; COMMIT
189
master-bin.000001 # Query 1 # use `test`; BEGIN
190
master-bin.000001 # Intvar 1 # INSERT_ID=3
191
master-bin.000001 # Query 1 # use `test`; insert into t1 values (NULL), (33), (NULL)
192
master-bin.000001 # Query 1 # use `test`; COMMIT
193
master-bin.000001 # Query 1 # use `test`; BEGIN
194
master-bin.000001 # Intvar 1 # INSERT_ID=5
195
master-bin.000001 # Query 1 # use `test`; insert into t1 values (55), (NULL)
196
master-bin.000001 # Query 1 # use `test`; COMMIT