~drizzle-trunk/drizzle/development

create table t1 (id int unsigned not null auto_increment, code tinyint unsigned not null, name char(20) not null, primary key (id), key (code), unique (name)) engine=MyISAM;

insert into t1 (code, name) values (1, 'Tim'), (1, 'Monty'), (2, 'David'), (2, 'Erik'), (3, 'Sasha'), (3, 'Jeremy'), (4, 'Matt');

select id, code, name from t1 order by id;

id code name

1 1 Tim

2 1 Monty

3 2 David

4 2 Erik

5 3 Sasha

6 3 Jeremy

7 4 Matt

update ignore t1 set id = 8, name = 'Sinisa' where id < 3;

select id, code, name from t1 order by id;

id code name

2 1 Monty

3 2 David

4 2 Erik

5 3 Sasha

6 3 Jeremy

7 4 Matt

8 1 Sinisa

update ignore t1 set id = id + 10, name = 'Ralph' where id < 4;

select id, code, name from t1 order by id;

id code name

3 2 David

4 2 Erik

5 3 Sasha

6 3 Jeremy

7 4 Matt

8 1 Sinisa

12 1 Ralph

drop table t1;

CREATE TABLE t1 (

id int(11) NOT NULL auto_increment,

parent_id int(11) DEFAULT '0' NOT NULL,

level tinyint(4) DEFAULT '0' NOT NULL,

PRIMARY KEY (id),

KEY parent_id (parent_id),

KEY level (level)

) engine=MyISAM;

INSERT INTO t1 VALUES (1,0,0),(3,1,1),(4,1,1),(8,2,2),(9,2,2),(17,3,2),(22,4,2),(24,4,2),(28,5,2),(29,5,2),(30,5,2),(31,6,2),(32,6,2),(33,6,2),(203,7,2),(202,7,2),(20,3,2),(157,0,0),(193,5,2),(40,7,2),(2,1,1),(15,2,2),(6,1,1),(34,6,2),(35,6,2),(16,3,2),(7,1,1),(36,7,2),(18,3,2),(26,5,2),(27,5,2),(183,4,2),(38,7,2),(25,5,2),(37,7,2),(21,4,2),(19,3,2),(5,1,1),(179,5,2);

update t1 set parent_id=parent_id+100;

select * from t1 where parent_id=102;

id parent_id level

8 102 2

9 102 2

15 102 2

update t1 set id=id+1000;

update t1 set id=1024 where id=1009;

Got one of the listed errors

select * from t1;

id parent_id level

1001 100 0

1003 101 1

1004 101 1

1008 102 2

1009 102 2

1017 103 2

1022 104 2

1024 104 2

1028 105 2

1029 105 2

1030 105 2

1031 106 2

1032 106 2

1033 106 2

1203 107 2

1202 107 2

1020 103 2

1157 100 0

1193 105 2

1040 107 2

1002 101 1

1015 102 2

1006 101 1

1034 106 2

1035 106 2

1016 103 2

1007 101 1

1036 107 2

1018 103 2

1026 105 2

1027 105 2

1183 104 2

1038 107 2

1025 105 2

1037 107 2

1021 104 2

1019 103 2

1005 101 1

1179 105 2

update ignore t1 set id=id+1;

select * from t1;

id parent_id level

1001 100 0

1003 101 1

100

1004 101 1

101

1008 102 2

102

1010 102 2

103

1017 103 2

104

1023 104 2

105

1024 104 2

106

1028 105 2

107

1029 105 2

108

1030 105 2

109

1031 106 2

110

1032 106 2

111

1033 106 2

112

1204 107 2

113

1203 107 2

114

1020 103 2

115

1158 100 0

116

1194 105 2

117

1041 107 2

118

1002 101 1

119

1015 102 2

120

1006 101 1

121

1034 106 2

122

1035 106 2

123

1016 103 2

124

1007 101 1

125

1036 107 2

126

1018 103 2

127

1026 105 2

128

1027 105 2

129

1184 104 2

130

1039 107 2

131

1025 105 2

132

1038 107 2

133

1022 104 2

134

1019 103 2

135

1005 101 1

136

1180 105 2

137

update ignore t1 set id=1023 where id=1010;

138

select * from t1 where parent_id=102;

139

id parent_id level

140

1008 102 2

141

1010 102 2

142

1015 102 2

143

explain select level from t1 where level=1;

144

id select_type table type possible_keys key key_len ref rows Extra

145

1 SIMPLE t1 ref level level 1 const # Using index

146

explain select level,id from t1 where level=1;

147

id select_type table type possible_keys key key_len ref rows Extra

148

1 SIMPLE t1 ref level level 1 const #

149

explain select level,id,parent_id from t1 where level=1;

150

id select_type table type possible_keys key key_len ref rows Extra

151

1 SIMPLE t1 ref level level 1 const #

152

select level,id from t1 where level=1;

153

level id

154

1 1003

155

1 1004

156

1 1002

157

1 1006

158

1 1007

159

1 1005

160

select level,id,parent_id from t1 where level=1;

161

level id parent_id

162

1 1003 101

163

1 1004 101

164

1 1002 101

165

1 1006 101

166

1 1007 101

167

1 1005 101

168

optimize table t1;

169

Table Op Msg_type Msg_text

170

test.t1 optimize status OK

171

show keys from t1;

172

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment

173

t1 0 PRIMARY 1 id A # NULL NULL BTREE

174

t1 1 parent_id 1 parent_id A # NULL NULL BTREE

175

t1 1 level 1 level A # NULL NULL BTREE

176

drop table t1;

177

CREATE TABLE t1 (

178

gesuchnr int(11) DEFAULT '0' NOT NULL,

179

benutzer_id int(11) DEFAULT '0' NOT NULL,

180

PRIMARY KEY (gesuchnr,benutzer_id)

181

) engine=MyISAM;

182

replace into t1 (gesuchnr,benutzer_id) values (2,1);

183

replace into t1 (gesuchnr,benutzer_id) values (1,1);

184

replace into t1 (gesuchnr,benutzer_id) values (1,1);

185

select * from t1;

186

gesuchnr benutzer_id

187

1 1

188

2 1

189

drop table t1;

190

create table t1 (a int) engine=MyISAM;

191

insert into t1 values (1), (2);

192

optimize table t1;

193

Table Op Msg_type Msg_text

194

test.t1 optimize status OK

195

delete from t1 where a = 1;

196

select * from t1;

197

198

199

check table t1;

200

Table Op Msg_type Msg_text

201

test.t1 check status OK

202

drop table t1;

203

create table t1 (a int,b varchar(20)) engine=MyISAM;

204

insert into t1 values (1,""), (2,"testing");

205

delete from t1 where a = 1;

206

select * from t1;

207

a b

208

2 testing

209

create index skr on t1 (a);

210

insert into t1 values (3,""), (4,"testing");

211

analyze table t1;

212

Table Op Msg_type Msg_text

213

test.t1 analyze status OK

214

show keys from t1;

215

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment

216

t1 1 skr 1 a A # NULL NULL YES BTREE

217

drop table t1;

218

create table t1 (a int,b varchar(20),key(a)) engine=MyISAM;

219

insert into t1 values (1,""), (2,"testing");

220

select * from t1 where a = 1;

221

a b

222

223

drop table t1;

224

CREATE TABLE t1 (

225

user_id int(10) DEFAULT '0' NOT NULL,

226

name varchar(100),

227

phone varchar(100),

228

ref_email varchar(100) DEFAULT '' NOT NULL,

229

detail varchar(200),

230

PRIMARY KEY (user_id,ref_email)

231

)engine=MyISAM;

232

INSERT INTO t1 VALUES (10292,'sanjeev','29153373','sansh777@hotmail.com','xxx'),(10292,'shirish','2333604','shirish@yahoo.com','ddsds'),(10292,'sonali','323232','sonali@bolly.com','filmstar');

233

select * from t1 where user_id=10292;

234

user_id name phone ref_email detail

235

10292 sanjeev 29153373 sansh777@hotmail.com xxx

236

10292 shirish 2333604 shirish@yahoo.com ddsds

237

10292 sonali 323232 sonali@bolly.com filmstar

238

INSERT INTO t1 VALUES (10291,'sanjeev','29153373','sansh777@hotmail.com','xxx'),(10293,'shirish','2333604','shirish@yahoo.com','ddsds');

239

select * from t1 where user_id=10292;

240

user_id name phone ref_email detail

241

10292 sanjeev 29153373 sansh777@hotmail.com xxx

242

10292 shirish 2333604 shirish@yahoo.com ddsds

243

10292 sonali 323232 sonali@bolly.com filmstar

244

select * from t1 where user_id>=10292;

245

user_id name phone ref_email detail

246

10292 sanjeev 29153373 sansh777@hotmail.com xxx

247

10292 shirish 2333604 shirish@yahoo.com ddsds

248

10292 sonali 323232 sonali@bolly.com filmstar

249

10293 shirish 2333604 shirish@yahoo.com ddsds

250

select * from t1 where user_id>10292;

251

user_id name phone ref_email detail

252

10293 shirish 2333604 shirish@yahoo.com ddsds

253

select * from t1 where user_id<10292;

254

user_id name phone ref_email detail

255

10291 sanjeev 29153373 sansh777@hotmail.com xxx

256

drop table t1;

257

CREATE TABLE t1 (a int not null, b int not null,c int not null,

258

key(a),primary key(a,b), unique(c),key(a),unique(b)) ENGINE = MyISAM;

259

show index from t1;

260

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment

261

t1 0 PRIMARY 1 a A # NULL NULL BTREE

262

t1 0 PRIMARY 2 b A # NULL NULL BTREE

263

t1 0 c 1 c A # NULL NULL BTREE

264

t1 0 b 1 b A # NULL NULL BTREE

265

t1 1 a 1 a A # NULL NULL BTREE

266

t1 1 a_2 1 a A # NULL NULL BTREE

267

drop table t1;

268

create table t1 (col1 int not null, col2 char(4) not null, primary key(col1)) ENGINE = MEMORY;

269

alter table t1 engine=MyISAM;

270

insert into t1 values ('1','1'),('5','2'),('2','3'),('3','4'),('4','4');

271

select * from t1;

272

col1 col2

273

1 1

274

5 2

275

2 3

276

3 4

277

4 4

278

update t1 set col2='7' where col1='4';

279

select * from t1;

280

col1 col2

281

1 1

282

5 2

283

2 3

284

3 4

285

4 7

286

