~drizzle-trunk/drizzle/development : revision 717

1

SET SESSION STORAGE_ENGINE = MEMORY;

2

drop table if exists t1,t2,t3,t4;

3

drop database if exists mysqltest;

4

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;

4

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

5

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

6

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

7

id code name

34

12 1 Ralph

35

drop table t1;

36

CREATE TABLE t1 (

37

id int(11) NOT NULL auto_increment,

38

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

39

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

37

id int NOT NULL auto_increment,

38

parent_id int DEFAULT '0' NOT NULL,

39

level int DEFAULT '0' NOT NULL,

40

PRIMARY KEY (id),

41

KEY parent_id (parent_id),

42

KEY level (level)

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

145

1 SIMPLE t1 ref level level 4 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 #

148

1 SIMPLE t1 ref level level 4 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 #

151

1 SIMPLE t1 ref level level 4 const #

152

select level,id from t1 where level=1;

153

level id

154

1 1003

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,

178

gesuchnr int DEFAULT '0' NOT NULL,

179

benutzer_id int DEFAULT '0' NOT NULL,

180

PRIMARY KEY (gesuchnr,benutzer_id)

181

) engine=MyISAM;

182

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

222

1

223

drop table t1;

224

CREATE TABLE t1 (

225

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

225

user_id int DEFAULT '0' NOT NULL,

226

name varchar(100),

227

phone varchar(100),

228

ref_email varchar(100) DEFAULT '' NOT NULL,

330

3 3

331

drop table t1,t2;

332

CREATE TABLE t1 (

333

id int(11) NOT NULL auto_increment,

333

id int 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,

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),

373

user_id int DEFAULT '0' NOT NULL,

374

quota bigint,

375

weight double,

376

access_date date,

377

access_time time,

378

approved datetime,

379

dummy_primary_key int(11) NOT NULL auto_increment,

379

dummy_primary_key int 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);

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,

396

id int NOT NULL auto_increment,

397

parent_id int DEFAULT '0' NOT NULL,

398

level int DEFAULT '0' NOT NULL,

399

KEY (id),

400

KEY parent_id (parent_id),

401

KEY level (level)

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

504

1 SIMPLE t1 ref level level 4 const # Using index

505

select level,id from t1 where level=1;

506

level id

507

1 1004

649

1 2

650

drop table t1;

651

CREATE TABLE t1 (

652

a int3 unsigned NOT NULL,

653

b int1 unsigned NOT NULL,

652

a int NOT NULL,

653

b int NOT NULL,

654

UNIQUE (a, b)

655

) ENGINE = MyISAM;

656

INSERT INTO t1 VALUES (1, 1);

658

MIN(B) MAX(b)

659

1 1

660

drop table t1;

661

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

661

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

662

INSERT INTO t1 VALUES (1);

663

SELECT * FROM t1;

664

a

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

721

`a` varchar(20),

722

KEY `a` (`a`())

723

) ENGINE=MyISAM

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);

758

select * from t1;

759

id name value uid

760

1 one one value 101

761

6 two other value 102

761

762

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;

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

835

t int NO MUL NULL

836

drop table t1;

837

CREATE TABLE t1 (

838

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

838

number bigint 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',

840

carrier_id int NOT NULL default '0',

841

privacy int NOT NULL default '0',

842

last_mod_date timestamp NOT NULL,

843

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

843

last_mod_id int 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'

845

last_app_id int default '-1',

846

version int NOT NULL default '0',

847

assigned_scps int default '0',

848

status int 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);

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',

857

number bigint 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',

859

carrier_id int NOT NULL default '0',

860

privacy int NOT NULL default '0',

861

last_mod_date timestamp NOT NULL,

862

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

862

last_mod_id int 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'

864

last_app_id int default '-1',

865

version int NOT NULL default '0',

866

assigned_scps int default '0',

867

status int 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);

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;

899

