~drizzle-trunk/drizzle/development

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=innodb;

773

775

show create table t1;

774

776

Table Create Table

775

777

t1 CREATE TABLE `t1` (

776

`a` varchar(20) DEFAULT NULL,

778

`a` VARCHAR(20) COLLATE utf8_general_ci DEFAULT NULL,

777

779

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

778

780

) ENGINE=InnoDB COLLATE = utf8_general_ci

779

781

drop table t1;

1150

1152

show create table t1;

1151

1153

Table Create Table

1152

1154

t1 CREATE TABLE `t1` (

1153

`id` int NOT NULL,

1154

`id2` int NOT NULL,

1155

`id` INT NOT NULL,

1156

`id2` INT NOT NULL,

1155

1157

UNIQUE KEY `id` (`id`,`id2`)

1156

1158

) ENGINE=InnoDB COLLATE = utf8_general_ci

1157

1159

show create table t2;

1158

1160

Table Create Table

1159

1161

t2 CREATE TABLE `t2` (

1160

`id` int NOT NULL,

1162

`id` INT NOT NULL,

1161

1163

KEY `t1_id_fk` (`id`),

1162

1164

CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)

1163

1165

) ENGINE=InnoDB COLLATE = utf8_general_ci

1165

1167

show create table t2;

1166

1168

Table Create Table

1167

1169

t2 CREATE TABLE `t2` (

1168

`id` int NOT NULL,

1170

`id` INT NOT NULL,

1169

1171

KEY `id` (`id`),

1170

1172

CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)

1171

1173

) ENGINE=InnoDB COLLATE = utf8_general_ci

1173

1175

show create table t2;

1174

1176

Table Create Table

1175

1177

t2 CREATE TABLE `t2` (

1176

`id` int NOT NULL,

1178

`id` INT NOT NULL,

1177

1179

KEY `id` (`id`),

1178

1180

KEY `id2` (`id`),

1179

1181

CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)

1184

1186

show create table t2;

1185

1187

Table Create Table

1186

1188

t2 CREATE TABLE `t2` (

1187

`id` int NOT NULL,

1189

`id` INT NOT NULL,

1188

1190

KEY `id` (`id`),

1189

1191

CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)

1190

1192

) ENGINE=InnoDB COLLATE = utf8_general_ci

1193

1195

show create table t2;

1194

1196

Table Create Table

1195

1197

t2 CREATE TABLE `t2` (

1196

`id` int NOT NULL,

1197

`id2` int NOT NULL,

1198

`id` INT NOT NULL,

1199

`id2` INT NOT NULL,

1198

1200

KEY `t1_id_fk` (`id`,`id2`),

1199

1201

CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`, `id2`) REFERENCES `t1` (`id`, `id2`)

1200

1202

) ENGINE=InnoDB COLLATE = utf8_general_ci

1202

1204

show create table t2;

1203

1205

Table Create Table

1204

1206

t2 CREATE TABLE `t2` (

1205

`id` int NOT NULL,

1206

`id2` int NOT NULL,

1207

`id` INT NOT NULL,

1208

`id2` INT NOT NULL,

1207

1209

UNIQUE KEY `id` (`id`,`id2`),

1208

1210

CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`, `id2`) REFERENCES `t1` (`id`, `id2`)

1209

1211

) ENGINE=InnoDB COLLATE = utf8_general_ci

1212

1214

show create table t2;

1213

1215

Table Create Table

1214

1216

t2 CREATE TABLE `t2` (

1215

`id` int NOT NULL,

1216

`id2` int NOT NULL,

1217

`id` INT NOT NULL,

1218

`id2` INT NOT NULL,

1217

1219

UNIQUE KEY `id` (`id`,`id2`),

1218

1220

KEY `t1_id_fk` (`id2`,`id`),

1219

1221

CONSTRAINT `t1_id_fk` FOREIGN KEY (`id2`, `id`) REFERENCES `t1` (`id`, `id2`)

1223

1225

show create table t2;

1224

1226

Table Create Table

1225

1227

t2 CREATE TABLE `t2` (

1226

`id` int NOT NULL,

1227

`id2` int NOT NULL,

1228

`id` INT NOT NULL,

1229

`id2` INT NOT NULL,

1228

1230

UNIQUE KEY `id` (`id`,`id2`),

1229

1231

CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)

1230

1232

) ENGINE=InnoDB COLLATE = utf8_general_ci

1233

1235

show create table t2;

1234

1236

Table Create Table

1235

1237