alter table t1 add co3 int not null;

287

select * from t1;

288

col1 col2 co3

289

1 1 0

290

5 2 0

291

2 3 0

292

3 4 0

293

4 7 0

294

update t1 set col2='9' where col1='2';

295

select * from t1;

296

col1 col2 co3

297

1 1 0

298

5 2 0

299

2 9 0

300

3 4 0

301

4 7 0

302

drop table t1;

303

create table t1 (a int not null , b int, primary key (a)) engine = MyISAM;

304

create table t2 (a int not null , b int, primary key (a)) engine = MEMORY;

305

insert into t1 VALUES (1,3) , (2,3), (3,3);

306

select * from t1;

307

a b

308

1 3

309

2 3

310

3 3

311

insert into t2 select * from t1;

312

select * from t2;

313

a b

314

1 3

315

2 3

316

3 3

317

delete from t1 where b = 3;

318

select * from t1;

319

a b

320

insert into t1 select * from t2;

321

select * from t1;

322

a b

323

3 3

324

2 3

325

1 3

326

select * from t2;

327

a b

328

1 3

329

2 3

330

3 3

331

drop table t1,t2;

332

CREATE TABLE t1 (

333

id int(11) NOT NULL auto_increment,

334

ggid varchar(32) binary DEFAULT '' NOT NULL,

335

email varchar(64) DEFAULT '' NOT NULL,

336

passwd varchar(32) binary DEFAULT '' NOT NULL,

337

PRIMARY KEY (id),

338

UNIQUE ggid (ggid)

339

) ENGINE=MyISAM;

340

insert into t1 (ggid,passwd) values ('test1','xxx');

341

insert into t1 (ggid,passwd) values ('test2','yyy');

342

insert into t1 (ggid,passwd) values ('test2','this will fail');

343

ERROR 23000: Duplicate entry 'test2' for key 'ggid'

344

insert into t1 (ggid,id) values ('this will fail',1);

345

ERROR 23000: Duplicate entry '1' for key 'PRIMARY'

346

select * from t1 where ggid='test1';

347

id ggid email passwd

348

1 test1 xxx

349

select * from t1 where passwd='xxx';

350

id ggid email passwd

351

1 test1 xxx

352

select * from t1 where id=2;

353

id ggid email passwd

354

2 test2 yyy

355

replace into t1 (ggid,id) values ('this will work',1);

356

replace into t1 (ggid,passwd) values ('test2','this will work');

357

update t1 set id=100,ggid='test2' where id=1;

358

ERROR 23000: Duplicate entry 'test2' for key 'ggid'

359

select * from t1;

360

id ggid email passwd

361

1 this will work

362

3 test2 this will work

363

select * from t1 where id=1;

364

id ggid email passwd

365

1 this will work

366

select * from t1 where id=999;

367

id ggid email passwd

368

drop table t1;

369

CREATE TABLE t1 (

370

user_name varchar(12),

371

password text,

372

subscribed char(1),

373

user_id int(11) DEFAULT '0' NOT NULL,

374

quota bigint(20),

375

weight double,

376

access_date date,

377

access_time time,

378

approved datetime,

379

dummy_primary_key int(11) NOT NULL auto_increment,

380

PRIMARY KEY (dummy_primary_key)

381

) ENGINE=MyISAM;

382

INSERT INTO t1 VALUES ('user_0','somepassword','N',0,0,0,'2000-09-07','23:06:59','2000-09-07 23:06:59',1);

383

INSERT INTO t1 VALUES ('user_1','somepassword','Y',1,1,1,'2000-09-07','23:06:59','2000-09-07 23:06:59',2);

384

INSERT INTO t1 VALUES ('user_2','somepassword','N',2,2,1.4142135623731,'2000-09-07','23:06:59','2000-09-07 23:06:59',3);

385

INSERT INTO t1 VALUES ('user_3','somepassword','Y',3,3,1.7320508075689,'2000-09-07','23:06:59','2000-09-07 23:06:59',4);

386

INSERT INTO t1 VALUES ('user_4','somepassword','N',4,4,2,'2000-09-07','23:06:59','2000-09-07 23:06:59',5);

387

select user_name, password , subscribed, user_id, quota, weight, access_date, access_time, approved, dummy_primary_key from t1 order by user_name;

388

user_name password subscribed user_id quota weight access_date access_time approved dummy_primary_key

389

user_0 somepassword N 0 0 0 2000-09-07 23:06:59 2000-09-07 23:06:59 1

390

user_1 somepassword Y 1 1 1 2000-09-07 23:06:59 2000-09-07 23:06:59 2

391

user_2 somepassword N 2 2 1.4142135623731 2000-09-07 23:06:59 2000-09-07 23:06:59 3

392

user_3 somepassword Y 3 3 1.7320508075689 2000-09-07 23:06:59 2000-09-07 23:06:59 4

393

user_4 somepassword N 4 4 2 2000-09-07 23:06:59 2000-09-07 23:06:59 5

394

drop table t1;

395

CREATE TABLE t1 (

396

id int(11) NOT NULL auto_increment,

397

parent_id int(11) DEFAULT '0' NOT NULL,

398

level tinyint(4) DEFAULT '0' NOT NULL,

399

KEY (id),

400

KEY parent_id (parent_id),

401

KEY level (level)

402

) engine=MyISAM;

403

404

INSERT INTO t1 values (179,5,2);

405

update t1 set parent_id=parent_id+100;

406

select * from t1 where parent_id=102;

407

id parent_id level

408

8 102 2

409

9 102 2

410

15 102 2

411

update t1 set id=id+1000;

412

update t1 set id=1024 where id=1009;

413

select * from t1;

414

id parent_id level

415

1001 100 0

416

1003 101 1

417

1004 101 1

418

1008 102 2

419

1024 102 2

420

1017 103 2

421

1022 104 2

422

1024 104 2

423

1028 105 2

424

1029 105 2

425

1030 105 2

426

1031 106 2

427

1032 106 2

428

1033 106 2

429

1203 107 2

430

1202 107 2

431

1020 103 2

432

1157 100 0

433

1193 105 2

434

1040 107 2

435

1002 101 1

436

1015 102 2

437

1006 101 1

438

1034 106 2

439

1035 106 2

440

1016 103 2

441

1007 101 1

442

1036 107 2

443

1018 103 2

444

1026 105 2

445

1027 105 2

446

1183 104 2

447

1038 107 2

448

1025 105 2

449

1037 107 2

450

1021 104 2

451

1019 103 2

452

1005 101 1

453

1179 105 2

454

update ignore t1 set id=id+1;

455

select * from t1;

456

id parent_id level

457

1002 100 0

458

1004 101 1

459

1005 101 1

460

1009 102 2

461

1025 102 2

462

1018 103 2

463

1023 104 2

464

1025 104 2

465

1029 105 2

466

1030 105 2

467

1031 105 2

468

1032 106 2

469

1033 106 2

470

1034 106 2

471

1204 107 2

472

1203 107 2

473

1021 103 2

474

1158 100 0

475

1194 105 2

476

1041 107 2

477

1003 101 1

478

1016 102 2

479

1007 101 1

480

1035 106 2

481

1036 106 2

482

1017 103 2

483

1008 101 1

484

1037 107 2

485

1019 103 2

486

1027 105 2

487

1028 105 2

488

1184 104 2

489

1039 107 2

490

1026 105 2

491

1038 107 2

492

1022 104 2

493

1020 103 2

494

1006 101 1

495

1180 105 2

496

update ignore t1 set id=1023 where id=1010;

497

select * from t1 where parent_id=102;

498

id parent_id level

499

1009 102 2

500

1025 102 2

501

1016 102 2

502

explain select level from t1 where level=1;

503

id select_type table type possible_keys key key_len ref rows Extra

504

1 SIMPLE t1 ref level level 1 const # Using index

505

select level,id from t1 where level=1;

506

level id

507

1 1004

508

1 1005

509

1 1003

510

1 1007

511

1 1008

512

1 1006

513

select level,id,parent_id from t1 where level=1;

514

level id parent_id

515

1 1004 101

516

1 1005 101

517

1 1003 101

518

1 1007 101

519

1 1008 101

520

1 1006 101

521

select level,id from t1 where level=1 order by id;

522

level id

523

1 1003

524

1 1004

525

1 1005

526

1 1006

527

1 1007

528

1 1008

529

delete from t1 where level=1;

530

select * from t1;

531

id parent_id level

532

1002 100 0

533

1009 102 2

534

1025 102 2

535

1018 103 2

536

1023 104 2

537

1025 104 2

538

1029 105 2

539

1030 105 2

540

1031 105 2

541

1032 106 2

542

1033 106 2

543

1034 106 2

544

1204 107 2

545

1203 107 2

546

1021 103 2

547

1158 100 0

548

1194 105 2

549

1041 107 2

550

1016 102 2

551

1035 106 2

552

1036 106 2

553

1017 103 2

554

1037 107 2

555

1019 103 2

556

1027 105 2

557

1028 105 2

558

1184 104 2

559

1039 107 2

560

1026 105 2

561

1038 107 2

562

1022 104 2

563

1020 103 2

564

1180 105 2

565

drop table t1;

566

CREATE TABLE t1 (

567

sca_code char(6) NOT NULL,

568

cat_code char(6) NOT NULL,

569

sca_desc varchar(50),

570

lan_code char(2) NOT NULL,

571

sca_pic varchar(100),

572

sca_sdesc varchar(50),

573

sca_sch_desc varchar(16),

574

PRIMARY KEY (sca_code, cat_code, lan_code),

575

INDEX sca_pic (sca_pic)

576

) engine = MyISAM ;

577

INSERT INTO t1 ( sca_code, cat_code, sca_desc, lan_code, sca_pic, sca_sdesc, sca_sch_desc) VALUES ( 'PD', 'J', 'PENDANT', 'EN', NULL, NULL, 'PENDANT'),( 'RI', 'J', 'RING', 'EN', NULL, NULL, 'RING'),( 'QQ', 'N', 'RING', 'EN', 'not null', NULL, 'RING');

578

select count(*) from t1 where sca_code = 'PD';

579

count(*)

580

581

select count(*) from t1 where sca_code <= 'PD';

582

count(*)

583

584

select count(*) from t1 where sca_pic is null;

585

count(*)

586

587

alter table t1 drop index sca_pic, add index sca_pic (cat_code, sca_pic);

588

select count(*) from t1 where sca_code='PD' and sca_pic is null;

589

count(*)

590

591

