1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
|
-- $Header: /home/faga/work/sqlfairy_svn/sqlfairy-cvsbackup/sqlfairy/t/data/mysql/entire_syntax.sql,v 1.2 2003-08-17 01:41:48 rossta Exp $
-- all data types
DROP TABLE IF EXISTS t01;
CREATE TABLE t01 (
i01 TINYINT,
i02 SMALLINT,
i03 MEDIUMINT,
i04 INT,
i05 INTEGER,
i06 BIGINT,
r01 REAL,
r02 DOUBLE,
r03 DOUBLE PRECISION,
r04 FLOAT,
n01 DECIMAL,
n02 NUMERIC,
c01 CHAR(10),
c02 VARCHAR(10),
c03 CHAR(10) BINARY,
c04 VARCHAR(10) BINARY,
-- c05 NATIONAL CHAR(10),
-- c06 NATIONAL VARCHAR(10),
-- c07 NATIONAL CHAR(10) BINARY,
-- c08 NATIONAL VARCHAR(10) BINARY,
d01 DATE,
d02 TIME,
d03 TIMESTAMP,
d04 DATETIME,
d05 YEAR,
b01 TINYBLOB,
b02 BLOB,
b03 MEDIUMBLOB,
b04 LONGBLOB,
t01 TINYTEXT,
t02 TEXT,
t03 MEDIUMTEXT,
t04 LONGTEXT,
e01 ENUM('value1','value2'),
-- s01 SET('value1','value2'), -- not supported
i11 TINYINT(1),
i12 SMALLINT(1),
i13 MEDIUMINT(1),
i14 INT(1),
i15 INTEGER(1),
i16 BIGINT(1),
r11 REAL(2,1),
r12 DOUBLE(2,1),
r13 DOUBLE PRECISION(2,1),
r14 FLOAT(1),
r15 FLOAT(2,1),
n11 DECIMAL(1),
n12 DECIMAL(2,1),
n13 NUMERIC(2),
n14 NUMERIC(2,1),
d11 TIMESTAMP(2),
d12 YEAR(2)
);
-- all data types, NOT NULL
DROP TABLE IF EXISTS t02;
CREATE TABLE t02 (
i01 TINYINT NOT NULL,
i02 SMALLINT NOT NULL,
i03 MEDIUMINT NOT NULL,
i04 INT NOT NULL,
i05 INTEGER NOT NULL,
i06 BIGINT NOT NULL,
r01 REAL NOT NULL,
r02 DOUBLE NOT NULL,
r03 DOUBLE PRECISION NOT NULL,
r04 FLOAT NOT NULL,
n01 DECIMAL NOT NULL,
n02 NUMERIC NOT NULL,
c01 CHAR(10) NOT NULL,
c02 VARCHAR(10) NOT NULL,
c03 CHAR(10) BINARY NOT NULL,
c04 VARCHAR(10) BINARY NOT NULL,
-- c05 NATIONAL CHAR(10) NOT NULL,
-- c06 NATIONAL VARCHAR(10) NOT NULL,
-- c07 NATIONAL CHAR(10) BINARY NOT NULL,
-- c08 NATIONAL VARCHAR(10) BINARY NOT NULL,
d01 DATE NOT NULL,
d02 TIME NOT NULL,
d03 TIMESTAMP NOT NULL,
d04 DATETIME NOT NULL,
d05 YEAR NOT NULL,
b01 TINYBLOB NOT NULL,
b02 BLOB NOT NULL,
b03 MEDIUMBLOB NOT NULL,
b04 LONGBLOB NOT NULL,
t01 TINYTEXT NOT NULL,
t02 TEXT NOT NULL,
t03 MEDIUMTEXT NOT NULL,
t04 LONGTEXT NOT NULL,
e01 ENUM('value1','value2') NOT NULL,
-- s01 SET('value1','value2'), -- not supported
i11 TINYINT(1) NOT NULL,
i12 SMALLINT(1) NOT NULL,
i13 MEDIUMINT(1) NOT NULL,
i14 INT(1) NOT NULL,
i15 INTEGER(1) NOT NULL,
i16 BIGINT(1) NOT NULL,
r11 REAL(2,1) NOT NULL,
r12 DOUBLE(2,1) NOT NULL,
r13 DOUBLE PRECISION(2,1) NOT NULL,
r14 FLOAT(1) NOT NULL,
r15 FLOAT(2,1) NOT NULL,
n11 DECIMAL(1) NOT NULL,
n12 DECIMAL(2,1) NOT NULL,
n13 NUMERIC(2) NOT NULL,
n14 NUMERIC(2,1) NOT NULL,
d11 TIMESTAMP(2) NOT NULL,
d12 YEAR(2) NOT NULL
);
-- all data types, DEFAULT
DROP TABLE IF EXISTS t03;
CREATE TABLE t03 (
i01 TINYINT DEFAULT 0,
i02 SMALLINT DEFAULT 0,
i03 MEDIUMINT DEFAULT 0,
i04 INT DEFAULT 0,
i05 INTEGER DEFAULT 0,
i06 BIGINT DEFAULT 0,
r01 REAL DEFAULT 0,
r02 DOUBLE DEFAULT 0,
r03 DOUBLE PRECISION DEFAULT 0,
r04 FLOAT DEFAULT 0,
n01 DECIMAL DEFAULT 0,
n02 NUMERIC DEFAULT 0,
c01 CHAR(10) DEFAULT '',
c02 VARCHAR(10) DEFAULT '',
c03 CHAR(10) BINARY DEFAULT '',
c04 VARCHAR(10) BINARY DEFAULT '',
-- c05 NATIONAL CHAR(10) DEFAULT '',
-- c06 NATIONAL VARCHAR(10) DEFAULT '',
-- c07 NATIONAL CHAR(10) BINARY DEFAULT '',
-- c08 NATIONAL VARCHAR(10) BINARY DEFAULT '',
d01 DATE DEFAULT 0,
d02 TIME DEFAULT 0,
d03 TIMESTAMP DEFAULT 0,
d04 DATETIME DEFAULT 0,
d05 YEAR DEFAULT 0,
b01 TINYBLOB DEFAULT '',
b02 BLOB DEFAULT '',
b03 MEDIUMBLOB DEFAULT '',
b04 LONGBLOB DEFAULT '',
t01 TINYTEXT DEFAULT '',
t02 TEXT DEFAULT '',
t03 MEDIUMTEXT DEFAULT '',
t04 LONGTEXT DEFAULT '',
e01 ENUM('value1','value2') DEFAULT 'value1',
-- s01 SET('value1','value2'), -- not supported
i11 TINYINT(1) DEFAULT 0,
i12 SMALLINT(1) DEFAULT 0,
i13 MEDIUMINT(1) DEFAULT 0,
i14 INT(1) DEFAULT 0,
i15 INTEGER(1) DEFAULT 0,
i16 BIGINT(1) DEFAULT 0,
r11 REAL(2,1) DEFAULT 0,
r12 DOUBLE(2,1) DEFAULT 0,
r13 DOUBLE PRECISION(2,1) DEFAULT 0,
r14 FLOAT(1) DEFAULT 0,
r15 FLOAT(2,1) DEFAULT 0,
n11 DECIMAL(1) DEFAULT 0,
n12 DECIMAL(2,1) DEFAULT 0,
n13 NUMERIC(2) DEFAULT 0,
n14 NUMERIC(2,1) DEFAULT 0,
d11 TIMESTAMP(2) DEFAULT 0,
d12 YEAR(2) DEFAULT 0
);
-- all data types, NOT NULL DEFAULT
DROP TABLE IF EXISTS t04;
CREATE TABLE t04 (
i01 TINYINT NOT NULL DEFAULT 0,
i02 SMALLINT NOT NULL DEFAULT 0,
i03 MEDIUMINT NOT NULL DEFAULT 0,
i04 INT NOT NULL DEFAULT 0,
i05 INTEGER NOT NULL DEFAULT 0,
i06 BIGINT NOT NULL DEFAULT 0,
r01 REAL NOT NULL DEFAULT 0,
r02 DOUBLE NOT NULL DEFAULT 0,
r03 DOUBLE PRECISION NOT NULL DEFAULT 0,
r04 FLOAT NOT NULL DEFAULT 0,
n01 DECIMAL NOT NULL DEFAULT 0,
n02 NUMERIC NOT NULL DEFAULT 0,
c01 CHAR(10) NOT NULL DEFAULT '',
c02 VARCHAR(10) NOT NULL DEFAULT '',
c03 CHAR(10) BINARY NOT NULL DEFAULT '',
c04 VARCHAR(10) BINARY NOT NULL DEFAULT '',
-- c05 NATIONAL CHAR(10) NOT NULL DEFAULT '',
-- c06 NATIONAL VARCHAR(10) NOT NULL DEFAULT '',
-- c07 NATIONAL CHAR(10) BINARY NOT NULL DEFAULT '',
-- c08 NATIONAL VARCHAR(10) BINARY NOT NULL DEFAULT '',
d01 DATE NOT NULL DEFAULT 0,
d02 TIME NOT NULL DEFAULT 0,
d03 TIMESTAMP NOT NULL DEFAULT 0,
d04 DATETIME NOT NULL DEFAULT 0,
d05 YEAR NOT NULL DEFAULT 0,
b01 TINYBLOB NOT NULL DEFAULT '',
b02 BLOB NOT NULL DEFAULT '',
b03 MEDIUMBLOB NOT NULL DEFAULT '',
b04 LONGBLOB NOT NULL DEFAULT '',
t01 TINYTEXT NOT NULL DEFAULT '',
t02 TEXT NOT NULL DEFAULT '',
t03 MEDIUMTEXT NOT NULL DEFAULT '',
t04 LONGTEXT NOT NULL DEFAULT '',
e01 ENUM('value1','value2') NOT NULL DEFAULT 'value1',
-- s01 SET('value1','value2'), -- not supported
i11 TINYINT(1) NOT NULL DEFAULT 0,
i12 SMALLINT(1) NOT NULL DEFAULT 0,
i13 MEDIUMINT(1) NOT NULL DEFAULT 0,
i14 INT(1) NOT NULL DEFAULT 0,
i15 INTEGER(1) NOT NULL DEFAULT 0,
i16 BIGINT(1) NOT NULL DEFAULT 0,
r11 REAL(2,1) NOT NULL DEFAULT 0,
r12 DOUBLE(2,1) NOT NULL DEFAULT 0,
r13 DOUBLE PRECISION(2,1) NOT NULL DEFAULT 0,
r14 FLOAT(1) NOT NULL DEFAULT 0,
r15 FLOAT(2,1) NOT NULL DEFAULT 0,
n11 DECIMAL(1) NOT NULL DEFAULT 0,
n12 DECIMAL(2,1) NOT NULL DEFAULT 0,
n13 NUMERIC(2) NOT NULL DEFAULT 0,
n14 NUMERIC(2,1) NOT NULL DEFAULT 0,
d11 TIMESTAMP(2) NOT NULL DEFAULT 0,
d12 YEAR(2) NOT NULL DEFAULT 0
);
-- /*
-- /*-- all data types, NULL
-- /*DROP TABLE IF EXISTS t05;
-- /*CREATE TABLE t05 (
-- /* i01 TINYINT NULL,
-- /* i02 SMALLINT NULL,
-- /* i03 MEDIUMINT NULL,
-- /* i04 INT NULL,
-- /* i05 INTEGER NULL,
-- /* i06 BIGINT NULL,
-- /* r01 REAL NULL,
-- /* r02 DOUBLE NULL,
-- /* r03 DOUBLE PRECISION NULL,
-- /* r04 FLOAT NULL,
-- /* n01 DECIMAL NULL,
-- /* n02 NUMERIC NULL,
-- /* c01 CHAR(10) NULL,
-- /* c02 VARCHAR(10) NULL,
-- /* c03 CHAR(10) BINARY NULL,
-- /* c04 VARCHAR(10) BINARY NULL,
-- /*-- c05 NATIONAL CHAR(10) NULL,
-- /*-- c06 NATIONAL VARCHAR(10) NULL,
-- /*-- c07 NATIONAL CHAR(10) BINARY NULL,
-- /*-- c08 NATIONAL VARCHAR(10) BINARY NULL,
-- /* d01 DATE NULL,
-- /* d02 TIME NULL,
-- /* d03 TIMESTAMP NULL,
-- /* d04 DATETIME NULL,
-- /* d05 YEAR NULL,
-- /* b01 TINYBLOB NULL,
-- /* b02 BLOB NULL,
-- /* b03 MEDIUMBLOB NULL,
-- /* b04 LONGBLOB NULL,
-- /* t01 TINYTEXT NULL,
-- /* t02 TEXT NULL,
-- /* t03 MEDIUMTEXT NULL,
-- /* t04 LONGTEXT NULL,
-- /*
-- /* e01 ENUM('value1','value2') NULL,
-- /*-- s01 SET('value1','value2'), -- supported
-- /*
-- /* i11 TINYINT(1) NULL,
-- /* i12 SMALLINT(1) NULL,
-- /* i13 MEDIUMINT(1) NULL,
-- /* i14 INT(1) NULL,
-- /* i15 INTEGER(1) NULL,
-- /* i16 BIGINT(1) NULL,
-- /* r11 REAL(2,1) NULL,
-- /* r12 DOUBLE(2,1) NULL,
-- /* r13 DOUBLE PRECISION(2,1) NULL,
-- /* r14 FLOAT(1) NULL,
-- /* r15 FLOAT(2,1) NULL,
-- /* n11 DECIMAL(1) NULL,
-- /* n12 DECIMAL(2,1) NULL,
-- /* n13 NUMERIC(2) NULL,
-- /* n14 NUMERIC(2,1) NULL,
-- /* d11 TIMESTAMP(2) NULL,
-- /* d12 YEAR(2) NULL
-- /*);
-- /*
-- /*-- all data types, NULL DEFAULT
-- /*DROP TABLE IF EXISTS t06;
-- /*CREATE TABLE t06 (
-- /* i01 TINYINT NULL DEFAULT 0,
-- /* i02 SMALLINT NULL DEFAULT 0,
-- /* i03 MEDIUMINT NULL DEFAULT 0,
-- /* i04 INT NULL DEFAULT 0,
-- /* i05 INTEGER NULL DEFAULT 0,
-- /* i06 BIGINT NULL DEFAULT 0,
-- /* r01 REAL NULL DEFAULT 0,
-- /* r02 DOUBLE NULL DEFAULT 0,
-- /* r03 DOUBLE PRECISION NULL DEFAULT 0,
-- /* r04 FLOAT NULL DEFAULT 0,
-- /* n01 DECIMAL NULL DEFAULT 0,
-- /* n02 NUMERIC NULL DEFAULT 0,
-- /* c01 CHAR(10) NULL DEFAULT '',
-- /* c02 VARCHAR(10) NULL DEFAULT '',
-- /* c03 CHAR(10) BINARY NULL DEFAULT '',
-- /* c04 VARCHAR(10) BINARY NULL DEFAULT '',
-- /*-- c05 NATIONAL CHAR(10) NULL DEFAULT '',
-- /*-- c06 NATIONAL VARCHAR(10) NULL DEFAULT '',
-- /*-- c07 NATIONAL CHAR(10) BINARY NULL DEFAULT '',
-- /*-- c08 NATIONAL VARCHAR(10) BINARY NULL DEFAULT '',
-- /* d01 DATE NULL DEFAULT 0,
-- /* d02 TIME NULL DEFAULT 0,
-- /* d03 TIMESTAMP NULL DEFAULT 0,
-- /* d04 DATETIME NULL DEFAULT 0,
-- /* d05 YEAR NULL DEFAULT 0,
-- /* b01 TINYBLOB NULL DEFAULT '',
-- /* b02 BLOB NULL DEFAULT '',
-- /* b03 MEDIUMBLOB NULL DEFAULT '',
-- /* b04 LONGBLOB NULL DEFAULT '',
-- /* t01 TINYTEXT NULL DEFAULT '',
-- /* t02 TEXT NULL DEFAULT '',
-- /* t03 MEDIUMTEXT NULL DEFAULT '',
-- /* t04 LONGTEXT NULL DEFAULT '',
-- /*
-- /* e01 ENUM('value1','value2') NULL DEFAULT 'value1',
-- /*-- s01 SET('value1','value2'), -- supported
-- /*
-- /* i11 TINYINT(1) NULL DEFAULT 0,
-- /* i12 SMALLINT(1) NULL DEFAULT 0,
-- /* i13 MEDIUMINT(1) NULL DEFAULT 0,
-- /* i14 INT(1) NULL DEFAULT 0,
-- /* i15 INTEGER(1) NULL DEFAULT 0,
-- /* i16 BIGINT(1) NULL DEFAULT 0,
-- /* r11 REAL(2,1) NULL DEFAULT 0,
-- /* r12 DOUBLE(2,1) NULL DEFAULT 0,
-- /* r13 DOUBLE PRECISION(2,1) NULL DEFAULT 0,
-- /* r14 FLOAT(1) NULL DEFAULT 0,
-- /* r15 FLOAT(2,1) NULL DEFAULT 0,
-- /* n11 DECIMAL(1) NULL DEFAULT 0,
-- /* n12 DECIMAL(2,1) NULL DEFAULT 0,
-- /* n13 NUMERIC(2) NULL DEFAULT 0,
-- /* n14 NUMERIC(2,1) NULL DEFAULT 0,
-- /* d11 TIMESTAMP(2) NULL DEFAULT 0,
-- /* d12 YEAR(2) NULL DEFAULT 0
-- /*);
-- /**/
-- all data types, UNSIGNED
DROP TABLE IF EXISTS t11;
CREATE TABLE t11 (
i01 TINYINT UNSIGNED NOT NULL DEFAULT 0,
i02 SMALLINT UNSIGNED NOT NULL DEFAULT 0,
i03 MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
i04 INT UNSIGNED NOT NULL DEFAULT 0,
i05 INTEGER UNSIGNED NOT NULL DEFAULT 0,
i06 BIGINT UNSIGNED NOT NULL DEFAULT 0,
r01 REAL UNSIGNED NOT NULL DEFAULT 0,
r02 DOUBLE UNSIGNED NOT NULL DEFAULT 0,
r03 DOUBLE PRECISION UNSIGNED NOT NULL DEFAULT 0,
r04 FLOAT UNSIGNED NOT NULL DEFAULT 0,
n01 DECIMAL UNSIGNED NOT NULL DEFAULT 0,
n02 NUMERIC UNSIGNED NOT NULL DEFAULT 0,
i11 TINYINT(1) UNSIGNED NOT NULL DEFAULT 0,
i12 SMALLINT(1) UNSIGNED NOT NULL DEFAULT 0,
i13 MEDIUMINT(1) UNSIGNED NOT NULL DEFAULT 0,
i14 INT(1) UNSIGNED NOT NULL DEFAULT 0,
i15 INTEGER(1) UNSIGNED NOT NULL DEFAULT 0,
i16 BIGINT(1) UNSIGNED NOT NULL DEFAULT 0,
r11 REAL(2,1) UNSIGNED NOT NULL DEFAULT 0,
r12 DOUBLE(2,1) UNSIGNED NOT NULL DEFAULT 0,
r13 DOUBLE PRECISION(2,1) UNSIGNED NOT NULL DEFAULT 0,
r14 FLOAT(1) UNSIGNED NOT NULL DEFAULT 0,
r15 FLOAT(2,1) UNSIGNED NOT NULL DEFAULT 0,
n11 DECIMAL(1) UNSIGNED NOT NULL DEFAULT 0,
n12 DECIMAL(2,1) UNSIGNED NOT NULL DEFAULT 0,
n13 NUMERIC(2) UNSIGNED NOT NULL DEFAULT 0,
n14 NUMERIC(2,1) UNSIGNED NOT NULL DEFAULT 0
);
-- all data types, ZEROFILL
DROP TABLE IF EXISTS t12;
CREATE TABLE t12 (
i01 TINYINT ZEROFILL NOT NULL DEFAULT 0,
i02 SMALLINT ZEROFILL NOT NULL DEFAULT 0,
i03 MEDIUMINT ZEROFILL NOT NULL DEFAULT 0,
i04 INT ZEROFILL NOT NULL DEFAULT 0,
i05 INTEGER ZEROFILL NOT NULL DEFAULT 0,
i06 BIGINT ZEROFILL NOT NULL DEFAULT 0,
r01 REAL ZEROFILL NOT NULL DEFAULT 0,
r02 DOUBLE ZEROFILL NOT NULL DEFAULT 0,
r03 DOUBLE PRECISION ZEROFILL NOT NULL DEFAULT 0,
r04 FLOAT ZEROFILL NOT NULL DEFAULT 0,
n01 DECIMAL ZEROFILL NOT NULL DEFAULT 0,
n02 NUMERIC ZEROFILL NOT NULL DEFAULT 0,
i11 TINYINT(1) ZEROFILL NOT NULL DEFAULT 0,
i12 SMALLINT(1) ZEROFILL NOT NULL DEFAULT 0,
i13 MEDIUMINT(1) ZEROFILL NOT NULL DEFAULT 0,
i14 INT(1) ZEROFILL NOT NULL DEFAULT 0,
i15 INTEGER(1) ZEROFILL NOT NULL DEFAULT 0,
i16 BIGINT(1) ZEROFILL NOT NULL DEFAULT 0,
r11 REAL(2,1) ZEROFILL NOT NULL DEFAULT 0,
r12 DOUBLE(2,1) ZEROFILL NOT NULL DEFAULT 0,
r13 DOUBLE PRECISION(2,1) ZEROFILL NOT NULL DEFAULT 0,
r14 FLOAT(1) ZEROFILL NOT NULL DEFAULT 0,
r15 FLOAT(2,1) ZEROFILL NOT NULL DEFAULT 0,
n11 DECIMAL(1) ZEROFILL NOT NULL DEFAULT 0,
n12 DECIMAL(2,1) ZEROFILL NOT NULL DEFAULT 0,
n13 NUMERIC(2,1) ZEROFILL NOT NULL DEFAULT 0,
n14 NUMERIC(2,1) ZEROFILL NOT NULL DEFAULT 0
);
-- all data types, UNSIGNED ZEROFILL
DROP TABLE IF EXISTS t13;
CREATE TABLE t13 (
i01 TINYINT UNSIGNED ZEROFILL NOT NULL DEFAULT 0,
i02 SMALLINT UNSIGNED ZEROFILL NOT NULL DEFAULT 0,
i03 MEDIUMINT UNSIGNED ZEROFILL NOT NULL DEFAULT 0,
i04 INT UNSIGNED ZEROFILL NOT NULL DEFAULT 0,
i05 INTEGER UNSIGNED ZEROFILL NOT NULL DEFAULT 0,
i06 BIGINT UNSIGNED ZEROFILL NOT NULL DEFAULT 0,
r01 REAL UNSIGNED ZEROFILL NOT NULL DEFAULT 0,
r02 DOUBLE UNSIGNED ZEROFILL NOT NULL DEFAULT 0,
r03 DOUBLE PRECISION UNSIGNED ZEROFILL NOT NULL DEFAULT 0,
r04 FLOAT UNSIGNED ZEROFILL NOT NULL DEFAULT 0,
n01 DECIMAL UNSIGNED ZEROFILL NOT NULL DEFAULT 0,
n02 NUMERIC UNSIGNED ZEROFILL NOT NULL DEFAULT 0,
i11 TINYINT(1) UNSIGNED ZEROFILL NOT NULL DEFAULT 0,
i12 SMALLINT(1) UNSIGNED ZEROFILL NOT NULL DEFAULT 0,
i13 MEDIUMINT(1) UNSIGNED ZEROFILL NOT NULL DEFAULT 0,
i14 INT(1) UNSIGNED ZEROFILL NOT NULL DEFAULT 0,
i15 INTEGER(1) UNSIGNED ZEROFILL NOT NULL DEFAULT 0,
i16 BIGINT(1) UNSIGNED ZEROFILL NOT NULL DEFAULT 0,
r11 REAL(2,1) UNSIGNED ZEROFILL NOT NULL DEFAULT 0,
r12 DOUBLE(2,1) UNSIGNED ZEROFILL NOT NULL DEFAULT 0,
r13 DOUBLE PRECISION(2,1) UNSIGNED ZEROFILL NOT NULL DEFAULT 0,
r14 FLOAT(1) UNSIGNED ZEROFILL NOT NULL DEFAULT 0,
r15 FLOAT(2,1) UNSIGNED ZEROFILL NOT NULL DEFAULT 0,
n11 DECIMAL(1) UNSIGNED ZEROFILL NOT NULL DEFAULT 0,
n12 DECIMAL(2,1) UNSIGNED ZEROFILL NOT NULL DEFAULT 0,
n13 NUMERIC(2,1) UNSIGNED ZEROFILL NOT NULL DEFAULT 0,
n14 NUMERIC(2,1) UNSIGNED ZEROFILL NOT NULL DEFAULT 0
);
-- all keys
DROP TABLE IF EXISTS t21;
CREATE TABLE t21 (
c01 CHAR(10) NOT NULL,
c02 VARCHAR(10) NOT NULL,
KEY k01 (c01),
KEY k02 (c01(1)),
INDEX k03 (c01),
INDEX k04 (c01(1)),
UNIQUE k05 (c01),
UNIQUE k06 (c01(1)),
UNIQUE INDEX k07 (c01),
UNIQUE INDEX k08 (c01(1)),
FULLTEXT k09 (c01),
FULLTEXT INDEX k10 (c01),
KEY k11 (c01, c02),
KEY k12 (c01(1), c02),
INDEX k13 (c01, c02),
INDEX k14 (c01(1), c02),
UNIQUE k15 (c01, c02),
UNIQUE k16 (c01(1), c02),
UNIQUE INDEX k17 (c01, c02),
UNIQUE INDEX k18 (c01(1), c02),
FULLTEXT k19 (c01, c02),
FULLTEXT INDEX k20 (c01, c02),
PRIMARY KEY (c01)
);
-- multi-field primary key
DROP TABLE IF EXISTS t22;
CREATE TABLE t22 (
c01 VARCHAR(10) NOT NULL,
c02 VARCHAR(10) NOT NULL,
PRIMARY KEY (c01, c02)
);
-- multi-field primary key with length
DROP TABLE IF EXISTS t23;
CREATE TABLE t23 (
c01 VARCHAR(255) NOT NULL,
c02 VARCHAR(255) NOT NULL,
PRIMARY KEY (c01(10), c02(10))
);
-- primary key 2nd syntax
DROP TABLE IF EXISTS t24;
CREATE TABLE t24 (
c01 VARCHAR(255) NOT NULL PRIMARY KEY,
c02 VARCHAR(255) NOT NULL
);
-- all data types, AUTO_INCREMENT
DROP TABLE IF EXISTS k01;
CREATE TABLE k01 (
i01 TINYINT UNSIGNED NOT NULL DEFAULT 0 AUTO_INCREMENT PRIMARY KEY
);
DROP TABLE IF EXISTS k02;
CREATE TABLE k02 (
i02 SMALLINT UNSIGNED NOT NULL DEFAULT 0 AUTO_INCREMENT PRIMARY KEY
);
DROP TABLE IF EXISTS k03;
CREATE TABLE k03 (
i03 MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 AUTO_INCREMENT PRIMARY KEY
);
DROP TABLE IF EXISTS k04;
CREATE TABLE k04 (
i04 INT UNSIGNED NOT NULL DEFAULT 0 AUTO_INCREMENT PRIMARY KEY
);
DROP TABLE IF EXISTS k05;
CREATE TABLE k05 (
i05 INTEGER UNSIGNED NOT NULL DEFAULT 0 AUTO_INCREMENT PRIMARY KEY
);
DROP TABLE IF EXISTS k06;
CREATE TABLE k06 (
i06 BIGINT UNSIGNED NOT NULL DEFAULT 0 AUTO_INCREMENT PRIMARY KEY
);
DROP TABLE IF EXISTS k11;
CREATE TABLE k11 (
i11 TINYINT(1) UNSIGNED NOT NULL DEFAULT 0 AUTO_INCREMENT PRIMARY KEY
);
DROP TABLE IF EXISTS k12;
CREATE TABLE k12 (
i12 SMALLINT(1) UNSIGNED NOT NULL DEFAULT 0 AUTO_INCREMENT PRIMARY KEY
);
DROP TABLE IF EXISTS k13;
CREATE TABLE k13 (
i13 MEDIUMINT(1) UNSIGNED NOT NULL DEFAULT 0 AUTO_INCREMENT PRIMARY KEY
);
DROP TABLE IF EXISTS k14;
CREATE TABLE k14 (
i14 INT(1) UNSIGNED NOT NULL DEFAULT 0 AUTO_INCREMENT PRIMARY KEY
);
DROP TABLE IF EXISTS k15;
CREATE TABLE k15 (
i15 INTEGER(1) UNSIGNED NOT NULL DEFAULT 0 AUTO_INCREMENT PRIMARY KEY
);
DROP TABLE IF EXISTS k16;
CREATE TABLE k16 (
i16 BIGINT(1) UNSIGNED NOT NULL DEFAULT 0 AUTO_INCREMENT PRIMARY KEY
);
|