t2 CREATE TABLE `t2` (

1236

`id` int NOT NULL,

1237

`id2` int NOT NULL,

1238

`id` INT NOT NULL,

1239

`id2` INT NOT NULL,

1238

1240

UNIQUE KEY `id` (`id`,`id2`),

1239

1241

KEY `t1_id_fk` (`id2`,`id`),

1240

1242

CONSTRAINT `t1_id_fk` FOREIGN KEY (`id2`, `id`) REFERENCES `t1` (`id`, `id2`)

1244

1246

show create table t2;

1245

1247

Table Create Table

1246

1248

t2 CREATE TABLE `t2` (

1247

`id` int NOT NULL AUTO_INCREMENT,

1248

`id2` int NOT NULL,

1249

`id` INT NOT NULL AUTO_INCREMENT,

1250

`id2` INT NOT NULL,

1249

1251

PRIMARY KEY (`id`),

1250

1252

KEY `id` (`id`,`id2`),

1251

1253

CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)

1255

1257

show create table t2;

1256

1258

Table Create Table

1257

1259

t2 CREATE TABLE `t2` (

1258

`id` int NOT NULL AUTO_INCREMENT,

1259

`id2` int NOT NULL,

1260

`id` INT NOT NULL AUTO_INCREMENT,

1261

`id2` INT NOT NULL,

1260

1262

KEY `t1_id_fk` (`id`),

1261

1263

CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)

1262

1264

) ENGINE=InnoDB COLLATE = utf8_general_ci

1264

1266

show create table t2;

1265

1267

Table Create Table

1266

1268

t2 CREATE TABLE `t2` (

1267

`id` int NOT NULL AUTO_INCREMENT,

1268

`id2` int NOT NULL,

1269

`id` INT NOT NULL AUTO_INCREMENT,

1270

`id2` INT NOT NULL,

1269

1271

KEY `id_test` (`id`),

1270

1272

KEY `id_test2` (`id`,`id2`),

1271

1273

CONSTRAINT `t1_id_fk` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)

1277

1279

show create table t2;

1278

1280

Table Create Table

1279

1281