create table t1 (id int not null auto_increment, code int 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;

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;

937

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

938

create table t2 (n int, d int) 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;

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

1117

1 SIMPLE t1 range c c 5 NULL # Using where; 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

1121

1 SIMPLE t1 range c c 5 NULL # Using where; 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);

1171

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

1172

SELECT * from t1;

1173

a

1174

1

1175

2

1174

3

1175

4

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

1177

CREATE TABLE t1 (col1 int)ENGINE=MyISAM;

1178

CREATE TABLE t2 (col1 int,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

1182

ERROR 22007: Incorrect datetime value: '20020204310000' for column 'stamp' at row 3

1184

1183

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

1185

1184

'20020204120000' GROUP BY col1;

1186

1185

col1

1187

1186

1

1188

1187

2

1189

1188

3

1190

4

1191

1189

drop table t1,t2;

1192

1190

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',

1191

`id` int NOT NULL auto_increment,

1192

`id_object` int default '0',

1193

`id_version` int NOT NULL default '1',

1196

1194

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

1197

1195

`description` text,

1198

1196

PRIMARY KEY (`id`),

1201

1199

) ENGINE=MyISAM;

1202

1200

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

1201

CREATE TABLE t2 (

1204

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

1205

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

1202

`id` int NOT NULL auto_increment,

1203

`id_version` int NOT NULL default '1',

1206

1204

PRIMARY KEY (`id`),

1207

1205

KEY `id_version` (`id_version`)

1208

1206

) ENGINE=MyISAM;

1283

1281

show create table t2;

1284

1282

Table Create Table

1285

1283

t2 CREATE TABLE `t2` (

1286

`a` int(11) DEFAULT NULL

1287

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1284

`a` int

1285

) ENGINE=MyISAM

1288

1286

drop table t1,t2;

1289

1287

flush status;

1290

1288

show status like "binlog_cache_use";

1328

1326

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

1329

1327

ERROR 42S21: Duplicate column name 'c1'

1330

1328

drop table t1;

1331

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

1329

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

1332

1330

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

1333

1331

select distinct concat(a, b) from t1;

1334

1332

concat(a, b)

1370

1368

min(b)

1371

1369

6

1372

1370

drop table t1;

1373

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

1371

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

1374

1372

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

1373

ERROR 22003: Out of range value for column 'x' at row 1

1375

1374

select * from t1;

1376

1375

x

1377

18446744073709551600

1378

18446744073709551601

1379

1376

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

1380

1377

count(*)

1381

2

1378

0

1382

1379

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

1383

1380

count(*)

1384

1381

0

1393

1390

0

1394

1391

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

1395

1392

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

1393

1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables

1397

1394

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

1398

1395

count(*)

1399

2

1396

0

1400

1397

select * from t1 where x > -16;

1401

1398

x

1402

18446744073709551600

1403

18446744073709551601

1404

1399

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

1405

1400

count(*)

1406

1

1401

0

1407

1402

drop table t1;

1408

1403

set storage_engine=MyISAM;

1409

1404

drop table if exists t1,t2,t3;

1418

1413

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

1419

1414

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

1420

1415

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

1421

*+ *+*+ *

1422

*+ *+*+ *

1416

*+ *+ *+ *

1417

*+ *+ *+ *

1423

1418

show create table t1;

1424

1419

Table Create Table

1425

1420

t1 CREATE TABLE `t1` (

1426

`v` varchar(10) DEFAULT NULL,

1427

`c` char(10) DEFAULT NULL,

1421

`v` varchar(10),

1422

`c` varchar(10),

1428

1423

`t` text

1429

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1424

) ENGINE=MyISAM

1430

1425

create table t2 like t1;

1431

1426

show create table t2;

1432

1427

Table Create Table

1433

1428

t2 CREATE TABLE `t2` (

1434

`v` varchar(10) DEFAULT NULL,

1435

`c` char(10) DEFAULT NULL,

1429

`v` varchar(10),

1430

`c` varchar(10),

1436

1431

`t` text

1437

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1432

) ENGINE=MyISAM

1438

1433

create table t3 select * from t1;

1439

1434

show create table t3;

1440

1435

Table Create Table

1441

1436

t3 CREATE TABLE `t3` (

1442

`v` varchar(10) DEFAULT NULL,

1443

`c` char(10) DEFAULT NULL,

1437

`v` varchar(10),

1438

`c` varchar(10),

1444

1439

`t` text

1445

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1440

) ENGINE=MyISAM

1446

1441

alter table t1 modify c varchar(10);

1447

1442

show create table t1;

1448

1443

Table Create Table

1449

1444

t1 CREATE TABLE `t1` (

1450

`v` varchar(10) DEFAULT NULL,

1451

`c` varchar(10) DEFAULT NULL,

1445

`v` varchar(10),

1446

`c` varchar(10),

1452

1447

`t` text

1453

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1448

) ENGINE=MyISAM

1454

1449

alter table t1 modify v char(10);

1455

1450

show create table t1;

1456

1451

Table Create Table

1457

1452

t1 CREATE TABLE `t1` (

1458

`v` char(10) DEFAULT NULL,

1459

`c` varchar(10) DEFAULT NULL,

1453

`v` varchar(10),

1454

`c` varchar(10),

1460

1455

`t` text

1461

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1456

) ENGINE=MyISAM

1462

1457

alter table t1 modify t varchar(10);

1463

1458

Warnings:

1464

1459

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

1465

1460

show create table t1;

1466

1461

Table Create Table

1467

1462

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

1463

`v` varchar(10),

1464

`c` varchar(10),

1465

`t` varchar(10)

1466

) ENGINE=MyISAM