select count(*) from t1 where cat_code='E';

592

count(*)

593

594

alter table t1 drop index sca_pic, add index (sca_pic, cat_code);

595

select count(*) from t1 where sca_code='PD' and sca_pic is null;

596

count(*)

597

598

select count(*) from t1 where sca_pic >= 'n';

599

count(*)

600

601

select sca_pic from t1 where sca_pic is null;

602

sca_pic

603

NULL

604

NULL

605

update t1 set sca_pic="test" where sca_pic is null;

606

delete from t1 where sca_code='pd';

607

drop table t1;

608

set @a:=now();

609

CREATE TABLE t1 (a int not null, b timestamp not null, primary key (a)) engine=MyISAM;

610

insert into t1 (a) values(1),(2),(3);

611

select t1.a from t1 natural join t1 as t2 where t1.b >= @a order by t1.a;

612

613

614

615

616

select a from t1 natural join t1 as t2 where b >= @a order by a;

617

618

619

620

621

update t1 set a=5 where a=1;

622

select a from t1;

623

624

625

626

627

drop table t1;

628

create table t1 (a varchar(100) not null, primary key(a), b int not null) engine=MyISAM;

629

insert into t1 values("hello",1),("world",2);

630

select * from t1 order by b desc;

631

a b

632

world 2

633

hello 1

634

optimize table t1;

635

Table Op Msg_type Msg_text

636

test.t1 optimize status OK

637

show keys from t1;

638

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_Comment

639

t1 0 PRIMARY 1 a A # NULL NULL BTREE

640

drop table t1;

641

create table t1 (i int, j int ) ENGINE=MyISAM;

642

insert into t1 values (1,2);

643

select * from t1 where i=1 and j=2;

644

i j

645

1 2

646

create index ax1 on t1 (i,j);

647

select * from t1 where i=1 and j=2;

648

i j

649

1 2

650

drop table t1;

651

CREATE TABLE t1 (

652

a int3 unsigned NOT NULL,

653

b int1 unsigned NOT NULL,

654

UNIQUE (a, b)

655

) ENGINE = MyISAM;

656

INSERT INTO t1 VALUES (1, 1);

657

SELECT MIN(B),MAX(b) FROM t1 WHERE t1.a = 1;

658

MIN(B) MAX(b)

659

1 1

660

drop table t1;

661

CREATE TABLE t1 (a int unsigned NOT NULL) engine=MyISAM;

662

INSERT INTO t1 VALUES (1);

663

SELECT * FROM t1;

664

665

666

DROP TABLE t1;

667

create table t1 (a int primary key,b int, c int, d int, e int, f int, g int, h int, i int, j int, k int, l int, m int, n int, o int, p int, q int, r int, s int, t int, u int, v int, w int, x int, y int, z int, a1 int, a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int, b1 int, b2 int, b3 int, b4 int, b5 int, b6 int) engine = MyISAM;

668

insert into t1 values (1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1);

669

explain select * from t1 where a > 0 and a < 50;

670

id select_type table type possible_keys key key_len ref rows Extra

671

1 SIMPLE t1 system PRIMARY NULL NULL NULL #

672

drop table t1;

673

create table t1 (id int NOT NULL,id2 int NOT NULL,id3 int NOT NULL,dummy1 char(30),primary key (id,id2),index index_id3 (id3)) engine=MyISAM;

674

insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL');

675

LOCK TABLES t1 WRITE;

676

insert into t1 values (99,1,2,'D'),(1,1,2,'D');

677

ERROR 23000: Duplicate entry '1-1' for key 'PRIMARY'

678

select id from t1;

679

680

681

682

683

684

select id from t1;

685

686

687

688

689

690

UNLOCK TABLES;

691

DROP TABLE t1;

692

create table t1 (id int NOT NULL,id2 int NOT NULL,id3 int NOT NULL,dummy1 char(30),primary key (id,id2),index index_id3 (id3)) engine=MyISAM;

693

insert into t1 values (0,0,0,'ABCDEFGHIJ'),(2,2,2,'BCDEFGHIJK'),(1,1,1,'CDEFGHIJKL');

694

LOCK TABLES t1 WRITE;

695

begin;

696

insert into t1 values (99,1,2,'D'),(1,1,2,'D');

697

ERROR 23000: Duplicate entry '1-1' for key 'PRIMARY'

698

select id from t1;

699

700

701

702

703

704

insert ignore into t1 values (100,1,2,'D'),(1,1,99,'D');

705

commit;

706

select id,id3 from t1;

707

id id3

708

0 0

709

2 2

710

1 1

711

99 2

712

100 2

713

UNLOCK TABLES;

714

DROP TABLE t1;

715

create table t1 (a char(20), unique (a(5))) engine=MyISAM;

716

drop table t1;

717

create table t1 (a char(20), index (a(5))) engine=MyISAM;

718

show create table t1;

719

Table Create Table

720

t1 CREATE TABLE `t1` (

721

`a` char(20) DEFAULT NULL,

722

KEY `a` (`a`(5))

723

) ENGINE=MyISAM DEFAULT CHARSET=latin1

724

drop table t1;

725

create temporary table t1 (a int not null auto_increment, primary key(a)) engine=MyISAM;

726

insert into t1 values (NULL),(NULL),(NULL);

727

delete from t1 where a=3;

728

insert into t1 values (NULL);

729

select * from t1;

730

731

732

733

734

alter table t1 add b int;

735

select * from t1;

736

a b

737

1 NULL

738

2 NULL

739

4 NULL

740

drop table t1;

741

create table t1

742

(

743

id int auto_increment primary key,

744

name varchar(32) not null,

745

value text not null,

746

uid int not null,

747

unique key(name,uid)

748

) engine=MyISAM;

749

insert into t1 values (1,'one','one value',101),

750

(2,'two','two value',102),(3,'three','three value',103);

751

set insert_id=5;

752

replace into t1 (value,name,uid) values ('other value','two',102);

753

delete from t1 where uid=102;

754

set insert_id=5;

755

replace into t1 (value,name,uid) values ('other value','two',102);

756

set insert_id=6;

757

replace into t1 (value,name,uid) values ('other value','two',102);

758

select * from t1;

759

id name value uid

760

1 one one value 101

761

3 three three value 103

762

6 two other value 102

763

drop table t1;

764

create database mysqltest;

765

create table mysqltest.t1 (a int not null) engine= MyISAM;

766

insert into mysqltest.t1 values(1);

767

create table mysqltest.t2 (a int not null) engine= MEMORY;

768

insert into mysqltest.t2 values(1);

769

create table mysqltest.t3 (a int not null) engine= MEMORY;

770

insert into mysqltest.t3 values(1);

771

commit;

772

drop database mysqltest;

773

show tables from mysqltest;

774

ERROR 42000: Unknown database 'mysqltest'

775

set autocommit=0;

776

create table t1 (a int not null) engine= MyISAM;

777

insert into t1 values(1),(2);

778

truncate table t1;

779

commit;

780

truncate table t1;

781

truncate table t1;

782

select * from t1;

783

784

insert into t1 values(1),(2);

785

delete from t1;

786

select * from t1;

787

788

commit;

789

drop table t1;

790

set autocommit=1;

791

create table t1 (a int not null) engine= MyISAM;

792

insert into t1 values(1),(2);

793

truncate table t1;

794

insert into t1 values(1),(2);

795

select * from t1;

796

797

798

799

truncate table t1;

800

insert into t1 values(1),(2);

801

delete from t1;

802

select * from t1;

803

804

drop table t1;

805

create table t1 (a int not null, b int not null, c int not null, primary key (a),key(b)) engine=MyISAM;

806

insert into t1 values (3,3,3),(1,1,1),(2,2,2),(4,4,4);

807

explain select * from t1 order by a;

808

id select_type table type possible_keys key key_len ref rows Extra

809

1 SIMPLE t1 ALL NULL NULL NULL NULL # Using filesort

810

explain select * from t1 order by b;

811

id select_type table type possible_keys key key_len ref rows Extra

812

1 SIMPLE t1 ALL NULL NULL NULL NULL # Using filesort

813

explain select * from t1 order by c;

814

id select_type table type possible_keys key key_len ref rows Extra

815

1 SIMPLE t1 ALL NULL NULL NULL NULL # Using filesort

816

explain select a from t1 order by a;

817

id select_type table type possible_keys key key_len ref rows Extra

818

1 SIMPLE t1 index NULL PRIMARY 4 NULL # Using index

819

explain select b from t1 order by b;

820

id select_type table type possible_keys key key_len ref rows Extra

821

1 SIMPLE t1 index NULL b 4 NULL # Using index

822

explain select a,b from t1 order by b;

823

id select_type table type possible_keys key key_len ref rows Extra

824

1 SIMPLE t1 ALL NULL NULL NULL NULL # Using filesort

825

explain select a,b from t1;

826

id select_type table type possible_keys key key_len ref rows Extra

827

1 SIMPLE t1 ALL NULL NULL NULL NULL #

828

explain select a,b,c from t1;

829

id select_type table type possible_keys key key_len ref rows Extra

830

1 SIMPLE t1 ALL NULL NULL NULL NULL #

831

drop table t1;

832

create table t1 (t int not null default 1, key (t)) engine=MyISAM;

833

desc t1;

834

Field Type Null Key Default Extra

835

t int(11) NO MUL 1

836

drop table t1;

837

CREATE TABLE t1 (

838

number bigint(20) NOT NULL default '0',

839

cname char(15) NOT NULL default '',

840

carrier_id smallint(6) NOT NULL default '0',

841

privacy tinyint(4) NOT NULL default '0',

842

last_mod_date timestamp NOT NULL,

843

last_mod_id smallint(6) NOT NULL default '0',

844

last_app_date timestamp NOT NULL,

845

last_app_id smallint(6) default '-1',

846

version smallint(6) NOT NULL default '0',

847

assigned_scps int(11) default '0',

848

status tinyint(4) default '0'

849

) ENGINE=MyISAM;

850

INSERT INTO t1 VALUES (4077711111,'SeanWheeler',90,2,20020111112846,500,00000000000000,-1,2,3,1);

851

INSERT INTO t1 VALUES (9197722223,'berry',90,3,20020111112809,500,20020102114532,501,4,10,0);

852

INSERT INTO t1 VALUES (650,'San Francisco',0,0,20011227111336,342,00000000000000,-1,1,24,1);

853

INSERT INTO t1 VALUES (302467,'Sue\'s Subshop',90,3,20020109113241,500,20020102115111,501,7,24,0);

