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
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
|
#
# Fixed point columns (NUMERIC, DECIMAL)
#
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (d DECIMAL ZEROFILL <CUSTOM_COL_OPTIONS>,
d0 DECIMAL(0) ZEROFILL <CUSTOM_COL_OPTIONS>,
d1_1 DECIMAL(1,1) ZEROFILL <CUSTOM_COL_OPTIONS>,
d10_2 DECIMAL(10,2) ZEROFILL <CUSTOM_COL_OPTIONS>,
d60_10 DECIMAL(60,10) ZEROFILL <CUSTOM_COL_OPTIONS>,
n NUMERIC ZEROFILL <CUSTOM_COL_OPTIONS>,
n0_0 NUMERIC(0,0) ZEROFILL <CUSTOM_COL_OPTIONS>,
n1 NUMERIC(1) ZEROFILL <CUSTOM_COL_OPTIONS>,
n20_4 NUMERIC(20,4) ZEROFILL <CUSTOM_COL_OPTIONS>,
n65_4 NUMERIC(65,4) ZEROFILL <CUSTOM_COL_OPTIONS>
) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
SHOW COLUMNS IN t1;
Field Type Null Key Default Extra
d decimal(10,0) unsigned zerofill # # #
d0 decimal(10,0) unsigned zerofill # # #
d1_1 decimal(1,1) unsigned zerofill # # #
d10_2 decimal(10,2) unsigned zerofill # # #
d60_10 decimal(60,10) unsigned zerofill # # #
n decimal(10,0) unsigned zerofill # # #
n0_0 decimal(10,0) unsigned zerofill # # #
n1 decimal(1,0) unsigned zerofill # # #
n20_4 decimal(20,4) unsigned zerofill # # #
n65_4 decimal(65,4) unsigned zerofill # # #
INSERT INTO t1 (d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4) VALUES (100,123456,0.3,40000.25,123456789123456789.10001,1024,7000.0,8.0,999999.9,9223372036854775807);
INSERT INTO t1 (d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4) VALUES (0,0,0,0,0,0,0,0,0,0);
INSERT INTO t1 (d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4) VALUES (9999999999.0,9999999999.0,0.9,99999999.99,99999999999999999999999999999999999999999999999999.9999999999,9999999999.0,9999999999.0,9.0,9999999999999999.9999,9999999999999999999999999999999999999999999999999999999999999.9999);
SELECT d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4 FROM t1;
d d0 d1_1 d10_2 d60_10 n n0_0 n1 n20_4 n65_4
0000000000 0000000000 0.0 00000000.00 00000000000000000000000000000000000000000000000000.0000000000 0000000000 0000000000 0 0000000000000000.0000 0000000000000000000000000000000000000000000000000000000000000.0000
0000000100 0000123456 0.3 00040000.25 00000000000000000000000000000000123456789123456789.1000100000 0000001024 0000007000 8 0000000000999999.9000 0000000000000000000000000000000000000000009223372036854775807.0000
9999999999 9999999999 0.9 99999999.99 99999999999999999999999999999999999999999999999999.9999999999 9999999999 9999999999 9 9999999999999999.9999 9999999999999999999999999999999999999999999999999999999999999.9999
INSERT INTO t1 (d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4) VALUES (-100,-123456,-0.3,-40000.25,-123456789123456789.10001,-1024,-7000.0,-8.0,-999999.9,-9223372036854775807);
Warnings:
Warning 1264 Out of range value for column 'd' at row 1
Warning 1264 Out of range value for column 'd0' at row 1
Warning 1264 Out of range value for column 'd1_1' at row 1
Warning 1264 Out of range value for column 'd10_2' at row 1
Warning 1264 Out of range value for column 'd60_10' at row 1
Warning 1264 Out of range value for column 'n' at row 1
Warning 1264 Out of range value for column 'n0_0' at row 1
Warning 1264 Out of range value for column 'n1' at row 1
Warning 1264 Out of range value for column 'n20_4' at row 1
Warning 1264 Out of range value for column 'n65_4' at row 1
INSERT INTO t1 (d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4) VALUES (-9999999999.0,-9999999999.0,-0.9,-99999999.99,-99999999999999999999999999999999999999999999999999.9999999999,-9999999999.0,-9999999999.0,-9.0,-9999999999999999.9999,-9999999999999999999999999999999999999999999999999999999999999.9999);
Warnings:
Warning 1264 Out of range value for column 'd' at row 1
Warning 1264 Out of range value for column 'd0' at row 1
Warning 1264 Out of range value for column 'd1_1' at row 1
Warning 1264 Out of range value for column 'd10_2' at row 1
Warning 1264 Out of range value for column 'd60_10' at row 1
Warning 1264 Out of range value for column 'n' at row 1
Warning 1264 Out of range value for column 'n0_0' at row 1
Warning 1264 Out of range value for column 'n1' at row 1
Warning 1264 Out of range value for column 'n20_4' at row 1
Warning 1264 Out of range value for column 'n65_4' at row 1
SELECT d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4 FROM t1;
d d0 d1_1 d10_2 d60_10 n n0_0 n1 n20_4 n65_4
0000000000 0000000000 0.0 00000000.00 00000000000000000000000000000000000000000000000000.0000000000 0000000000 0000000000 0 0000000000000000.0000 0000000000000000000000000000000000000000000000000000000000000.0000
0000000000 0000000000 0.0 00000000.00 00000000000000000000000000000000000000000000000000.0000000000 0000000000 0000000000 0 0000000000000000.0000 0000000000000000000000000000000000000000000000000000000000000.0000
0000000000 0000000000 0.0 00000000.00 00000000000000000000000000000000000000000000000000.0000000000 0000000000 0000000000 0 0000000000000000.0000 0000000000000000000000000000000000000000000000000000000000000.0000
0000000100 0000123456 0.3 00040000.25 00000000000000000000000000000000123456789123456789.1000100000 0000001024 0000007000 8 0000000000999999.9000 0000000000000000000000000000000000000000009223372036854775807.0000
9999999999 9999999999 0.9 99999999.99 99999999999999999999999999999999999999999999999999.9999999999 9999999999 9999999999 9 9999999999999999.9999 9999999999999999999999999999999999999999999999999999999999999.9999
SELECT d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4 FROM t1 WHERE n20_4 = 9999999999999999.9999 OR d < 100;
d d0 d1_1 d10_2 d60_10 n n0_0 n1 n20_4 n65_4
0000000000 0000000000 0.0 00000000.00 00000000000000000000000000000000000000000000000000.0000000000 0000000000 0000000000 0 0000000000000000.0000 0000000000000000000000000000000000000000000000000000000000000.0000
0000000000 0000000000 0.0 00000000.00 00000000000000000000000000000000000000000000000000.0000000000 0000000000 0000000000 0 0000000000000000.0000 0000000000000000000000000000000000000000000000000000000000000.0000
0000000000 0000000000 0.0 00000000.00 00000000000000000000000000000000000000000000000000.0000000000 0000000000 0000000000 0 0000000000000000.0000 0000000000000000000000000000000000000000000000000000000000000.0000
9999999999 9999999999 0.9 99999999.99 99999999999999999999999999999999999999999999999999.9999999999 9999999999 9999999999 9 9999999999999999.9999 9999999999999999999999999999999999999999999999999999999999999.9999
INSERT INTO t1 (d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4) SELECT n65_4, n65_4, n65_4, n65_4, n65_4, n65_4, n65_4, n65_4, n65_4, n65_4 FROM t1 WHERE n65_4 = ( SELECT MAX(n65_4) FROM t1 );
Warnings:
Warning 1264 Out of range value for column 'd' at row 1
Warning 1264 Out of range value for column 'd0' at row 1
Warning 1264 Out of range value for column 'd1_1' at row 1
Warning 1264 Out of range value for column 'd10_2' at row 1
Warning 1264 Out of range value for column 'd60_10' at row 1
Warning 1264 Out of range value for column 'n' at row 1
Warning 1264 Out of range value for column 'n0_0' at row 1
Warning 1264 Out of range value for column 'n1' at row 1
Warning 1264 Out of range value for column 'n20_4' at row 1
SELECT d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4 FROM t1;
d d0 d1_1 d10_2 d60_10 n n0_0 n1 n20_4 n65_4
0000000000 0000000000 0.0 00000000.00 00000000000000000000000000000000000000000000000000.0000000000 0000000000 0000000000 0 0000000000000000.0000 0000000000000000000000000000000000000000000000000000000000000.0000
0000000000 0000000000 0.0 00000000.00 00000000000000000000000000000000000000000000000000.0000000000 0000000000 0000000000 0 0000000000000000.0000 0000000000000000000000000000000000000000000000000000000000000.0000
0000000000 0000000000 0.0 00000000.00 00000000000000000000000000000000000000000000000000.0000000000 0000000000 0000000000 0 0000000000000000.0000 0000000000000000000000000000000000000000000000000000000000000.0000
0000000100 0000123456 0.3 00040000.25 00000000000000000000000000000000123456789123456789.1000100000 0000001024 0000007000 8 0000000000999999.9000 0000000000000000000000000000000000000000009223372036854775807.0000
9999999999 9999999999 0.9 99999999.99 99999999999999999999999999999999999999999999999999.9999999999 9999999999 9999999999 9 9999999999999999.9999 9999999999999999999999999999999999999999999999999999999999999.9999
9999999999 9999999999 0.9 99999999.99 99999999999999999999999999999999999999999999999999.9999999999 9999999999 9999999999 9 9999999999999999.9999 9999999999999999999999999999999999999999999999999999999999999.9999
INSERT INTO t1 (d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4) VALUES (10000000000.0,10000000000.0,1.1,100000000.99,100000000000000000000000000000000000000000000000000.0,10000000000.0,10000000000.0,10.0,10000000000000000.9999,10000000000000000000000000000000000000000000000000000000000000.9999);
Warnings:
Warning 1264 Out of range value for column 'd' at row 1
Warning 1264 Out of range value for column 'd0' at row 1
Warning 1264 Out of range value for column 'd1_1' at row 1
Warning 1264 Out of range value for column 'd10_2' at row 1
Warning 1264 Out of range value for column 'd60_10' at row 1
Warning 1264 Out of range value for column 'n' at row 1
Warning 1264 Out of range value for column 'n0_0' at row 1
Warning 1264 Out of range value for column 'n1' at row 1
Warning 1264 Out of range value for column 'n20_4' at row 1
Warning 1264 Out of range value for column 'n65_4' at row 1
SELECT d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4 FROM t1;
d d0 d1_1 d10_2 d60_10 n n0_0 n1 n20_4 n65_4
0000000000 0000000000 0.0 00000000.00 00000000000000000000000000000000000000000000000000.0000000000 0000000000 0000000000 0 0000000000000000.0000 0000000000000000000000000000000000000000000000000000000000000.0000
0000000000 0000000000 0.0 00000000.00 00000000000000000000000000000000000000000000000000.0000000000 0000000000 0000000000 0 0000000000000000.0000 0000000000000000000000000000000000000000000000000000000000000.0000
0000000000 0000000000 0.0 00000000.00 00000000000000000000000000000000000000000000000000.0000000000 0000000000 0000000000 0 0000000000000000.0000 0000000000000000000000000000000000000000000000000000000000000.0000
0000000100 0000123456 0.3 00040000.25 00000000000000000000000000000000123456789123456789.1000100000 0000001024 0000007000 8 0000000000999999.9000 0000000000000000000000000000000000000000009223372036854775807.0000
9999999999 9999999999 0.9 99999999.99 99999999999999999999999999999999999999999999999999.9999999999 9999999999 9999999999 9 9999999999999999.9999 9999999999999999999999999999999999999999999999999999999999999.9999
9999999999 9999999999 0.9 99999999.99 99999999999999999999999999999999999999999999999999.9999999999 9999999999 9999999999 9 9999999999999999.9999 9999999999999999999999999999999999999999999999999999999999999.9999
9999999999 9999999999 0.9 99999999.99 99999999999999999999999999999999999999999999999999.9999999999 9999999999 9999999999 9 9999999999999999.9999 9999999999999999999999999999999999999999999999999999999999999.9999
INSERT INTO t1 (d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4) VALUES (9999999999.1,9999999999.1,1.9,99999999.001,99999999999999999999999999999999999999999999999999.99999999991,9999999999.1,9999999999.1,9.1,9999999999999999.00001,9999999999999999999999999999999999999999999999999999999999999.11111);
Warnings:
Note 1265 Data truncated for column 'd' at row 1
Note 1265 Data truncated for column 'd0' at row 1
Warning 1264 Out of range value for column 'd1_1' at row 1
Note 1265 Data truncated for column 'd10_2' at row 1
Note 1265 Data truncated for column 'd60_10' at row 1
Note 1265 Data truncated for column 'n' at row 1
Note 1265 Data truncated for column 'n0_0' at row 1
Note 1265 Data truncated for column 'n1' at row 1
Note 1265 Data truncated for column 'n20_4' at row 1
Note 1265 Data truncated for column 'n65_4' at row 1
SELECT d,d0,d1_1,d10_2,d60_10,n,n0_0,n1,n20_4,n65_4 FROM t1;
d d0 d1_1 d10_2 d60_10 n n0_0 n1 n20_4 n65_4
0000000000 0000000000 0.0 00000000.00 00000000000000000000000000000000000000000000000000.0000000000 0000000000 0000000000 0 0000000000000000.0000 0000000000000000000000000000000000000000000000000000000000000.0000
0000000000 0000000000 0.0 00000000.00 00000000000000000000000000000000000000000000000000.0000000000 0000000000 0000000000 0 0000000000000000.0000 0000000000000000000000000000000000000000000000000000000000000.0000
0000000000 0000000000 0.0 00000000.00 00000000000000000000000000000000000000000000000000.0000000000 0000000000 0000000000 0 0000000000000000.0000 0000000000000000000000000000000000000000000000000000000000000.0000
0000000100 0000123456 0.3 00040000.25 00000000000000000000000000000000123456789123456789.1000100000 0000001024 0000007000 8 0000000000999999.9000 0000000000000000000000000000000000000000009223372036854775807.0000
9999999999 9999999999 0.9 99999999.00 99999999999999999999999999999999999999999999999999.9999999999 9999999999 9999999999 9 9999999999999999.0000 9999999999999999999999999999999999999999999999999999999999999.1111
9999999999 9999999999 0.9 99999999.99 99999999999999999999999999999999999999999999999999.9999999999 9999999999 9999999999 9 9999999999999999.9999 9999999999999999999999999999999999999999999999999999999999999.9999
9999999999 9999999999 0.9 99999999.99 99999999999999999999999999999999999999999999999999.9999999999 9999999999 9999999999 9 9999999999999999.9999 9999999999999999999999999999999999999999999999999999999999999.9999
9999999999 9999999999 0.9 99999999.99 99999999999999999999999999999999999999999999999999.9999999999 9999999999 9999999999 9 9999999999999999.9999 9999999999999999999999999999999999999999999999999999999999999.9999
ALTER TABLE t1 ADD COLUMN n66 NUMERIC(66);
ERROR 42000: Too big precision 66 specified for 'n66'. Maximum is 65.
ALTER TABLE t1 ADD COLUMN n66_6 DECIMAL(66,6);
ERROR 42000: Too big precision 66 specified for 'n66_6'. Maximum is 65.
ALTER TABLE t1 ADD COLUMN n66_66 DECIMAL(66,66);
ERROR 42000: Too big scale 66 specified for 'n66_66'. Maximum is 30.
DROP TABLE t1;
CREATE TABLE t1 (a DECIMAL ZEROFILL <CUSTOM_COL_OPTIONS>,
b NUMERIC ZEROFILL <CUSTOM_COL_OPTIONS>
) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
SHOW COLUMNS IN t1;
Field Type Null Key Default Extra
a decimal(10,0) unsigned zerofill # # # #
b decimal(10,0) unsigned zerofill # # # #
INSERT INTO t1 (a,b) VALUES (1.1,1234);
Warnings:
Note 1265 Data truncated for column 'a' at row 1
SELECT a,b FROM t1;
a b
0000000001 0000001234
DROP TABLE t1;
#
# Floating point columns (FLOAT, DOUBLE)
#
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (f FLOAT ZEROFILL <CUSTOM_COL_OPTIONS>,
f0 FLOAT(0) ZEROFILL <CUSTOM_COL_OPTIONS>,
r1_1 REAL(1,1) ZEROFILL <CUSTOM_COL_OPTIONS>,
f23_0 FLOAT(23) ZEROFILL <CUSTOM_COL_OPTIONS>,
f20_3 FLOAT(20,3) ZEROFILL <CUSTOM_COL_OPTIONS>,
d DOUBLE ZEROFILL <CUSTOM_COL_OPTIONS>,
d1_0 DOUBLE(1,0) ZEROFILL <CUSTOM_COL_OPTIONS>,
d10_10 DOUBLE PRECISION (10,10) ZEROFILL <CUSTOM_COL_OPTIONS>,
d53 DOUBLE(53,0) ZEROFILL <CUSTOM_COL_OPTIONS>,
d53_10 DOUBLE(53,10) ZEROFILL <CUSTOM_COL_OPTIONS>
) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
SHOW COLUMNS IN t1;
Field Type Null Key Default Extra
f float unsigned zerofill # # #
f0 float unsigned zerofill # # #
r1_1 double(1,1) unsigned zerofill # # #
f23_0 float unsigned zerofill # # #
f20_3 float(20,3) unsigned zerofill # # #
d double unsigned zerofill # # #
d1_0 double(1,0) unsigned zerofill # # #
d10_10 double(10,10) unsigned zerofill # # #
d53 double(53,0) unsigned zerofill # # #
d53_10 double(53,10) unsigned zerofill # # #
INSERT INTO t1 (f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10) VALUES (12345.12345,12345.12345,0.9,123456789.123,56789.987,11111111.111,8.0,0.0123456789,1234566789123456789,99999999999999999.99999999);
SELECT f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10 FROM t1;
f 0000012345.1
d 000000000011111111.111
d10_10 0.0123456789
d1_0 8
d53 00000000000000000000000000000000001234566789123456800
d53_10 000000000000000000000000100000000000000000.0000000000
f0 0000012345.1
f20_3 0000000000056789.988
f23_0 000123457000
r1_1 0.9
INSERT INTO t1 (f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10) VALUES (0,0,0,0,0,0,0,0,0,0);
INSERT INTO t1 (f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10) VALUES (
99999999999999999999999999999999999999,
99999999999999999999999999999999999999.9999999999999999,
0.9,
99999999999999999999999999999999999999.9,
99999999999999999.999,
999999999999999999999999999999999999999999999999999999999999999999999999999999999,
9,
0.9999999999,
1999999999999999999999999999999999999999999999999999999,
19999999999999999999999999999999999999999999.9999999999
);
Warnings:
Warning 1264 Out of range value for column 'd53' at row 1
Warning 1264 Out of range value for column 'd53_10' at row 1
SELECT f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10 FROM t1;
f 0000012345.1
d 0000000000000000000000
d 0000000000000000001e81
d 000000000011111111.111
d10_10 0.0000000000
d10_10 0.0123456789
d10_10 0.9999999999
d1_0 0
d1_0 8
d1_0 9
d53 00000000000000000000000000000000000000000000000000000
d53 00000000000000000000000000000000001234566789123456800
d53 100000000000000000000000000000000000000000000000000000
d53_10 000000000000000000000000000000000000000000.0000000000
d53_10 000000000000000000000000100000000000000000.0000000000
d53_10 10000000000000000000000000000000000000000000.0000000000
f 000000000000
f 000000001e38
f0 000000000000
f0 000000001e38
f0 0000012345.1
f20_3 0000000000000000.000
f20_3 0000000000056789.988
f20_3 99999998430674940.000
f23_0 000000000000
f23_0 000000001e38
f23_0 000123457000
r1_1 0.0
r1_1 0.9
r1_1 0.9
INSERT INTO t1 (f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10) VALUES (-999999999999999999999999,-99999999999.999999999999,-0.9,-999.99999999999999999999,-99999999999999999.999,-999999999999999999999999999999999999999999999999999999999999-0.999,-9,-.9999999999,-999999999999999999999999999999.99999999999999999999999,-9999999999999999999999999999999999999999999.9999999999);
Warnings:
Warning 1264 Out of range value for column 'f' at row 1
Warning 1264 Out of range value for column 'f0' at row 1
Warning 1264 Out of range value for column 'r1_1' at row 1
Warning 1264 Out of range value for column 'f23_0' at row 1
Warning 1264 Out of range value for column 'f20_3' at row 1
Warning 1264 Out of range value for column 'd' at row 1
Warning 1264 Out of range value for column 'd1_0' at row 1
Warning 1264 Out of range value for column 'd10_10' at row 1
Warning 1264 Out of range value for column 'd53' at row 1
Warning 1264 Out of range value for column 'd53_10' at row 1
SELECT f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10 FROM t1;
f 0000012345.1
d 0000000000000000000000
d 0000000000000000000000
d 0000000000000000001e81
d 000000000011111111.111
d10_10 0.0000000000
d10_10 0.0000000000
d10_10 0.0123456789
d10_10 0.9999999999
d1_0 0
d1_0 0
d1_0 8
d1_0 9
d53 00000000000000000000000000000000000000000000000000000
d53 00000000000000000000000000000000000000000000000000000
d53 00000000000000000000000000000000001234566789123456800
d53 100000000000000000000000000000000000000000000000000000
d53_10 000000000000000000000000000000000000000000.0000000000
d53_10 000000000000000000000000000000000000000000.0000000000
d53_10 000000000000000000000000100000000000000000.0000000000
d53_10 10000000000000000000000000000000000000000000.0000000000
f 000000000000
f 000000000000
f 000000001e38
f0 000000000000
f0 000000000000
f0 000000001e38
f0 0000012345.1
f20_3 0000000000000000.000
f20_3 0000000000000000.000
f20_3 0000000000056789.988
f20_3 99999998430674940.000
f23_0 000000000000
f23_0 000000000000
f23_0 000000001e38
f23_0 000123457000
r1_1 0.0
r1_1 0.0
r1_1 0.9
r1_1 0.9
SELECT MAX(f), MAX(f0), MAX(r1_1), MAX(f23_0), MAX(f20_3), MAX(d), MAX(d1_0), MAX(d10_10), MAX(d53), MAX(d53_10) FROM t1;
MAX(f) 9.999999680285692e37
MAX(d) 1e81
MAX(d10_10) 0.9999999999
MAX(d1_0) 9
MAX(d53) 100000000000000000000000000000000000000000000000000000
MAX(d53_10) 10000000000000000000000000000000000000000000.0000000000
MAX(f0) 9.999999680285692e37
MAX(f20_3) 99999998430674940.000
MAX(f23_0) 9.999999680285692e37
MAX(r1_1) 0.9
INSERT INTO t1 (f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10) SELECT d53_10, d53_10, d53_10, d53_10, d53_10, d53_10, d53_10, d53_10, d53_10, d53_10 FROM t1 ORDER BY d53_10 DESC LIMIT 1;
Warnings:
Warning 1264 Out of range value for column 'f' at row 1
Warning 1264 Out of range value for column 'f0' at row 1
Warning 1264 Out of range value for column 'r1_1' at row 1
Warning 1264 Out of range value for column 'f23_0' at row 1
Warning 1264 Out of range value for column 'f20_3' at row 1
Warning 1264 Out of range value for column 'd1_0' at row 1
SELECT f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10 FROM t1;
f 0000012345.1
d 0000000000000000000000
d 0000000000000000000000
d 0000000000000000001e43
d 0000000000000000001e81
d 000000000011111111.111
d10_10 0.0000000000
d10_10 0.0000000000
d10_10 0.0123456789
d10_10 0.9999999999
d10_10 10000000000000000000000000000000000000000000.0000000000
d1_0 0
d1_0 0
d1_0 8
d1_0 9
d1_0 9
d53 00000000000000000000000000000000000000000000000000000
d53 00000000000000000000000000000000000000000000000000000
d53 00000000000000000000000000000000001234566789123456800
d53 00000000010000000000000000000000000000000000000000000
d53 100000000000000000000000000000000000000000000000000000
d53_10 000000000000000000000000000000000000000000.0000000000
d53_10 000000000000000000000000000000000000000000.0000000000
d53_10 000000000000000000000000100000000000000000.0000000000
d53_10 10000000000000000000000000000000000000000000.0000000000
d53_10 10000000000000000000000000000000000000000000.0000000000
f 000000000000
f 000000000000
f 000000001e38
f 003.40282e38
f0 000000000000
f0 000000000000
f0 000000001e38
f0 0000012345.1
f0 003.40282e38
f20_3 0000000000000000.000
f20_3 0000000000000000.000
f20_3 0000000000056789.988
f20_3 99999998430674940.000
f20_3 99999998430674940.000
f23_0 000000000000
f23_0 000000000000
f23_0 000000001e38
f23_0 000123457000
f23_0 003.40282e38
r1_1 0.0
r1_1 0.0
r1_1 0.9
r1_1 0.9
r1_1 0.9
INSERT INTO t1 (f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10) VALUES (
999999999999999999999999999999999999999,
999999999999999999999999999999999999999.9999999999999999,
1.9,
999999999999999999999999999999999999999.9,
999999999999999999.999,
9999999999999999999999999999999999999999999999999999999999999999999999999999999999,
99,
1.9999999999,
1999999999999999999999999999999999999999999999999999999,
19999999999999999999999999999999999999999999.9999999999
);
Warnings:
Warning 1916 Got overflow when converting '' to DECIMAL. Value truncated
Warning 1264 Out of range value for column 'f' at row 1
Warning 1264 Out of range value for column 'f0' at row 1
Warning 1264 Out of range value for column 'r1_1' at row 1
Warning 1264 Out of range value for column 'f23_0' at row 1
Warning 1264 Out of range value for column 'f20_3' at row 1
Warning 1264 Out of range value for column 'd1_0' at row 1
Warning 1264 Out of range value for column 'd10_10' at row 1
Warning 1264 Out of range value for column 'd53' at row 1
Warning 1264 Out of range value for column 'd53_10' at row 1
SELECT f,f0,r1_1,f23_0,f20_3,d,d1_0,d10_10,d53,d53_10 FROM t1;
f 0000012345.1
d 0000000000000000000000
d 0000000000000000000000
d 0000000000000000001e43
d 0000000000000000001e65
d 0000000000000000001e81
d 000000000011111111.111
d10_10 0.0000000000
d10_10 0.0000000000
d10_10 0.0123456789
d10_10 0.9999999999
d10_10 0.9999999999
d10_10 10000000000000000000000000000000000000000000.0000000000
d1_0 0
d1_0 0
d1_0 8
d1_0 9
d1_0 9
d1_0 9
d53 00000000000000000000000000000000000000000000000000000
d53 00000000000000000000000000000000000000000000000000000
d53 00000000000000000000000000000000001234566789123456800
d53 00000000010000000000000000000000000000000000000000000
d53 100000000000000000000000000000000000000000000000000000
d53 100000000000000000000000000000000000000000000000000000
d53_10 000000000000000000000000000000000000000000.0000000000
d53_10 000000000000000000000000000000000000000000.0000000000
d53_10 000000000000000000000000100000000000000000.0000000000
d53_10 10000000000000000000000000000000000000000000.0000000000
d53_10 10000000000000000000000000000000000000000000.0000000000
d53_10 10000000000000000000000000000000000000000000.0000000000
f 000000000000
f 000000000000
f 000000001e38
f 003.40282e38
f 003.40282e38
f0 000000000000
f0 000000000000
f0 000000001e38
f0 0000012345.1
f0 003.40282e38
f0 003.40282e38
f20_3 0000000000000000.000
f20_3 0000000000000000.000
f20_3 0000000000056789.988
f20_3 99999998430674940.000
f20_3 99999998430674940.000
f20_3 99999998430674940.000
f23_0 000000000000
f23_0 000000000000
f23_0 000000001e38
f23_0 000123457000
f23_0 003.40282e38
f23_0 003.40282e38
r1_1 0.0
r1_1 0.0
r1_1 0.9
r1_1 0.9
r1_1 0.9
r1_1 0.9
ALTER TABLE t1 ADD COLUMN d0_0 DOUBLE(0,0);
# ERROR: Statement succeeded (expected results: ER_TOO_BIG_DISPLAYWIDTH)
# ------------ UNEXPECTED RESULT ------------
# [ ALTER TABLE t1 ADD COLUMN d0_0 DOUBLE(0,0) ]
# The statement|command succeeded unexpectedly.
# ALTER TABLE or the mix could be unsupported|malfunctioning, or the problem was caused by previous errors.
# You can change the engine code, or create an rdiff, or disable the test by adding it to disabled.def.
# Further in this test, the message might sometimes be suppressed; a part of the test might be skipped.
# Also, this problem may cause a chain effect (more errors of different kinds in the test).
# -------------------------------------------
ALTER TABLE t1 ADD COLUMN n66_6 DECIMAL(256,1);
ERROR 42000: Too big precision 256 specified for 'n66_6'. Maximum is 65.
ALTER TABLE t1 ADD COLUMN n66_66 DECIMAL(40,35);
ERROR 42000: Too big scale 35 specified for 'n66_66'. Maximum is 30.
DROP TABLE t1;
CREATE TABLE t1 (a DOUBLE ZEROFILL <CUSTOM_COL_OPTIONS>,
b FLOAT ZEROFILL <CUSTOM_COL_OPTIONS>
) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
SHOW COLUMNS IN t1;
Field Type Null Key Default Extra
a double unsigned zerofill # # # #
b float unsigned zerofill # # # #
INSERT INTO t1 (a,b) VALUES (1,1234.5);
SELECT a,b FROM t1;
a b
0000000000000000000001 0000001234.5
DROP TABLE t1;
#
# INT columns
#
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (i INT ZEROFILL <CUSTOM_COL_OPTIONS>,
i0 INT(0) ZEROFILL <CUSTOM_COL_OPTIONS>,
i1 INT(1) ZEROFILL <CUSTOM_COL_OPTIONS>,
i20 INT(20) ZEROFILL <CUSTOM_COL_OPTIONS>,
t TINYINT ZEROFILL <CUSTOM_COL_OPTIONS>,
t0 TINYINT(0) ZEROFILL <CUSTOM_COL_OPTIONS>,
t1 TINYINT(1) ZEROFILL <CUSTOM_COL_OPTIONS>,
t20 TINYINT(20) ZEROFILL <CUSTOM_COL_OPTIONS>,
s SMALLINT ZEROFILL <CUSTOM_COL_OPTIONS>,
s0 SMALLINT(0) ZEROFILL <CUSTOM_COL_OPTIONS>,
s1 SMALLINT(1) ZEROFILL <CUSTOM_COL_OPTIONS>,
s20 SMALLINT(20) ZEROFILL <CUSTOM_COL_OPTIONS>,
m MEDIUMINT ZEROFILL <CUSTOM_COL_OPTIONS>,
m0 MEDIUMINT(0) ZEROFILL <CUSTOM_COL_OPTIONS>,
m1 MEDIUMINT(1) ZEROFILL <CUSTOM_COL_OPTIONS>,
m20 MEDIUMINT(20) ZEROFILL <CUSTOM_COL_OPTIONS>,
b BIGINT ZEROFILL <CUSTOM_COL_OPTIONS>,
b0 BIGINT(0) ZEROFILL <CUSTOM_COL_OPTIONS>,
b1 BIGINT(1) ZEROFILL <CUSTOM_COL_OPTIONS>,
b20 BIGINT(20) ZEROFILL <CUSTOM_COL_OPTIONS>
) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
SHOW COLUMNS IN t1;
Field Type Null Key Default Extra
i int(10) unsigned zerofill # # #
i0 int(10) unsigned zerofill # # #
i1 int(1) unsigned zerofill # # #
i20 int(20) unsigned zerofill # # #
t tinyint(3) unsigned zerofill # # #
t0 tinyint(3) unsigned zerofill # # #
t1 tinyint(1) unsigned zerofill # # #
t20 tinyint(20) unsigned zerofill # # #
s smallint(5) unsigned zerofill # # #
s0 smallint(5) unsigned zerofill # # #
s1 smallint(1) unsigned zerofill # # #
s20 smallint(20) unsigned zerofill # # #
m mediumint(8) unsigned zerofill # # #
m0 mediumint(8) unsigned zerofill # # #
m1 mediumint(1) unsigned zerofill # # #
m20 mediumint(20) unsigned zerofill # # #
b bigint(20) unsigned zerofill # # #
b0 bigint(20) unsigned zerofill # # #
b1 bigint(1) unsigned zerofill # # #
b20 bigint(20) unsigned zerofill # # #
INSERT INTO t1 (i,i0,i1,i20,t,t0,t1,t20,s,s0,s1,s20,m,m0,m1,m20,b,b0,b1,b20) VALUES (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20);
INSERT INTO t1 (i,i0,i1,i20,t,t0,t1,t20,s,s0,s1,s20,m,m0,m1,m20,b,b0,b1,b20) VALUES (0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0);
INSERT INTO t1 (i,i0,i1,i20,t,t0,t1,t20,s,s0,s1,s20,m,m0,m1,m20,b,b0,b1,b20) VALUES (2147483647,2147483647,2147483647,2147483647,127,127,127,127,32767,32767,32767,32767,8388607,8388607,8388607,8388607,9223372036854775807,9223372036854775807,9223372036854775807,9223372036854775807);
SELECT i,i0,i1,i20,t,t0,t1,t20,s,s0,s1,s20,m,m0,m1,m20,b,b0,b1,b20 FROM t1;
i i0 i1 i20 t t0 t1 t20 s s0 s1 s20 m m0 m1 m20 b b0 b1 b20
0000000000 0000000000 0 00000000000000000000 000 000 0 00000000000000000000 00000 00000 0 00000000000000000000 00000000 00000000 0 00000000000000000000 00000000000000000000 00000000000000000000 0 00000000000000000000
0000000001 0000000002 3 00000000000000000004 005 006 7 00000000000000000008 00009 00010 11 00000000000000000012 00000013 00000014 15 00000000000000000016 00000000000000000017 00000000000000000018 19 00000000000000000020
2147483647 2147483647 2147483647 00000000002147483647 127 127 127 00000000000000000127 32767 32767 32767 00000000000000032767 08388607 08388607 8388607 00000000000008388607 09223372036854775807 09223372036854775807 9223372036854775807 09223372036854775807
INSERT INTO t1 (i,i0,i1,i20,t,t0,t1,t20,s,s0,s1,s20,m,m0,m1,m20,b,b0,b1,b20) VALUES (-2147483648,-2147483648,-2147483648,-2147483648,-128,-128,-128,-128,-32768,-32768,-32768,-32768,-8388608,-8388608,-8388608,-8388608,-9223372036854775808,-9223372036854775808,-9223372036854775808,-9223372036854775808);
Warnings:
Warning 1264 Out of range value for column 'i' at row 1
Warning 1264 Out of range value for column 'i0' at row 1
Warning 1264 Out of range value for column 'i1' at row 1
Warning 1264 Out of range value for column 'i20' at row 1
Warning 1264 Out of range value for column 't' at row 1
Warning 1264 Out of range value for column 't0' at row 1
Warning 1264 Out of range value for column 't1' at row 1
Warning 1264 Out of range value for column 't20' at row 1
Warning 1264 Out of range value for column 's' at row 1
Warning 1264 Out of range value for column 's0' at row 1
Warning 1264 Out of range value for column 's1' at row 1
Warning 1264 Out of range value for column 's20' at row 1
Warning 1264 Out of range value for column 'm' at row 1
Warning 1264 Out of range value for column 'm0' at row 1
Warning 1264 Out of range value for column 'm1' at row 1
Warning 1264 Out of range value for column 'm20' at row 1
Warning 1264 Out of range value for column 'b' at row 1
Warning 1264 Out of range value for column 'b0' at row 1
Warning 1264 Out of range value for column 'b1' at row 1
Warning 1264 Out of range value for column 'b20' at row 1
INSERT INTO t1 (i,i0,i1,i20,t,t0,t1,t20,s,s0,s1,s20,m,m0,m1,m20,b,b0,b1,b20) VALUES (4294967295,4294967295,4294967295,4294967295,255,255,255,255,65535,65535,65535,65535,16777215,16777215,16777215,16777215,18446744073709551615,18446744073709551615,18446744073709551615,18446744073709551615);
SELECT i,i0,i1,i20,t,t0,t1,t20,s,s0,s1,s20,m,m0,m1,m20,b,b0,b1,b20 FROM t1;
i i0 i1 i20 t t0 t1 t20 s s0 s1 s20 m m0 m1 m20 b b0 b1 b20
0000000000 0000000000 0 00000000000000000000 000 000 0 00000000000000000000 00000 00000 0 00000000000000000000 00000000 00000000 0 00000000000000000000 00000000000000000000 00000000000000000000 0 00000000000000000000
0000000000 0000000000 0 00000000000000000000 000 000 0 00000000000000000000 00000 00000 0 00000000000000000000 00000000 00000000 0 00000000000000000000 00000000000000000000 00000000000000000000 0 00000000000000000000
0000000001 0000000002 3 00000000000000000004 005 006 7 00000000000000000008 00009 00010 11 00000000000000000012 00000013 00000014 15 00000000000000000016 00000000000000000017 00000000000000000018 19 00000000000000000020
2147483647 2147483647 2147483647 00000000002147483647 127 127 127 00000000000000000127 32767 32767 32767 00000000000000032767 08388607 08388607 8388607 00000000000008388607 09223372036854775807 09223372036854775807 9223372036854775807 09223372036854775807
4294967295 4294967295 4294967295 00000000004294967295 255 255 255 00000000000000000255 65535 65535 65535 00000000000000065535 16777215 16777215 16777215 00000000000016777215 18446744073709551615 18446744073709551615 18446744073709551615 18446744073709551615
INSERT INTO t1 (i,i0,i1,i20,t,t0,t1,t20,s,s0,s1,s20,m,m0,m1,m20,b,b0,b1,b20) VALUES (-2147483649,-2147483649,-2147483649,-2147483649,-129,-129,-129,-129,-32769,-32769,-32769,-32769,-8388609,-8388609,-8388609,-8388609,-9223372036854775809,-9223372036854775809,-9223372036854775809,-9223372036854775809);
Warnings:
Warning 1264 Out of range value for column 'i' at row 1
Warning 1264 Out of range value for column 'i0' at row 1
Warning 1264 Out of range value for column 'i1' at row 1
Warning 1264 Out of range value for column 'i20' at row 1
Warning 1264 Out of range value for column 't' at row 1
Warning 1264 Out of range value for column 't0' at row 1
Warning 1264 Out of range value for column 't1' at row 1
Warning 1264 Out of range value for column 't20' at row 1
Warning 1264 Out of range value for column 's' at row 1
Warning 1264 Out of range value for column 's0' at row 1
Warning 1264 Out of range value for column 's1' at row 1
Warning 1264 Out of range value for column 's20' at row 1
Warning 1264 Out of range value for column 'm' at row 1
Warning 1264 Out of range value for column 'm0' at row 1
Warning 1264 Out of range value for column 'm1' at row 1
Warning 1264 Out of range value for column 'm20' at row 1
Warning 1264 Out of range value for column 'b' at row 1
Warning 1264 Out of range value for column 'b0' at row 1
Warning 1264 Out of range value for column 'b1' at row 1
Warning 1264 Out of range value for column 'b20' at row 1
INSERT INTO t1 (i,i0,i1,i20,t,t0,t1,t20,s,s0,s1,s20,m,m0,m1,m20,b,b0,b1,b20) VALUES (4294967296,4294967296,4294967296,4294967296,256,256,256,256,65536,65536,65536,65536,16777216,16777216,16777216,16777216,18446744073709551616,18446744073709551616,18446744073709551616,18446744073709551616);
Warnings:
Warning 1264 Out of range value for column 'i' at row 1
Warning 1264 Out of range value for column 'i0' at row 1
Warning 1264 Out of range value for column 'i1' at row 1
Warning 1264 Out of range value for column 'i20' at row 1
Warning 1264 Out of range value for column 't' at row 1
Warning 1264 Out of range value for column 't0' at row 1
Warning 1264 Out of range value for column 't1' at row 1
Warning 1264 Out of range value for column 't20' at row 1
Warning 1264 Out of range value for column 's' at row 1
Warning 1264 Out of range value for column 's0' at row 1
Warning 1264 Out of range value for column 's1' at row 1
Warning 1264 Out of range value for column 's20' at row 1
Warning 1264 Out of range value for column 'm' at row 1
Warning 1264 Out of range value for column 'm0' at row 1
Warning 1264 Out of range value for column 'm1' at row 1
Warning 1264 Out of range value for column 'm20' at row 1
Warning 1264 Out of range value for column 'b' at row 1
Warning 1264 Out of range value for column 'b0' at row 1
Warning 1264 Out of range value for column 'b1' at row 1
Warning 1264 Out of range value for column 'b20' at row 1
INSERT INTO t1 (i,i0,i1,i20,t,t0,t1,t20,s,s0,s1,s20,m,m0,m1,m20,b,b0,b1,b20) SELECT b,b,b,b,b,b,b,b,b,b,b,b,b,b,b,b,b,b,b,b FROM t1 WHERE b IN (-9223372036854775808,9223372036854775807,18446744073709551615);
Warnings:
Warning 1264 Out of range value for column 'i' at row 1
Warning 1264 Out of range value for column 'i0' at row 1
Warning 1264 Out of range value for column 'i1' at row 1
Warning 1264 Out of range value for column 'i20' at row 1
Warning 1264 Out of range value for column 't' at row 1
Warning 1264 Out of range value for column 't0' at row 1
Warning 1264 Out of range value for column 't1' at row 1
Warning 1264 Out of range value for column 't20' at row 1
Warning 1264 Out of range value for column 's' at row 1
Warning 1264 Out of range value for column 's0' at row 1
Warning 1264 Out of range value for column 's1' at row 1
Warning 1264 Out of range value for column 's20' at row 1
Warning 1264 Out of range value for column 'm' at row 1
Warning 1264 Out of range value for column 'm0' at row 1
Warning 1264 Out of range value for column 'm1' at row 1
Warning 1264 Out of range value for column 'm20' at row 1
Warning 1264 Out of range value for column 'i' at row 2
Warning 1264 Out of range value for column 'i0' at row 2
Warning 1264 Out of range value for column 'i1' at row 2
Warning 1264 Out of range value for column 'i20' at row 2
Warning 1264 Out of range value for column 't' at row 2
Warning 1264 Out of range value for column 't0' at row 2
Warning 1264 Out of range value for column 't1' at row 2
Warning 1264 Out of range value for column 't20' at row 2
Warning 1264 Out of range value for column 's' at row 2
Warning 1264 Out of range value for column 's0' at row 2
Warning 1264 Out of range value for column 's1' at row 2
Warning 1264 Out of range value for column 's20' at row 2
Warning 1264 Out of range value for column 'm' at row 2
Warning 1264 Out of range value for column 'm0' at row 2
Warning 1264 Out of range value for column 'm1' at row 2
Warning 1264 Out of range value for column 'm20' at row 2
Warning 1264 Out of range value for column 'i' at row 3
Warning 1264 Out of range value for column 'i0' at row 3
Warning 1264 Out of range value for column 'i1' at row 3
Warning 1264 Out of range value for column 'i20' at row 3
Warning 1264 Out of range value for column 't' at row 3
Warning 1264 Out of range value for column 't0' at row 3
Warning 1264 Out of range value for column 't1' at row 3
Warning 1264 Out of range value for column 't20' at row 3
Warning 1264 Out of range value for column 's' at row 3
Warning 1264 Out of range value for column 's0' at row 3
Warning 1264 Out of range value for column 's1' at row 3
Warning 1264 Out of range value for column 's20' at row 3
Warning 1264 Out of range value for column 'm' at row 3
Warning 1264 Out of range value for column 'm0' at row 3
Warning 1264 Out of range value for column 'm1' at row 3
Warning 1264 Out of range value for column 'm20' at row 3
SELECT i,i0,i1,i20,t,t0,t1,t20,s,s0,s1,s20,m,m0,m1,m20,b,b0,b1,b20 FROM t1;
i i0 i1 i20 t t0 t1 t20 s s0 s1 s20 m m0 m1 m20 b b0 b1 b20
0000000000 0000000000 0 00000000000000000000 000 000 0 00000000000000000000 00000 00000 0 00000000000000000000 00000000 00000000 0 00000000000000000000 00000000000000000000 00000000000000000000 0 00000000000000000000
0000000000 0000000000 0 00000000000000000000 000 000 0 00000000000000000000 00000 00000 0 00000000000000000000 00000000 00000000 0 00000000000000000000 00000000000000000000 00000000000000000000 0 00000000000000000000
0000000000 0000000000 0 00000000000000000000 000 000 0 00000000000000000000 00000 00000 0 00000000000000000000 00000000 00000000 0 00000000000000000000 00000000000000000000 00000000000000000000 0 00000000000000000000
0000000001 0000000002 3 00000000000000000004 005 006 7 00000000000000000008 00009 00010 11 00000000000000000012 00000013 00000014 15 00000000000000000016 00000000000000000017 00000000000000000018 19 00000000000000000020
2147483647 2147483647 2147483647 00000000002147483647 127 127 127 00000000000000000127 32767 32767 32767 00000000000000032767 08388607 08388607 8388607 00000000000008388607 09223372036854775807 09223372036854775807 9223372036854775807 09223372036854775807
4294967295 4294967295 4294967295 00000000004294967295 255 255 255 00000000000000000255 65535 65535 65535 00000000000000065535 16777215 16777215 16777215 00000000000016777215 09223372036854775807 09223372036854775807 9223372036854775807 09223372036854775807
4294967295 4294967295 4294967295 00000000004294967295 255 255 255 00000000000000000255 65535 65535 65535 00000000000000065535 16777215 16777215 16777215 00000000000016777215 18446744073709551615 18446744073709551615 18446744073709551615 18446744073709551615
4294967295 4294967295 4294967295 00000000004294967295 255 255 255 00000000000000000255 65535 65535 65535 00000000000000065535 16777215 16777215 16777215 00000000000016777215 18446744073709551615 18446744073709551615 18446744073709551615 18446744073709551615
4294967295 4294967295 4294967295 00000000004294967295 255 255 255 00000000000000000255 65535 65535 65535 00000000000000065535 16777215 16777215 16777215 00000000000016777215 18446744073709551615 18446744073709551615 18446744073709551615 18446744073709551615
4294967295 4294967295 4294967295 00000000004294967295 255 255 255 00000000000000000255 65535 65535 65535 00000000000000065535 16777215 16777215 16777215 00000000000016777215 18446744073709551615 18446744073709551615 18446744073709551615 18446744073709551615
ALTER TABLE t1 ADD COLUMN i257 INT(257);
ERROR 42000: Display width out of range for 'i257' (max = 255)
DROP TABLE t1;
CREATE TABLE t1 (t TINYINT ZEROFILL <CUSTOM_COL_OPTIONS>,
s SMALLINT ZEROFILL <CUSTOM_COL_OPTIONS>,
m MEDIUMINT ZEROFILL <CUSTOM_COL_OPTIONS>,
i INT ZEROFILL <CUSTOM_COL_OPTIONS>,
b BIGINT ZEROFILL <CUSTOM_COL_OPTIONS>
) ENGINE=<STORAGE_ENGINE> <CUSTOM_TABLE_OPTIONS>;
SHOW COLUMNS IN t1;
Field Type Null Key Default Extra
t tinyint(3) unsigned zerofill # # # #
s smallint(5) unsigned zerofill # # # #
m mediumint(8) unsigned zerofill # # # #
i int(10) unsigned zerofill # # # #
b bigint(20) unsigned zerofill # # # #
INSERT INTO t1 (t,s,m,i,b) VALUES (1,10,100,1000,0);
SELECT t,s,m,i,b FROM t1;
t s m i b
001 00010 00000100 0000001000 00000000000000000000
DROP TABLE t1;
|