1472

1467

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

1473

1468

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

1474

*+*+*+ *

1475

*+*+*+ *

1469

*+ *+ *+ *

1470

*+ *+ *+ *

1476

1471

drop table t1,t2,t3;

1477

1472

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

1478

1473

show create table t1;

1479

1474

Table Create Table

1480

1475

t1 CREATE TABLE `t1` (

1481

`v` varchar(10) DEFAULT NULL,

1482

`c` char(10) DEFAULT NULL,

1476

`v` varchar(10),

1477

`c` varchar(10),

1483

1478

`t` text,

1484

1479

KEY `v` (`v`),

1485

1480

KEY `c` (`c`),

1486

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

1487

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1481

KEY `t` (`t`())

1482

) ENGINE=MyISAM

1488

1483

select count(*) from t1;

1489

1484

count(*)

1490

1485

270

1527

1522

9

1528

1523

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

1529

1524

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

1525

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

1531

1526

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

1532

1527

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

1528

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

1534

1529

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

1535

1530

id select_type table type possible_keys key key_len ref rows Extra

1536

1 SIMPLE t1 ref t t 13 const # Using where

1531

1 SIMPLE t1 ref t t 43 const # Using where

1537

1532

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

1538

1533

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

1534

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

1540

1535

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

1541

1536

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

1537

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

1543

1538

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

1544

1539

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

1540

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

1546

1541

alter table t1 add unique(v);

1547

1542

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

1548

1543

alter table t1 add key(v);

1549

1544

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

1550

1545

qq

1551

1546

*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 *

1547

*a *a *a *

1548

*a *a *a *

1549

*a *a *a *

1550

*a *a *a *

1551

*a *a *a *

1552

*a *a *a *

1553

*a *a *a *

1554

*a *a *a *

1555

*a *a *a *

1561

1556

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

1562

1557

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

1558

1 SIMPLE t1 ref v,v_2 # 43 const # Using where

1564

1559

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

1565

1560

v count(*)

1566

1561

a 1

1697

1692

show create table t1;

1698

1693

Table Create Table

1699

1694

t1 CREATE TABLE `t1` (

1700

`v` varchar(300) DEFAULT NULL,

1701

`c` char(10) DEFAULT NULL,

1695

`v` varchar(300),

1696

`c` varchar(10),

1702

1697

`t` text,

1703

1698

KEY `c` (`c`),

1704

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

1699

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

1705

1700

KEY `v` (`v`)

1706

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1701

) ENGINE=MyISAM

1707

1702

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

1708

1703

count(*)

1709

1704

10

1724

1719

9

1725

1720

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

1726

1721

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

1722

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

1728

1723

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

1729

1724

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

1725

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

1731

1726

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

1732

1727

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

1728

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

1734

1729

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

1735

1730

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

1731

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

1737

1732

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

1738

1733

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

1734

1 SIMPLE t1 ref v v 1203 const # Using where

1740

1735

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

1741

1736

v count(*)

1742

1737

a 1

1777

1772

show create table t1;

1778

1773

Table Create Table

1779

1774

t1 CREATE TABLE `t1` (

1780

`v` varchar(300) DEFAULT NULL,

1781

`c` char(10) DEFAULT NULL,

1775

`v` varchar(300),

1776

`c` varchar(10),

1782

1777

`t` text,

1783

1778

KEY `c` (`c`),

1784

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

1785

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

1786

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1779

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

1780

KEY `v` (`v`())

1781

) ENGINE=MyISAM

1787

1782

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

1788

1783