854

INSERT INTO t1 VALUES (6014911113,'SudzCarwash',520,1,20020102115234,500,20020102115259,501,33,32768,0);

855

INSERT INTO t1 VALUES (333,'tubs',99,2,20020109113440,501,20020109113440,500,3,10,0);

856

CREATE TABLE t2 (

857

number bigint(20) NOT NULL default '0',

858

cname char(15) NOT NULL default '',

859

carrier_id smallint(6) NOT NULL default '0',

860

privacy tinyint(4) NOT NULL default '0',

861

last_mod_date timestamp NOT NULL,

862

last_mod_id smallint(6) NOT NULL default '0',

863

last_app_date timestamp NOT NULL,

864

last_app_id smallint(6) default '-1',

865

version smallint(6) NOT NULL default '0',

866

assigned_scps int(11) default '0',

867

status tinyint(4) default '0'

868

) ENGINE=MyISAM;

869

INSERT INTO t2 VALUES (4077711111,'SeanWheeler',0,2,20020111112853,500,00000000000000,-1,2,3,1);

870

INSERT INTO t2 VALUES (9197722223,'berry',90,3,20020111112818,500,20020102114532,501,4,10,0);

871

INSERT INTO t2 VALUES (650,'San Francisco',90,0,20020109113158,342,00000000000000,-1,1,24,1);

872

INSERT INTO t2 VALUES (333,'tubs',99,2,20020109113453,501,20020109113453,500,3,10,0);

873

select * from t1;

874

number cname carrier_id privacy last_mod_date last_mod_id last_app_date last_app_id version assigned_scps status

875

4077711111 SeanWheeler 90 2 2002-01-11 11:28:46 500 0000-00-00 00:00:00 -1 2 3 1

876

9197722223 berry 90 3 2002-01-11 11:28:09 500 2002-01-02 11:45:32 501 4 10 0

877

650 San Francisco 0 0 2001-12-27 11:13:36 342 0000-00-00 00:00:00 -1 1 24 1

878

302467 Sue's Subshop 90 3 2002-01-09 11:32:41 500 2002-01-02 11:51:11 501 7 24 0

879

6014911113 SudzCarwash 520 1 2002-01-02 11:52:34 500 2002-01-02 11:52:59 501 33 32768 0

880

333 tubs 99 2 2002-01-09 11:34:40 501 2002-01-09 11:34:40 500 3 10 0

881

select * from t2;

882

number cname carrier_id privacy last_mod_date last_mod_id last_app_date last_app_id version assigned_scps status

883

4077711111 SeanWheeler 0 2 2002-01-11 11:28:53 500 0000-00-00 00:00:00 -1 2 3 1

884

9197722223 berry 90 3 2002-01-11 11:28:18 500 2002-01-02 11:45:32 501 4 10 0

885

650 San Francisco 90 0 2002-01-09 11:31:58 342 0000-00-00 00:00:00 -1 1 24 1

886

333 tubs 99 2 2002-01-09 11:34:53 501 2002-01-09 11:34:53 500 3 10 0

887

delete t1, t2 from t1 left join t2 on t1.number=t2.number where (t1.carrier_id=90 and t1.number=t2.number) or (t2.carrier_id=90 and t1.number=t2.number) or (t1.carrier_id=90 and t2.number is null);

888

select * from t1;

889

number cname carrier_id privacy last_mod_date last_mod_id last_app_date last_app_id version assigned_scps status

890

6014911113 SudzCarwash 520 1 2002-01-02 11:52:34 500 2002-01-02 11:52:59 501 33 32768 0

891

333 tubs 99 2 2002-01-09 11:34:40 501 2002-01-09 11:34:40 500 3 10 0

892

select * from t2;

893

number cname carrier_id privacy last_mod_date last_mod_id last_app_date last_app_id version assigned_scps status

894

333 tubs 99 2 2002-01-09 11:34:53 501 2002-01-09 11:34:53 500 3 10 0

895

select * from t2;

896

number cname carrier_id privacy last_mod_date last_mod_id last_app_date last_app_id version assigned_scps status

897

333 tubs 99 2 2002-01-09 11:34:53 501 2002-01-09 11:34:53 500 3 10 0

898

drop table t1,t2;

899

create table t1 (id int unsigned not null auto_increment, code tinyint unsigned not null, name char(20) not null, primary key (id), key (code), unique (name)) engine=MyISAM;

900

BEGIN;

901

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

902

SELECT @@tx_isolation,@@global.tx_isolation;

903

@@tx_isolation @@global.tx_isolation

904

SERIALIZABLE REPEATABLE-READ

905

insert into t1 (code, name) values (1, 'Tim'), (1, 'Monty'), (2, 'David');

906

select id, code, name from t1 order by id;

907

id code name

908

1 1 Tim

909

2 1 Monty

910

3 2 David

911

COMMIT;

912

BEGIN;

913

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

914

insert into t1 (code, name) values (2, 'Erik'), (3, 'Sasha');

915

select id, code, name from t1 order by id;

916

id code name

917

1 1 Tim

918

2 1 Monty

919

3 2 David

920

4 2 Erik

921

5 3 Sasha

922

COMMIT;

923

BEGIN;

924

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

925

insert into t1 (code, name) values (3, 'Jeremy'), (4, 'Matt');

926

select id, code, name from t1 order by id;

927

id code name

928

1 1 Tim

929

2 1 Monty

930

3 2 David

931

4 2 Erik

932

5 3 Sasha

933

6 3 Jeremy

934

7 4 Matt

935

COMMIT;

936

DROP TABLE t1;

937

create table t1 (n int(10), d int(10)) engine=MyISAM;

938

create table t2 (n int(10), d int(10)) engine=MyISAM;

939

insert into t1 values(1,1),(1,2);

940

insert into t2 values(1,10),(2,20);

941

UPDATE t1,t2 SET t1.d=t2.d,t2.d=30 WHERE t1.n=t2.n;

942

select * from t1;

943

n d

944

1 10

945

1 10

946

select * from t2;

947

n d

948

1 30

949

2 20

950

drop table t1,t2;

951

create table t1 (a int, b int) engine=MyISAM;

952

insert into t1 values(20,null);

953

select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on

954

t2.b=t3.a;

955

b ifnull(t2.b,"this is null")

956

NULL this is null

957

select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on

958

t2.b=t3.a order by 1;

959

b ifnull(t2.b,"this is null")

960

NULL this is null

961

insert into t1 values(10,null);

962

select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on

963

t2.b=t3.a order by 1;

964

b ifnull(t2.b,"this is null")

965

NULL this is null

966

NULL this is null

967

drop table t1;

968

create table t1 (a varchar(10) not null) engine = MEMORY;

969

create table t2 (b varchar(10) not null unique) engine=MyISAM;

970

select t1.a from t1,t2 where t1.a=t2.b;

971

972

drop table t1,t2;

973

create table t1 (a int not null, b int, primary key (a)) engine = MyISAM;

974

create table t2 (a int not null, b int, primary key (a)) engine = MyISAM;

975

insert into t1 values (10, 20);

976

insert into t2 values (10, 20);

977

update t1, t2 set t1.b = 150, t2.b = t1.b where t2.a = t1.a and t1.a = 10;

978

drop table t1,t2;

979

CREATE TABLE t1 (a int not null primary key, b int not null, unique (b)) engine=MyISAM;

980

INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);

981

UPDATE t1 set a=a+100 where b between 2 and 3 and a < 1000;

982

SELECT * from t1;

983

a b

984

1 1

985

102 2

986

103 3

987

4 4

988

5 5

989

6 6

990

7 7

991

8 8

992

9 9

993

drop table t1;

994

CREATE TABLE t1 (a int not null primary key, b int not null, key (b)) engine=MyISAM;

995

CREATE TABLE t2 (a int not null primary key, b int not null, key (b)) engine=MyISAM;

996

INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(10,10),(11,11),(12,12);

997

INSERT INTO t2 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);

998

update t1,t2 set t1.a=t1.a+100;

999

select * from t1;

1000

a b

1001

101 1

1002

102 2

1003

103 3

1004

104 4

1005

105 5

1006

106 6

1007

107 7

1008

108 8

1009

109 9

1010

110 10

1011

111 11

1012

112 12

1013

update t1,t2 set t1.a=t1.a+100 where t1.a=101;

1014

select * from t1;

1015

a b

1016

201 1

1017

102 2

1018

103 3

1019

104 4

1020

105 5

1021

106 6

1022

107 7

1023

108 8

1024

109 9

1025

110 10

1026

111 11

1027

112 12

1028

update t1,t2 set t1.b=t1.b+10 where t1.b=2;

1029

select * from t1;

1030

a b

1031

201 1

1032

102 12

1033

103 3

1034

104 4

1035

105 5

1036

106 6

1037

107 7

1038

108 8

1039

109 9

1040

110 10

1041

111 11

1042

112 12

1043

update t1,t2 set t1.b=t1.b+2,t2.b=t1.b+10 where t1.b between 3 and 5 and t1.a=t2.a+100;

1044

select * from t1;

1045

a b

1046

201 1

1047

102 12

1048

103 5

1049

104 6

1050

105 7

1051

106 6

1052

107 7

1053

108 8

1054

109 9

1055

110 10

1056

111 11

1057

112 12

1058

select * from t2;

1059

a b

1060

1 1

1061

2 2

1062

3 13

1063

4 14

1064

5 15

1065

6 6

1066

7 7

1067

8 8

1068

9 9

1069

drop table t1,t2;

1070

CREATE TABLE t2 ( NEXT_T BIGINT NOT NULL PRIMARY KEY) ENGINE=MEMORY;

1071

CREATE TABLE t1 ( B_ID INTEGER NOT NULL PRIMARY KEY) ENGINE=MyISAM;

1072

SET AUTOCOMMIT=0;

1073

INSERT INTO t1 ( B_ID ) VALUES ( 1 );

1074

INSERT INTO t2 ( NEXT_T ) VALUES ( 1 );

1075

ROLLBACK;

1076

Warnings:

1077

Warning 1196 Some non-transactional changed tables couldn't be rolled back

1078

SELECT * FROM t1;

1079

B_ID

1080

1081

drop table t1,t2;

1082

create table t1 ( pk int primary key, parent int not null, child int not null, index (parent) ) engine = MyISAM;

1083

insert into t1 values (1,0,4), (2,1,3), (3,2,1), (4,1,2);

1084

select distinct parent,child from t1 order by parent;

1085