t2 CREATE TABLE `t2` (

1280

`a` int NOT NULL AUTO_INCREMENT,

1281

`b` int DEFAULT NULL,

1282

`a` INT NOT NULL AUTO_INCREMENT,

1283

`b` INT DEFAULT NULL,

1282

1284

PRIMARY KEY (`a`),

1283

1285

UNIQUE KEY `b_2` (`b`),

1284

1286

KEY `b` (`b`),

1289

1291

show create table t2;

1290

1292

Table Create Table

1291

1293

t2 CREATE TABLE `t2` (

1292

`a` int NOT NULL AUTO_INCREMENT,

1293

`b` int DEFAULT NULL,

1294

`a` INT NOT NULL AUTO_INCREMENT,

1295

`b` INT DEFAULT NULL,

1294

1296

PRIMARY KEY (`a`),

1295

1297

UNIQUE KEY `b` (`b`),

1296

1298

CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t1` (`id`),

1509

1511

show create table t1;

1510

1512

Table Create Table

1511

1513

t1 CREATE TABLE `t1` (

1512

`v` varchar(10) DEFAULT NULL,

1513

`c` varchar(10) DEFAULT NULL,

1514

`t` text

1514

`v` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

1515

`c` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

1516

`t` TEXT COLLATE utf8_general_ci

1515

1517

) ENGINE=InnoDB COLLATE = utf8_general_ci

1516

1518

create table t2 like t1;

1517

1519

show create table t2;

1518

1520

Table Create Table

1519

1521

t2 CREATE TABLE `t2` (

1520

`v` varchar(10) DEFAULT NULL,

1521

`c` varchar(10) DEFAULT NULL,

1522

`t` text

1522

`v` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

1523

`c` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

1524

`t` TEXT COLLATE utf8_general_ci

1523

1525

) ENGINE=InnoDB COLLATE = utf8_general_ci

1524

1526

create table t3 select * from t1;

1525

1527

show create table t3;

1526

1528

Table Create Table

1527

1529

t3 CREATE TABLE `t3` (

1528

`v` varchar(10) DEFAULT NULL,

1529

`c` varchar(10) DEFAULT NULL,

1530

`t` text

1530

`v` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

1531

`c` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

1532

`t` TEXT COLLATE utf8_general_ci

1531

1533

) ENGINE=InnoDB COLLATE = utf8_general_ci

1532

1534

alter table t1 modify c varchar(10);

1533

1535

show create table t1;

1534

1536

Table Create Table

1535

1537

t1 CREATE TABLE `t1` (

1536

`v` varchar(10) DEFAULT NULL,

1537

`c` varchar(10) DEFAULT NULL,

1538

`t` text

1538

`v` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

1539

`c` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

1540

`t` TEXT COLLATE utf8_general_ci

1539

1541

) ENGINE=InnoDB COLLATE = utf8_general_ci

1540

1542

alter table t1 modify v char(10);

1541

1543

show create table t1;

1542

1544

Table Create Table

1543

1545

t1 CREATE TABLE `t1` (

1544

`v` varchar(10) DEFAULT NULL,

1545

`c` varchar(10) DEFAULT NULL,

1546

`t` text

1546

`v` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

1547

`c` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

1548

`t` TEXT COLLATE utf8_general_ci

1547

1549

) ENGINE=InnoDB COLLATE = utf8_general_ci

1548

1550

alter table t1 modify t varchar(10);

1549

1551

show create table t1;

1550

1552

Table Create Table

1551

1553

t1 CREATE TABLE `t1` (

1552

`v` varchar(10) DEFAULT NULL,

1553

`c` varchar(10) DEFAULT NULL,

1554

`t` varchar(10) DEFAULT NULL

1554

`v` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

1555

`c` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

1556

`t` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL

1555

1557

) ENGINE=InnoDB COLLATE = utf8_general_ci

1556

1558

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

1557

1559

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

1562

1564

show create table t1;

1563

1565

Table Create Table

1564

1566

t1 CREATE TABLE `t1` (

1565

`v` varchar(10) DEFAULT NULL,

1566

`c` varchar(10) DEFAULT NULL,

1567

`t` text,

1567

`v` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

1568

`c` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

1569

`t` TEXT COLLATE utf8_general_ci,

1568

1570

KEY `v` (`v`),

1569

1571

KEY `c` (`c`),

1570

1572

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

1783

1785

show create table t1;

1784

1786

Table Create Table

1785

1787

t1 CREATE TABLE `t1` (

1786

`v` varchar(300) DEFAULT NULL,

1787

`c` varchar(10) DEFAULT NULL,

1788

`t` text,

1788

`v` VARCHAR(300) COLLATE utf8_general_ci DEFAULT NULL,

1789

`c` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

1790

`t` TEXT COLLATE utf8_general_ci,

1789

1791

KEY `c` (`c`),

1790

1792

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

1791

1793

KEY `v` (`v`(255))

1863

1865

show create table t1;

1864

1866

Table Create Table

1865

1867

t1 CREATE TABLE `t1` (

1866

`v` varchar(300) DEFAULT NULL,

1867

`c` varchar(10) DEFAULT NULL,

1868

`t` text,

1868

`v` VARCHAR(300) COLLATE utf8_general_ci DEFAULT NULL,

1869

`c` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

1870

`t` TEXT COLLATE utf8_general_ci,

1869

1871

KEY `c` (`c`),

1870

1872

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

1871

1873

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

1945

1947

show create table t1;

1946

1948

Table Create Table

1947

1949

t1 CREATE TABLE `t1` (

1948

`v` varchar(600) DEFAULT NULL,

1949

`c` varchar(10) DEFAULT NULL,

1950

`t` text,

1950

`v` VARCHAR(600) COLLATE utf8_general_ci DEFAULT NULL,

1951

`c` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

1952

`t` TEXT COLLATE utf8_general_ci,

1951

1953

KEY `c` (`c`),

1952

1954

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

1953

1955

KEY `v` (`v`(255))

2023

2025

show create table t1;

2024

2026

Table Create Table

2025

2027

t1 CREATE TABLE `t1` (

2026

`v` varchar(10) DEFAULT NULL,

2027

`c` varchar(10) DEFAULT NULL,

2028

`t` text,

2028

`v` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

2029

`c` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

2030

`t` TEXT COLLATE utf8_general_ci,

2029

2031

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

2030

2032

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

2031

2033

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

2035

2037

show create table t1;

2036

2038

Table Create Table

2037

2039

t1 CREATE TABLE `t1` (

2038

`v` varchar(10) DEFAULT NULL

2040

`v` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL

2039

2041

) ENGINE=InnoDB COLLATE = utf8_general_ci

2040

2042

drop table t1;

2041

2043

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

2042

2044

show create table t1;

2043

2045

Table Create Table

2044

2046

t1 CREATE TABLE `t1` (

2045

`v` varchar(10) DEFAULT NULL,

2046

`c` varchar(10) DEFAULT NULL

2047

`v` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL,

2048

`c` VARCHAR(10) COLLATE utf8_general_ci DEFAULT NULL

2047

2049

) ENGINE=InnoDB COLLATE = utf8_general_ci

2048

2050

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

2049

2051

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

2068

2070

show create table t1;

2069

2071

Table Create Table

2070

2072

t1 CREATE TABLE `t1` (

2071

`v` varchar(16383) DEFAULT NULL

2073

`v` VARCHAR(16383) COLLATE utf8_general_ci DEFAULT NULL

2072

2074

) ENGINE=InnoDB COLLATE = utf8_general_ci

2073

2075

drop table t1;

2074

2076

create table t1 (v varchar(16383));

2075

2077

show create table t1;

2076

2078

Table Create Table

2077

2079

t1 CREATE TABLE `t1` (

2078

`v` varchar(16383) DEFAULT NULL

2080

`v` VARCHAR(16383) COLLATE utf8_general_ci DEFAULT NULL

2079

2081

) ENGINE=InnoDB COLLATE = utf8_general_ci

2080

2082

drop table t1;

2081

2083

set storage_engine=InnoDB;

2192

2194

show create table t9;

2193

2195

Table Create Table

2194

2196

t9 CREATE TABLE `t9` (

2195

`col1` varchar(512) DEFAULT NULL,

2196

`col2` varchar(512) DEFAULT NULL,

2197

`col1` VARCHAR(512) COLLATE utf8_general_ci DEFAULT NULL,

2198

`col2` VARCHAR(512) COLLATE utf8_general_ci DEFAULT NULL,

2197

2199

KEY `col1` (`col1`(255),`col2`(255))

2198

2200

) ENGINE=InnoDB COLLATE = utf8_general_ci

2199

2201

drop table t1, t2, t4, t5, t6, t7, t8, t9;

2212

2214

show create table t1;

2213

2215

Table Create Table

2214

2216

t1 CREATE TABLE `t1` (

2215

`col1` varchar(768) DEFAULT NULL,

2217

`col1` VARCHAR(768) COLLATE utf8_general_ci DEFAULT NULL,

2216

2218

KEY `col1` (`col1`(255))

2217

2219

) ENGINE=InnoDB COLLATE = utf8_general_ci

2218

2220

drop table t1, t2, t3, t4;

2411

2413

SHOW CREATE TABLE t2;

2412

2414

Table Create Table

2413

2415

t2 CREATE TABLE `t2` (

2414

`a` int DEFAULT NULL,

2416

`a` INT DEFAULT NULL,

2415

2417

KEY `t2_ibfk_0` (`a`)

2416

2418

) ENGINE=InnoDB COLLATE = utf8_general_ci

2417

2419

DROP TABLE t2,t1;

2448

2450

show create table t1;

2449

2451

Table Create Table

2450

2452

t1 CREATE TABLE `t1` (

2451

`c1` bigint NOT NULL,

2452

`c2` bigint NOT NULL,

2453

`c1` BIGINT NOT NULL,

2454

`c2` BIGINT NOT NULL,

2453

2455

PRIMARY KEY (`c1`),

2454

2456

UNIQUE KEY `c2` (`c2`),

2455

2457

CONSTRAINT `c2_fk` FOREIGN KEY (`c2`) REFERENCES `t2` (`c1`) ON DELETE CASCADE

2458

2460

show create table t1;

2459

2461

Table Create Table

2460

2462

t1 CREATE TABLE `t1` (

2461

`c1` bigint NOT NULL,

2462

`c2` bigint NOT NULL,

2463

`c1` BIGINT NOT NULL,

2464

`c2` BIGINT NOT NULL,

2463

2465

PRIMARY KEY (`c1`),

2464

2466

UNIQUE KEY `c2` (`c2`)

2465

2467

) ENGINE=InnoDB COLLATE = utf8_general_ci

2649

2651

SHOW CREATE TABLE t2;

2650

2652

Table Create Table

2651

2653

t2 CREATE TABLE `t2` (

2652

`id` int NOT NULL,

2653

`f` int NOT NULL,

2654

`id` INT NOT NULL,

2655

`f` INT NOT NULL,

2654

2656

PRIMARY KEY (`id`),

2655

2657

KEY `f` (`f`),

2656

2658

CONSTRAINT `t2_t1` FOREIGN KEY (`id`) REFERENCES `t1` (`id`) ON UPDATE CASCADE ON DELETE CASCADE,

2686

2688

SHOW CREATE TABLE t1;

2687

2689

Table Create Table

2688

2690

t1 CREATE TABLE `t1` (

2689

`id` int NOT NULL AUTO_INCREMENT,

2691

`id` INT NOT NULL AUTO_INCREMENT,

2690

2692

PRIMARY KEY (`id`)

2691

2693

) ENGINE=InnoDB COLLATE = utf8_general_ci AUTO_INCREMENT=42

2692

2694

CREATE TABLE t2 (id int PRIMARY KEY) ENGINE=InnoDB;

2695

2697

SHOW CREATE TABLE t1;

2696

2698

Table Create Table

2697

2699

t1 CREATE TABLE `t1` (

2698

`id` int NOT NULL AUTO_INCREMENT,

2700

`id` INT NOT NULL AUTO_INCREMENT,

2699

2701

PRIMARY KEY (`id`),

2700

2702

CONSTRAINT `t1_t2` FOREIGN KEY (`id`) REFERENCES `t2` (`id`)

2701

2703

) ENGINE=InnoDB COLLATE = utf8_general_ci

2716

2718

-10

2717

2719

2718

2720

DROP TABLE t1;

2721

SET GLOBAL innodb_lock_wait_timeout=@orig_lock_wait_timeout ;

Older »