count(*)

1789

1784

10

1804

1799

9

1805

1800

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

1806

1801

id select_type table type possible_keys key key_len ref rows Extra

1807

1 SIMPLE t1 ref v v 33 const # Using where

1802

1 SIMPLE t1 ref v v 123 const # Using where

1808

1803

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

1809

1804

id select_type table type possible_keys key key_len ref rows Extra

1810

1 SIMPLE t1 range v v 33 NULL # Using where

1805

1 SIMPLE t1 range v v 123 NULL # Using where

1811

1806

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

1812

1807

id select_type table type possible_keys key key_len ref rows Extra

1813

1 SIMPLE t1 ref v v 33 const # Using where

1808

1 SIMPLE t1 ref v v 123 const # Using where

1814

1809

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

1815

1810

id select_type table type possible_keys key key_len ref rows Extra

1816

1 SIMPLE t1 ref v v 33 const # Using where

1811

1 SIMPLE t1 ref v v 123 const # Using where

1817

1812

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

1818

1813

id select_type table type possible_keys key key_len ref rows Extra

1819

1 SIMPLE t1 ref v v 33 const # Using where

1814

1 SIMPLE t1 ref v v 123 const # Using where

1820

1815

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

1821

1816

v count(*)

1822

1817

a 1

1854

1849

h 10

1855

1850

i 10

1856

1851

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

1852

Warnings:

1853

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

1854

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

1855

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

1857

1856

show create table t1;

1858

1857

Table Create Table

1859

1858

t1 CREATE TABLE `t1` (

1860

`v` varchar(600) DEFAULT NULL,

1861

`c` char(10) DEFAULT NULL,

1859

`v` varchar(600),

1860

`c` varchar(10),

1862

1861

`t` text,

1863

1862

KEY `c` (`c`),

1864

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

1865

KEY `v` (`v`)

1866

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1863

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

1864

KEY `v` (`v`())

1865

) ENGINE=MyISAM

1867

1866

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

1868

1867

v count(*)

1869

1868

a 1

1904

1903

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

1905

1904

insert into t1 values ('a ');

1906

1905

insert into t1 values ('a ');

1907

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

1906

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

1908

1907

alter table t1 modify a varchar(10);

1909

1908

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

1910

1909

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

1935

1934

show create table t1;

1936

1935

Table Create Table

1937

1936

t1 CREATE TABLE `t1` (

1938

`v` varchar(10) DEFAULT NULL,

1939

`c` char(10) DEFAULT NULL,

1937

`v` varchar(10),

1938

`c` varchar(10),

1940

1939

`t` text,

1941

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

1942

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

1943

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

1944

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1940

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

1941

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

1942

KEY `t` (`t`())

1943

) ENGINE=MyISAM

1945

1944

drop table t1;

1946

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

1945

create table t1 (v char(10));

1947

1946

show create table t1;

1948

1947

Table Create Table

1949

1948

t1 CREATE TABLE `t1` (

1950

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

1951

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1949

`v` varchar(10)

1950

) ENGINE=MyISAM

1952

1951

drop table t1;

1953

1952

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

1954

1953

show create table t1;

1955

1954

Table Create Table

1956

1955

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

1956

`v` varchar(10),

1957

`c` varchar(10)

1958

) ENGINE=MyISAM ROW_FORMAT=FIXED

1960

1959

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

1961

1960

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

1962

1961

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

1963

1962

*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

1963

*a *a *

1971

1964

drop table t1;

1972

1965

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

1973

1966

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

1974

1967

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

1975

1968

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

1969

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

1977

1970

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

1978

1971

a b

1979

1972

20 NULL

1980

1973

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

1974

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

2007

1975

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

2008

1976

select min(a) from t1;

2012

1980

min(b)

2013

1981

6

2014

1982

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;

1983

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

2016

1984

insert into t1 (b) values (1);

2017

1985

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

2018

1986

select * from t1;

2027

1995

replace into t1 (b) values (3);

2028

1996

select * from t1;

2029

1997

a b

2030

3 1

2031

2 2

2032

4 3

1998

7 1

1999

6 2

2000

8 3

2033

2001

drop table t1;

2034

2002

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

2035

2003

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

2059

2027

select * from t1;

2060

2028

f1 f2

2061

2029

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

2030

create table t1(a date) engine=MyISAM;

2121

2031

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

2122

2032

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