parent child

1086

0 4

1087

1 2

1088

1 3

1089

2 1

1090

drop table t1;

1091

create table t1 (a int not null auto_increment primary key, b int, c int, key(c)) engine=MyISAM;

1092

create table t2 (a int not null auto_increment primary key, b int) ENGINE = MEMORY;

1093

insert into t1 (b) values (null),(null),(null),(null),(null),(null),(null);

1094

insert into t2 (a) select b from t1;

1095

insert into t1 (b) select b from t2;

1096

insert into t2 (a) select b from t1;

1097

insert into t1 (a) select b from t2;

1098

insert into t2 (a) select b from t1;

1099

insert into t1 (a) select b from t2;

1100

insert into t2 (a) select b from t1;

1101

insert into t1 (a) select b from t2;

1102

insert into t2 (a) select b from t1;

1103

insert into t1 (a) select b from t2;

1104

insert into t2 (a) select b from t1;

1105

insert into t1 (a) select b from t2;

1106

insert into t2 (a) select b from t1;

1107

insert into t1 (a) select b from t2;

1108

insert into t2 (a) select b from t1;

1109

insert into t1 (a) select b from t2;

1110

insert into t2 (a) select b from t1;

1111

insert into t1 (a) select b from t2;

1112

select count(*) from t1;

1113

count(*)

1114

29267

1115

explain select * from t1 where c between 1 and 2500;

1116

id select_type table type possible_keys key key_len ref rows Extra

1117

1 SIMPLE t1 range c c 5 NULL # Using index condition; Using MRR

1118

update t1 set c=a;

1119

explain select * from t1 where c between 1 and 2500;

1120

id select_type table type possible_keys key key_len ref rows Extra

1121

1 SIMPLE t1 range c c 5 NULL # Using index condition; Using MRR

1122

drop table t1,t2;

1123

create table t1 (id int primary key auto_increment, fk int, index index_fk (fk)) engine=MyISAM;

1124

insert into t1 (id) values (null),(null),(null),(null),(null);

1125

update t1 set fk=69 where fk is null order by id limit 1;

1126

SELECT * from t1;

1127

id fk

1128

1 69

1129

2 NULL

1130

3 NULL

1131

4 NULL

1132

5 NULL

1133

drop table t1;

1134

create table t1 (a int not null, b int not null, key (a)) engine=MyISAM;

1135

insert into t1 values (1,1),(1,2),(1,3),(3,1),(3,2),(3,3),(3,1),(3,2),(3,3),(2,1),(2,2),(2,3);

1136

SET @tmp=0;

1137

update t1 set b=(@tmp:=@tmp+1) order by a;

1138

update t1 set b=99 where a=1 order by b asc limit 1;

1139

update t1 set b=100 where a=1 order by b desc limit 2;

1140

update t1 set a=a+10+b where a=1 order by b;

1141

select * from t1 order by a,b;

1142

a b

1143

2 4

1144

2 5

1145

2 6

1146

3 7

1147

3 8

1148

3 9

1149

3 10

1150

3 11

1151

3 12

1152

13 2

1153

111 100

1154

111 100

1155

drop table t1;

1156

create table t1 ( c char(8) not null ) engine=MyISAM;

1157

insert into t1 values ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9');

1158

insert into t1 values ('A'),('B'),('C'),('D'),('E'),('F');

1159

alter table t1 add b char(8) not null;

1160

alter table t1 add a char(8) not null;

1161

alter table t1 add primary key (a,b,c);

1162

update t1 set a=c, b=c;

1163

create table t2 (c char(8) not null, b char(8) not null, a char(8) not null, primary key(a,b,c)) engine=MyISAM;

1164

insert into t2 select * from t1;

1165

delete t1,t2 from t2,t1 where t1.a<'B' and t2.b=t1.b;

1166

drop table t1,t2;

1167

SET AUTOCOMMIT=1;

1168

create table t1 (a integer auto_increment primary key) engine=MyISAM;

1169

insert into t1 (a) values (NULL),(NULL);

1170

truncate table t1;

1171

insert into t1 (a) values (NULL),(NULL);

1172

SELECT * from t1;

1173

1174

1175

1176

drop table t1;

1177

CREATE TABLE t1 (col1 int(1))ENGINE=MyISAM;

1178

CREATE TABLE t2 (col1 int(1),stamp TIMESTAMP,INDEX stamp_idx

1179

(stamp))ENGINE=MyISAM;

1180

insert into t1 values (1),(2),(3);

1181

insert into t2 values (1, 20020204130000),(2, 20020204130000),(4,20020204310000 ),(5,20020204230000);

1182

Warnings:

1183

Warning 1265 Data truncated for column 'stamp' at row 3

1184

SELECT col1 FROM t1 UNION SELECT col1 FROM t2 WHERE stamp <

1185

'20020204120000' GROUP BY col1;

1186

col1

1187

1188

1189

1190

1191

drop table t1,t2;

1192

CREATE TABLE t1 (

1193

`id` int(10) unsigned NOT NULL auto_increment,

1194

`id_object` int(10) unsigned default '0',

1195

`id_version` int(10) unsigned NOT NULL default '1',

1196

`label` varchar(100) NOT NULL default '',

1197

`description` text,

1198

PRIMARY KEY (`id`),

1199

KEY `id_object` (`id_object`),

1200

KEY `id_version` (`id_version`)

1201

) ENGINE=MyISAM;

1202

INSERT INTO t1 VALUES("6", "3382", "9", "Test", NULL), ("7", "102", "5", "Le Pekin (Test)", NULL),("584", "1794", "4", "Test de resto", NULL),("837", "1822", "6", "Test 3", NULL),("1119", "3524", "1", "Societe Test", NULL),("1122", "3525", "1", "Fournisseur Test", NULL);

1203

CREATE TABLE t2 (

1204

`id` int(10) unsigned NOT NULL auto_increment,

1205

`id_version` int(10) unsigned NOT NULL default '1',

1206

PRIMARY KEY (`id`),

1207

KEY `id_version` (`id_version`)

1208

) ENGINE=MyISAM;

1209

INSERT INTO t2 VALUES("3524", "1"),("3525", "1"),("1794", "4"),("102", "5"),("1822", "6"),("3382", "9");

1210

SELECT t2.id, t1.`label` FROM t2 INNER JOIN

1211

(SELECT t1.id_object as id_object FROM t1 WHERE t1.`label` LIKE '%test%') AS lbl

1212

ON (t2.id = lbl.id_object) INNER JOIN t1 ON (t2.id = t1.id_object);

1213

id label

1214

3382 Test

1215

102 Le Pekin (Test)

1216

1794 Test de resto

1217

1822 Test 3

1218

3524 Societe Test

1219

3525 Fournisseur Test

1220

drop table t1,t2;

1221

create table t1 (a int, b varchar(200), c text not null) checksum=1 engine=MyISAM;

1222

create table t2 (a int, b varchar(200), c text not null) checksum=0 engine=MyISAM;

1223

create table t3 (a int, b varchar(200), c varchar(200) not null) checksum=1 engine=MEMORY;

1224

create table t4 (a int, b varchar(200), c varchar(200) not null) checksum=0 engine=MEMORY;

1225

create table t5 (a int, b varchar(200), c text not null) checksum=1 engine=MyISAM;

1226

create table t6 (a int, b varchar(200), c text not null) checksum=0 engine=MyISAM;

1227

insert t1 values (1, "aaa", "bbb"), (NULL, "", "ccccc"), (0, NULL, "");

1228

insert t2 select * from t1;

1229

insert t3 select * from t1;

1230

insert t4 select * from t1;

1231

insert t5 select * from t1;

1232

insert t6 select * from t1;

1233

checksum table t1, t2, t3, t4, t5, t6, t7 quick;

1234

Table Checksum

1235

test.t1 2948697075

1236

test.t2 NULL

1237

test.t3 NULL

1238

test.t4 NULL

1239

test.t5 2948697075

1240

test.t6 NULL

1241

test.t7 NULL

1242

Warnings:

1243

Error 1146 Table 'test.t7' doesn't exist

1244

checksum table t1, t2, t3, t4, t5, t6, t7;

1245

Table Checksum

1246

test.t1 2948697075

1247

test.t2 2948697075

1248

test.t3 2948697075

1249

test.t4 2948697075

1250

test.t5 2948697075

1251

test.t6 2948697075

1252

test.t7 NULL

1253

Warnings:

1254

Error 1146 Table 'test.t7' doesn't exist

1255

checksum table t1, t2, t3, t4, t5, t6, t7 extended;

1256

Table Checksum

1257

test.t1 2948697075

1258

test.t2 2948697075

1259

test.t3 2948697075

1260

test.t4 2948697075

1261

test.t5 2948697075

1262

test.t6 2948697075

1263

test.t7 NULL

1264

Warnings:

1265

Error 1146 Table 'test.t7' doesn't exist

1266

drop table t1,t2,t3, t4, t5, t6;

1267

create table t1 (id int, name char(10) not null, name2 char(10) not null) engine=MyISAM;

1268

insert into t1 values(1,'first','fff'),(2,'second','sss'),(3,'third','ttt');

1269

select trim(name2) from t1 union all select trim(name) from t1 union all select trim(id) from t1;

1270

trim(name2)

1271

fff

1272

sss

1273

ttt

1274

first

1275

second

1276

third

1277

1278

1279

1280

drop table t1;

1281

create table t1 (a int) engine=MyISAM;

1282

create table t2 like t1;

1283

show create table t2;

1284

Table Create Table

1285

t2 CREATE TABLE `t2` (

1286

`a` int(11) DEFAULT NULL

1287

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1288

drop table t1,t2;

1289

flush status;

1290

show status like "binlog_cache_use";

1291

Variable_name Value

1292

Binlog_cache_use 0

1293

show status like "binlog_cache_disk_use";

1294

Variable_name Value

1295

Binlog_cache_disk_use 0

1296

create table t1 (a int) engine=MyISAM;

1297

show status like "binlog_cache_use";

1298

Variable_name Value

1299

Binlog_cache_use 0

1300

show status like "binlog_cache_disk_use";

1301

Variable_name Value

1302

Binlog_cache_disk_use 0

1303

begin;

1304

delete from t1;

1305

commit;

1306

show status like "binlog_cache_use";

1307

Variable_name Value

1308

Binlog_cache_use 0

1309

show status like "binlog_cache_disk_use";

1310

Variable_name Value

1311

Binlog_cache_disk_use 0

1312

drop table t1;

1313

create table t1 (c char(10), index (c,c)) engine=MyISAM;

1314

ERROR 42S21: Duplicate column name 'c'

1315

create table t1 (c1 char(10), c2 char(10), index (c1,c2,c1)) engine=MyISAM;

1316

ERROR 42S21: Duplicate column name 'c1'

1317

create table t1 (c1 char(10), c2 char(10), index (c1,c1,c2)) engine=MyISAM;

1318

ERROR 42S21: Duplicate column name 'c1'

1319

create table t1 (c1 char(10), c2 char(10), index (c2,c1,c1)) engine=MyISAM;

1320

ERROR 42S21: Duplicate column name 'c1'

1321

create table t1 (c1 char(10), c2 char(10)) engine=MyISAM;

1322

alter table t1 add key (c1,c1);

1323

ERROR 42S21: Duplicate column name 'c1'

1324

alter table t1 add key (c2,c1,c1);

1325

ERROR 42S21: Duplicate column name 'c1'

1326

alter table t1 add key (c1,c2,c1);

1327

ERROR 42S21: Duplicate column name 'c1'

1328

alter table t1 add key (c1,c1,c2);

1329

ERROR 42S21: Duplicate column name 'c1'

1330

drop table t1;

1331

create table t1(a int(1) , b int(1)) engine=MyISAM;

1332

insert into t1 values ('1111', '3333');

1333

select distinct concat(a, b) from t1;

1334

concat(a, b)

1335

11113333

1336

drop table t1;

1337

create temporary table t1 (a int) engine=MyISAM;

1338

insert into t1 values (4711);

1339

truncate t1;

1340

insert into t1 values (42);

1341

select * from t1;

1342

1343

1344

drop table t1;

1345

create table t1 (a int) engine=MyISAM;

1346

insert into t1 values (4711);

1347

truncate t1;

1348

insert into t1 values (42);

1349

select * from t1;

1350

1351

1352

drop table t1;

1353

create table t1 (a int not null, b int not null, c blob not null, d int not null, e int, primary key (a,b,c(255),d)) engine=MyISAM;

1354

insert into t1 values (2,2,"b",2,2),(1,1,"a",1,1),(3,3,"ab",3,3);

1355

select * from t1 order by a,b,c,d;

1356

a b c d e

1357

1 1 a 1 1

1358

2 2 b 2 2

1359

3 3 ab 3 3

1360

explain select * from t1 order by a,b,c,d;

1361

id select_type table type possible_keys key key_len ref rows Extra

1362

1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using filesort

1363

drop table t1;

1364

create table t1 (a char(1), b char(1), key(a, b)) engine=MyISAM;

1365

insert into t1 values ('8', '6'), ('4', '7');

1366

select min(a) from t1;

1367

min(a)

1368

1369

select min(b) from t1 where a='8';

1370

min(b)

1371

1372

drop table t1;

1373

create table t1 (x bigint unsigned not null primary key) engine=MyISAM;

1374

insert into t1(x) values (0xfffffffffffffff0),(0xfffffffffffffff1);

1375

select * from t1;

1376

1377

18446744073709551600

1378

18446744073709551601

1379

select count(*) from t1 where x>0;

1380

count(*)

1381

1382

select count(*) from t1 where x=0;

1383

count(*)

1384

1385

select count(*) from t1 where x<0;

1386

count(*)

1387

1388

select count(*) from t1 where x < -16;

1389

count(*)

1390

1391

select count(*) from t1 where x = -16;

1392

count(*)

1393

1394

explain select count(*) from t1 where x > -16;

1395

id select_type table type possible_keys key key_len ref rows Extra

1396

1 SIMPLE t1 index PRIMARY PRIMARY 8 NULL 2 Using where; Using index

1397

select count(*) from t1 where x > -16;

1398

count(*)

1399

1400

select * from t1 where x > -16;

1401

1402

18446744073709551600

1403

18446744073709551601

1404

select count(*) from t1 where x = 18446744073709551601;

1405

count(*)

1406

1407

drop table t1;

1408

set storage_engine=MyISAM;

1409

drop table if exists t1,t2,t3;

1410

--- Testing varchar ---

1411

--- Testing varchar ---

1412

create table t1 (v varchar(10), c char(10), t text);

1413

insert into t1 values('+ ', '+ ', '+ ');

1414

set @a=repeat(' ',20);

1415

insert into t1 values (concat('+',@a),concat('+',@a),concat('+',@a));

1416

Warnings:

1417

Note 1265 Data truncated for column 'v' at row 1

1418

Note 1265 Data truncated for column 'c' at row 1

1419

select concat('*',v,'*',c,'*',t,'*') from t1;

1420

concat('*',v,'*',c,'*',t,'*')

1421

*+ *+*+ *

1422

*+ *+*+ *

1423

show create table t1;

1424

Table Create Table

1425

t1 CREATE TABLE `t1` (

1426

`v` varchar(10) DEFAULT NULL,

1427

`c` char(10) DEFAULT NULL,

1428

`t` text

1429

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1430

create table t2 like t1;

1431

show create table t2;

1432

Table Create Table

1433

t2 CREATE TABLE `t2` (

1434

`v` varchar(10) DEFAULT NULL,

1435

`c` char(10) DEFAULT NULL,

1436

`t` text

1437

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1438

create table t3 select * from t1;

1439

show create table t3;

1440

Table Create Table

1441

t3 CREATE TABLE `t3` (

1442

`v` varchar(10) DEFAULT NULL,

1443

`c` char(10) DEFAULT NULL,

1444

`t` text

1445

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1446

alter table t1 modify c varchar(10);

1447

show create table t1;

1448

Table Create Table

1449

t1 CREATE TABLE `t1` (

1450

`v` varchar(10) DEFAULT NULL,

1451

`c` varchar(10) DEFAULT NULL,

1452

`t` text

1453

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1454

alter table t1 modify v char(10);

1455

show create table t1;

1456

Table Create Table

1457

t1 CREATE TABLE `t1` (

1458

`v` char(10) DEFAULT NULL,

1459

`c` varchar(10) DEFAULT NULL,

1460

`t` text

1461

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1462

alter table t1 modify t varchar(10);

1463

Warnings:

1464

Note 1265 Data truncated for column 't' at row 2

1465

show create table t1;

1466

Table Create Table

1467

t1 CREATE TABLE `t1` (

1468

`v` char(10) DEFAULT NULL,

1469

`c` varchar(10) DEFAULT NULL,

1470

`t` varchar(10) DEFAULT NULL

1471

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1472

select concat('*',v,'*',c,'*',t,'*') from t1;

1473

concat('*',v,'*',c,'*',t,'*')

1474

*+*+*+ *

1475

*+*+*+ *

1476

drop table t1,t2,t3;

1477

create table t1 (v varchar(10), c char(10), t text, key(v), key(c), key(t(10)));

1478

show create table t1;

1479

Table Create Table

1480

t1 CREATE TABLE `t1` (

1481

`v` varchar(10) DEFAULT NULL,

1482

`c` char(10) DEFAULT NULL,

1483

`t` text,

1484

KEY `v` (`v`),

1485

KEY `c` (`c`),

1486

KEY `t` (`t`(10))

1487

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1488

select count(*) from t1;

1489

count(*)

1490

270

1491

insert into t1 values(concat('a',char(1)),concat('a',char(1)),concat('a',char(1)));

1492

select count(*) from t1 where v='a';

1493

count(*)

1494

1495

select count(*) from t1 where c='a';

1496

count(*)

1497

1498

select count(*) from t1 where t='a';

1499

count(*)

1500

1501

select count(*) from t1 where v='a ';

1502

count(*)

1503

1504

select count(*) from t1 where c='a ';

1505

count(*)

1506

1507

select count(*) from t1 where t='a ';

1508

count(*)

1509

1510

select count(*) from t1 where v between 'a' and 'a ';

1511

count(*)

1512

1513

select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';

1514

count(*)

1515

1516

select count(*) from t1 where v like 'a%';

1517

count(*)

1518

1519

select count(*) from t1 where c like 'a%';

1520

count(*)

1521

1522

select count(*) from t1 where t like 'a%';

1523

count(*)

1524

1525

select count(*) from t1 where v like 'a %';

1526

count(*)

1527

1528

explain select count(*) from t1 where v='a ';

1529

id select_type table type possible_keys key key_len ref rows Extra

1530

1 SIMPLE t1 ref v v 13 const # Using where; Using index

1531

explain select count(*) from t1 where c='a ';

1532

id select_type table type possible_keys key key_len ref rows Extra

1533

1 SIMPLE t1 ref c c 11 const # Using where; Using index

1534

explain select count(*) from t1 where t='a ';

1535

id select_type table type possible_keys key key_len ref rows Extra

1536

1 SIMPLE t1 ref t t 13 const # Using where

1537

explain select count(*) from t1 where v like 'a%';

1538

id select_type table type possible_keys key key_len ref rows Extra

1539

1 SIMPLE t1 range v v 13 NULL # Using where; Using index

1540

explain select count(*) from t1 where v between 'a' and 'a ';

1541

id select_type table type possible_keys key key_len ref rows Extra

1542

1 SIMPLE t1 ref v v 13 const # Using where; Using index

1543

explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';

1544

id select_type table type possible_keys key key_len ref rows Extra

1545

1 SIMPLE t1 ref v v 13 const # Using where; Using index

1546

alter table t1 add unique(v);

1547

ERROR 23000: Duplicate entry '{ ' for key 'v_2'

1548

alter table t1 add key(v);

1549

select concat('*',v,'*',c,'*',t,'*') as qq from t1 where v='a';

1550

1551

*a*a*a*

1552

*a *a*a *

1553

*a *a*a *

1554

*a *a*a *

1555

*a *a*a *

1556

*a *a*a *

1557

*a *a*a *

1558

*a *a*a *

1559

*a *a*a *

1560

*a *a*a *

1561

explain select * from t1 where v='a';

1562

id select_type table type possible_keys key key_len ref rows Extra

1563

1 SIMPLE t1 ref v,v_2 # 13 const # Using index condition

1564

select v,count(*) from t1 group by v limit 10;

1565

v count(*)

1566

a 1

1567

a 10

1568

b 10

1569

c 10

1570

d 10

1571

e 10

1572

f 10

1573

g 10

1574

h 10

1575

i 10

1576

select v,count(t) from t1 group by v limit 10;

1577

v count(t)

1578

a 1

1579

a 10

1580

b 10

1581

c 10

1582

d 10

1583

e 10

1584

f 10

1585

g 10

1586

h 10

1587

i 10

1588

select v,count(c) from t1 group by v limit 10;

1589

v count(c)

1590

a 1

1591

a 10

1592

b 10

1593

c 10

1594

d 10

1595

e 10

1596

f 10

1597

g 10

1598

h 10

1599

i 10

1600

select sql_big_result v,count(t) from t1 group by v limit 10;

1601

v count(t)

1602

a 1

1603

a 10

1604

b 10

1605

c 10

1606

d 10

1607

e 10

1608

f 10

1609

g 10

1610

h 10

1611

i 10

1612

select sql_big_result v,count(c) from t1 group by v limit 10;

1613

v count(c)

1614

a 1

1615

a 10

1616

b 10

1617

c 10

1618

d 10

1619

e 10

1620

f 10

1621

g 10

1622

h 10

1623

i 10

1624

select c,count(*) from t1 group by c limit 10;

1625

c count(*)

1626

a 1

1627

a 10

1628

b 10

1629

c 10

1630

d 10

1631

e 10

1632

f 10

1633

g 10

1634

h 10

1635

i 10

1636

select c,count(t) from t1 group by c limit 10;

1637

c count(t)

1638

a 1

1639

a 10

1640

b 10

1641

c 10

1642

d 10

1643

e 10

1644

f 10

1645

g 10

1646

h 10

1647

i 10

1648

select sql_big_result c,count(t) from t1 group by c limit 10;

1649

c count(t)

1650

a 1

1651

a 10

1652

b 10

1653

c 10

1654

d 10

1655

e 10

1656

f 10

1657

g 10

1658

h 10

1659

i 10

1660

select t,count(*) from t1 group by t limit 10;

1661

t count(*)

1662

a 1

1663

a 10

1664

b 10

1665

c 10

1666

d 10

1667

e 10

1668

f 10

1669

g 10

1670

h 10

1671

i 10

1672

select t,count(t) from t1 group by t limit 10;

1673

t count(t)

1674

a 1

1675

a 10

1676

b 10

1677

c 10

1678

d 10

1679

e 10

1680

f 10

1681

g 10

1682

h 10

1683

i 10

1684

select sql_big_result t,count(t) from t1 group by t limit 10;

1685

t count(t)

1686

a 1

1687

a 10

1688

b 10

1689

c 10

1690

d 10

1691

e 10

1692

f 10

1693

g 10

1694

h 10

1695

i 10

1696

alter table t1 modify v varchar(300), drop key v, drop key v_2, add key v (v);

1697

show create table t1;

1698

Table Create Table

1699

t1 CREATE TABLE `t1` (

1700

`v` varchar(300) DEFAULT NULL,

1701

`c` char(10) DEFAULT NULL,

1702

`t` text,

1703

KEY `c` (`c`),

1704

KEY `t` (`t`(10)),

1705

KEY `v` (`v`)

1706

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1707

select count(*) from t1 where v='a';

1708

count(*)

1709

1710

select count(*) from t1 where v='a ';

1711

count(*)

1712

1713

select count(*) from t1 where v between 'a' and 'a ';

1714

count(*)

1715

1716

select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';

1717

count(*)

1718

1719

select count(*) from t1 where v like 'a%';

1720

count(*)

1721

1722

select count(*) from t1 where v like 'a %';

1723

count(*)

1724

1725

explain select count(*) from t1 where v='a ';

1726

id select_type table type possible_keys key key_len ref rows Extra

1727

1 SIMPLE t1 ref v v 303 const # Using where; Using index

1728

explain select count(*) from t1 where v like 'a%';

1729

id select_type table type possible_keys key key_len ref rows Extra

1730

1 SIMPLE t1 range v v 303 NULL # Using where; Using index

1731

explain select count(*) from t1 where v between 'a' and 'a ';

1732

id select_type table type possible_keys key key_len ref rows Extra

1733

1 SIMPLE t1 ref v v 303 const # Using where; Using index

1734

explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';

1735

id select_type table type possible_keys key key_len ref rows Extra

1736

1 SIMPLE t1 ref v v 303 const # Using where; Using index

1737

explain select * from t1 where v='a';

1738

id select_type table type possible_keys key key_len ref rows Extra

1739

1 SIMPLE t1 ref v v 303 const # Using index condition

1740

select v,count(*) from t1 group by v limit 10;

1741

v count(*)

1742

a 1

1743

a 10

1744

b 10

1745

c 10

1746

d 10

1747

e 10

1748

f 10

1749

g 10

1750

h 10

1751

i 10

1752

select v,count(t) from t1 group by v limit 10;

1753

v count(t)

1754

a 1

1755

a 10

1756

b 10

1757

c 10

1758

d 10

1759

e 10

1760

f 10

1761

g 10

1762

h 10

1763

i 10

1764

select sql_big_result v,count(t) from t1 group by v limit 10;

1765

v count(t)

1766

a 1

1767

a 10

1768

b 10

1769

c 10

1770

d 10

1771

e 10

1772

f 10

1773

g 10

1774

h 10

1775

i 10

1776

alter table t1 drop key v, add key v (v(30));

1777

show create table t1;

1778

Table Create Table

1779

t1 CREATE TABLE `t1` (

1780

`v` varchar(300) DEFAULT NULL,

1781

`c` char(10) DEFAULT NULL,

1782

`t` text,

1783

KEY `c` (`c`),

1784

KEY `t` (`t`(10)),

1785

KEY `v` (`v`(30))

1786

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1787

select count(*) from t1 where v='a';

1788

count(*)

1789

1790

select count(*) from t1 where v='a ';

1791

count(*)

1792

1793

select count(*) from t1 where v between 'a' and 'a ';

1794

count(*)

1795

1796

select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';

1797

count(*)

1798

1799

select count(*) from t1 where v like 'a%';

1800

count(*)

1801

1802

select count(*) from t1 where v like 'a %';

1803

count(*)

1804

1805

explain select count(*) from t1 where v='a ';

1806

id select_type table type possible_keys key key_len ref rows Extra

1807

1 SIMPLE t1 ref v v 33 const # Using where

1808

explain select count(*) from t1 where v like 'a%';

1809

id select_type table type possible_keys key key_len ref rows Extra

1810

1 SIMPLE t1 range v v 33 NULL # Using where

1811

explain select count(*) from t1 where v between 'a' and 'a ';

1812

id select_type table type possible_keys key key_len ref rows Extra

1813

1 SIMPLE t1 ref v v 33 const # Using where

1814

explain select count(*) from t1 where v between 'a' and 'a ' and v between 'a ' and 'b\n';

1815

id select_type table type possible_keys key key_len ref rows Extra

1816

1 SIMPLE t1 ref v v 33 const # Using where

1817

explain select * from t1 where v='a';

1818

id select_type table type possible_keys key key_len ref rows Extra

1819

1 SIMPLE t1 ref v v 33 const # Using where

1820

select v,count(*) from t1 group by v limit 10;

1821

v count(*)

1822

a 1

1823

a 10

1824

b 10

1825

c 10

1826

d 10

1827

e 10

1828

f 10

1829

g 10

1830

h 10

1831

i 10

1832

select v,count(t) from t1 group by v limit 10;

1833

v count(t)

1834

a 1

1835

a 10

1836

b 10

1837

c 10

1838

d 10

1839

e 10

1840

f 10

1841

g 10

1842

h 10

1843

i 10

1844

select sql_big_result v,count(t) from t1 group by v limit 10;

1845

v count(t)

1846

a 1

1847

a 10

1848

b 10

1849

c 10

1850

d 10

1851

e 10

1852

f 10

1853

g 10

1854

h 10

1855

i 10

1856

alter table t1 modify v varchar(600), drop key v, add key v (v);

1857

show create table t1;

1858

Table Create Table

1859

t1 CREATE TABLE `t1` (

1860

`v` varchar(600) DEFAULT NULL,

1861

`c` char(10) DEFAULT NULL,

1862

`t` text,

1863

KEY `c` (`c`),

1864

KEY `t` (`t`(10)),

1865

KEY `v` (`v`)

1866

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1867

select v,count(*) from t1 group by v limit 10;

1868

v count(*)

1869

a 1

1870

a 10

1871

b 10

1872

c 10

1873

d 10

1874

e 10

1875

f 10

1876

g 10

1877

h 10

1878

i 10

1879

select v,count(t) from t1 group by v limit 10;

1880

v count(t)

1881

a 1

1882

a 10

1883

b 10

1884

c 10

1885

d 10

1886

e 10

1887

f 10

1888

g 10

1889

h 10

1890

i 10

1891

select sql_big_result v,count(t) from t1 group by v limit 10;

1892

v count(t)

1893

a 1

1894

a 10

1895

b 10

1896

c 10

1897

d 10

1898

e 10

1899

f 10

1900

g 10

1901

h 10

1902

i 10

1903

drop table t1;

1904

create table t1 (a char(10), unique (a));

1905

insert into t1 values ('a ');

1906

insert into t1 values ('a ');

1907

ERROR 23000: Duplicate entry 'a' for key 'a'

1908

alter table t1 modify a varchar(10);

1909

insert into t1 values ('a '),('a '),('a '),('a ');

1910

ERROR 23000: Duplicate entry 'a ' for key 'a'

1911

insert into t1 values ('a ');

1912

ERROR 23000: Duplicate entry 'a ' for key 'a'

1913

insert into t1 values ('a ');

1914

ERROR 23000: Duplicate entry 'a ' for key 'a'

1915

insert into t1 values ('a ');

1916

ERROR 23000: Duplicate entry 'a ' for key 'a'

1917

update t1 set a='a ' where a like 'a%';

1918

select concat(a,'.') from t1;

1919

concat(a,'.')

1920

a .

1921

update t1 set a='abc ' where a like 'a ';

1922

select concat(a,'.') from t1;

1923

concat(a,'.')

1924

a .

1925

update t1 set a='a ' where a like 'a %';

1926

select concat(a,'.') from t1;

1927

concat(a,'.')

1928

a .

1929

update t1 set a='a ' where a like 'a ';

1930

select concat(a,'.') from t1;

1931

concat(a,'.')

1932

a .

1933

drop table t1;

1934

create table t1 (v varchar(10), c char(10), t text, key(v(5)), key(c(5)), key(t(5)));

1935

show create table t1;

1936

Table Create Table

1937

t1 CREATE TABLE `t1` (

1938

`v` varchar(10) DEFAULT NULL,

1939

`c` char(10) DEFAULT NULL,

1940

`t` text,

1941

KEY `v` (`v`(5)),

1942

KEY `c` (`c`(5)),

1943

KEY `t` (`t`(5))

1944

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1945

drop table t1;

1946

create table t1 (v char(10) character set utf8);

1947

show create table t1;

1948

Table Create Table

1949

t1 CREATE TABLE `t1` (

1950

`v` char(10) CHARACTER SET utf8 DEFAULT NULL

1951

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1952

drop table t1;

1953

create table t1 (v varchar(10), c char(10)) row_format=fixed;

1954

show create table t1;

1955

Table Create Table

1956

t1 CREATE TABLE `t1` (

1957

`v` varchar(10) DEFAULT NULL,

1958

`c` char(10) DEFAULT NULL

1959

) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED

1960

insert into t1 values('a','a'),('a ','a ');

1961

select concat('*',v,'*',c,'*') from t1;

1962

concat('*',v,'*',c,'*')

1963

*a*a*

1964

*a *a*

1965

drop table t1;

1966

create table t1 (v varchar(65530), key(v(10)));

1967

insert into t1 values(repeat('a',65530));

1968

select length(v) from t1 where v=repeat('a',65530);

1969

length(v)

1970

65530

1971

drop table t1;

1972

create table t1(a int, b varchar(12), key ba(b, a));

1973

insert into t1 values (1, 'A'), (20, NULL);

1974

explain select * from t1 where a=20 and b is null;

1975

id select_type table type possible_keys key key_len ref rows Extra

1976

1 SIMPLE t1 ref ba ba 20 const,const 1 Using where; Using index

1977

select * from t1 where a=20 and b is null;

1978

a b

1979

20 NULL

1980

drop table t1;

1981

create table t1 (v varchar(65530), key(v));

1982

Warnings:

1983

Warning 1071 Specified key was too long; max key length is 1332 bytes

1984

drop table t1;

1985

create table t1 (v varchar(65536));

1986

Warnings:

1987

Note 1246 Converting column 'v' from VARCHAR to TEXT

1988

show create table t1;

1989

Table Create Table

1990

t1 CREATE TABLE `t1` (

1991

`v` mediumtext

1992

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1993

drop table t1;

1994

create table t1 (v varchar(65530) character set utf8);

1995

Warnings:

1996

Note 1246 Converting column 'v' from VARCHAR to TEXT

1997

show create table t1;

1998

Table Create Table

1999

t1 CREATE TABLE `t1` (

2000

`v` mediumtext CHARACTER SET utf8

2001

) ENGINE=MyISAM DEFAULT CHARSET=latin1

2002

drop table t1;

2003

set storage_engine=MEMORY;

2004

create table t1 (v varchar(16384)) engine=MyISAM;

2005

drop table t1;

2006

create table t1 (a char(1), b char(1), key(a, b)) engine=MyISAM;

2007

insert into t1 values ('8', '6'), ('4', '7');

2008

select min(a) from t1;

2009

min(a)

2010

2011

select min(b) from t1 where a='8';

2012

min(b)

2013

2014

drop table t1;

2015

CREATE TABLE t1 ( `a` int(11) NOT NULL auto_increment, `b` int(11) default NULL,PRIMARY KEY (`a`),UNIQUE KEY `b` (`b`)) ENGINE=MyISAM;

2016

insert into t1 (b) values (1);

2017

replace into t1 (b) values (2), (1), (3);

2018

select * from t1;

2019

a b

2020

3 1

2021

2 2

2022

4 3

2023

truncate table t1;

2024

insert into t1 (b) values (1);

2025

replace into t1 (b) values (2);

2026

replace into t1 (b) values (1);

2027

replace into t1 (b) values (3);

2028

select * from t1;

2029

a b

2030

3 1

2031

2 2

2032

4 3

2033

drop table t1;

2034

create table t1 (rowid int not null auto_increment, val int not null,primary

2035

key (rowid), unique(val)) engine=MyISAM;

2036

replace into t1 (val) values ('1'),('2');

2037

replace into t1 (val) values ('1'),('2');

2038

insert into t1 (val) values ('1'),('2');

2039

ERROR 23000: Duplicate entry '1' for key 'val'

2040

select * from t1;

2041

rowid val

2042

3 1

2043

4 2

2044

drop table t1;

2045

CREATE TABLE t1 (GRADE DECIMAL(4) NOT NULL, PRIMARY KEY (GRADE)) ENGINE=MyISAM;

2046

INSERT INTO t1 (GRADE) VALUES (151),(252),(343);

2047

SELECT GRADE FROM t1 WHERE GRADE > 160 AND GRADE < 300;

2048

GRADE

2049

252

2050

SELECT GRADE FROM t1 WHERE GRADE= 151;

2051

GRADE

2052

151

2053

DROP TABLE t1;

2054

create table t1 (f1 varchar(10), f2 varchar(10), primary key (f1,f2)) engine=MyISAM;

2055

create table t2 (f3 varchar(10), f4 varchar(10), key (f4)) engine=MyISAM;

2056

insert into t2 values ('aa','cc');

2057

insert into t1 values ('aa','bb'),('aa','cc');

2058

delete t1 from t1,t2 where f1=f3 and f4='cc';

2059

select * from t1;

2060

f1 f2

2061

drop table t1,t2;

2062

create table t1(a int not null, b int, c int, d int, primary key(a)) engine=MyISAM;

2063

insert into t1(a) values (1),(2),(3);

2064

commit;

2065

set autocommit = 0;

2066

update t1 set b = 5 where a = 2;

2067

create trigger t1t before insert on t1 for each row begin set NEW.b = NEW.a * 10 + 5, NEW.c = NEW.a / 10; end |

2068

set autocommit = 0;

2069

insert into t1(a) values (10),(20),(30),(40),(50),(60),(70),(80),(90),(100),

2070

(11),(21),(31),(41),(51),(61),(71),(81),(91),(101),

2071

(12),(22),(32),(42),(52),(62),(72),(82),(92),(102),

2072

(13),(23),(33),(43),(53),(63),(73),(83),(93),(103),

2073

(14),(24),(34),(44),(54),(64),(74),(84),(94),(104);

2074

commit;

2075

commit;

2076

drop trigger t1t;

2077

drop table t1;

2078

create table t1(a int not null, b int, c int, d int, primary key(a)) engine=MyISAM;

2079

create table t2(a int not null, b int, c int, d int, primary key(a)) engine=MyISAM;

2080

create table t3(a int not null, b int, c int, d int, primary key(a)) engine=MyISAM;

2081

create table t4(a int not null, b int, c int, d int, primary key(a)) engine=MyISAM;

2082

create table t5(a int not null, b int, c int, d int, primary key(a)) engine=MyISAM;

2083

insert into t1(a) values (1),(2),(3);

2084

insert into t2(a) values (1),(2),(3);

2085

insert into t3(a) values (1),(2),(3);

2086

insert into t4(a) values (1),(2),(3);

2087

insert into t3(a) values (5),(7),(8);

2088

insert into t4(a) values (5),(7),(8);

2089

insert into t5(a) values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12);

2090

create trigger t1t before insert on t1 for each row begin

2091

INSERT INTO t2 SET a = NEW.a;

2092

end |

2093

create trigger t2t before insert on t2 for each row begin

2094

DELETE FROM t3 WHERE a = NEW.a;

2095

end |

2096

create trigger t3t before delete on t3 for each row begin

2097

UPDATE t4 SET b = b + 1 WHERE a = OLD.a;

2098

end |

2099

create trigger t4t before update on t4 for each row begin

2100

UPDATE t5 SET b = b + 1 where a = NEW.a;

2101

end |

2102

commit;

2103

set autocommit = 0;

2104

update t1 set b = b + 5 where a = 1;

2105

update t2 set b = b + 5 where a = 1;

2106

update t3 set b = b + 5 where a = 1;

2107

update t4 set b = b + 5 where a = 1;

2108

insert into t5(a) values(20);

2109

set autocommit = 0;

2110

insert into t1(a) values(7);

2111

insert into t2(a) values(8);

2112

delete from t2 where a = 3;

2113

update t4 set b = b + 1 where a = 3;

2114

commit;

2115

drop trigger t1t;

2116

drop trigger t2t;

2117

drop trigger t3t;

2118

drop trigger t4t;

2119

drop table t1, t2, t3, t4, t5;

2120

create table t1(a date) engine=MyISAM;

2121

create table t2(a date, key(a)) engine=MyISAM;

2122

insert into t1 values('2005-10-01');

2123

insert into t2 values('2005-10-01');

2124

select * from t1, t2

2125

where t2.a between t1.a - interval 2 day and t1.a + interval 2 day;

2126

a a

2127

2005-10-01 2005-10-01

2128

drop table t1, t2;

2129

create table t1 (id int not null, f_id int not null, f int not null,

2130

primary key(f_id, id)) engine=MyISAM;

2131

create table t2 (id int not null,s_id int not null,s varchar(200),

2132

primary key(id)) engine=MyISAM;

2133

INSERT INTO t1 VALUES (8, 1, 3);

2134

INSERT INTO t1 VALUES (1, 2, 1);

2135

INSERT INTO t2 VALUES (1, 0, '');

2136

INSERT INTO t2 VALUES (8, 1, '');

2137

commit;

2138

DELETE ml.* FROM t1 AS ml LEFT JOIN t2 AS mm ON (mm.id=ml.id)

2139

WHERE mm.id IS NULL;

2140

select ml.* from t1 as ml left join t2 as mm on (mm.id=ml.id)

2141

where mm.id is null lock in share mode;

2142

id f_id f

2143

drop table t1,t2;

2144

create table t1(a int not null, b int, primary key(a)) engine=MyISAM;

2145

insert into t1 values(1,1),(2,2),(3,1),(4,2),(5,1),(6,2),(7,3);

2146

commit;

2147

set autocommit = 0;

2148

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2149

update t1 set b = 5 where b = 1;

2150

set autocommit = 0;

2151

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

2152

select * from t1 where a = 7 and b = 3 for update;

2153

a b

2154

7 3

2155

commit;

2156

commit;

2157

drop table t1;

2158

CREATE TABLE t1 ( a int ) ENGINE=MyISAM;

2159

BEGIN;

2160

INSERT INTO t1 VALUES (1);

2161

OPTIMIZE TABLE t1;

2162

Table Op Msg_type Msg_text

2163

test.t1 optimize status OK

2164

DROP TABLE t1;

Older »