bigint.result 4.91 KB
Newer Older
1
drop table if exists t1;
2
select 0,256,00000000000000065536,2147483647,-2147483648,2147483648,+4294967296;
3
0	256	00000000000000065536	2147483647	-2147483648	2147483648	4294967296
4
0	256	65536	2147483647	-2147483648	2147483648	4294967296
5
select 9223372036854775807,-009223372036854775808;
6 7
9223372036854775807	-009223372036854775808
9223372036854775807	-9223372036854775808
8
select +9999999999999999999,-9999999999999999999;
9
9999999999999999999	-9999999999999999999
10
9999999999999999999	-9999999999999999999
11 12 13
select cast(9223372036854775808 as unsigned)+1;
cast(9223372036854775808 as unsigned)+1
9223372036854775809
14 15
select 9223372036854775808+1;
9223372036854775808+1
16
9223372036854775809
17 18
select -(0-3),round(-(0-3)), round(9999999999999999999);
-(0-3)	round(-(0-3))	round(9999999999999999999)
19
3	3	9999999999999999999
20
create table t1 (a bigint unsigned not null, primary key(a));
21
insert into t1 values (18446744073709551615), (0xFFFFFFFFFFFFFFFE), (18446744073709551613), (18446744073709551612);
22
select * from t1;
23
a
24 25
18446744073709551612
18446744073709551613
26
18446744073709551614
27
18446744073709551615
28
select * from t1 where a=18446744073709551615;
29 30
a
18446744073709551615
31 32
delete from t1 where a=18446744073709551615;
select * from t1;
33
a
34 35
18446744073709551612
18446744073709551613
36
18446744073709551614
37
drop table t1;
sasha@mysql.sashanet.com's avatar
sasha@mysql.sashanet.com committed
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
create table t1 ( a int not null default 1, big bigint );
insert into t1 (big) values (-1),(12345678901234567),(9223372036854775807),(18446744073709551615);
select min(big),max(big),max(big)-1 from t1;
min(big)	max(big)	max(big)-1
-1	9223372036854775807	9223372036854775806
select min(big),max(big),max(big)-1 from t1 group by a;
min(big)	max(big)	max(big)-1
-1	9223372036854775807	9223372036854775806
alter table t1 modify big bigint unsigned not null;
select min(big),max(big),max(big)-1 from t1;
min(big)	max(big)	max(big)-1
12345678901234567	18446744073709551615	18446744073709551614
select min(big),max(big),max(big)-1 from t1 group by a;
min(big)	max(big)	max(big)-1
12345678901234567	18446744073709551615	18446744073709551614
alter table t1 add key (big);
select min(big),max(big),max(big)-1 from t1;
min(big)	max(big)	max(big)-1
12345678901234567	18446744073709551615	18446744073709551614
select min(big),max(big),max(big)-1 from t1 group by a;
min(big)	max(big)	max(big)-1
12345678901234567	18446744073709551615	18446744073709551614
alter table t1 modify big bigint not null;
select min(big),max(big),max(big)-1 from t1;
min(big)	max(big)	max(big)-1
-1	9223372036854775807	9223372036854775806
select min(big),max(big),max(big)-1 from t1 group by a;
min(big)	max(big)	max(big)-1
-1	9223372036854775807	9223372036854775806
drop table t1;
68 69 70 71 72 73 74 75 76
create table t1 (id bigint auto_increment primary key, a int) auto_increment=9999999999;
insert into t1 values (null,1);
select * from t1;
id	a
9999999999	1
select * from t1 limit 9999999999;
id	a
9999999999	1
drop table t1;
77 78
CREATE TABLE t1 ( quantity decimal(60,0));
insert into t1 values (10000000000000000000);
79
insert into t1 values (10000000000000000000.0);
80 81 82
insert into t1 values ('10000000000000000000');
select * from t1;
quantity
83
-8446744073709551616
84 85 86
10000000000000000000
10000000000000000000
drop table t1;
87 88 89
SELECT '0x8000000000000001'+0;
'0x8000000000000001'+0
0
90 91
Warnings:
Warning	1292	Truncated incorrect DOUBLE value: '0x8000000000000001'
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
create table t1 (
value64  bigint unsigned  not null,
value32  integer          not null,
primary key(value64, value32)
);
create table t2 (
value64  bigint unsigned  not null,
value32  integer          not null,
primary key(value64, value32)
);
insert into t1 values(17156792991891826145, 1);
insert into t1 values( 9223372036854775807, 2);
insert into t2 values(17156792991891826145, 3);
insert into t2 values( 9223372036854775807, 4);
select * from t1;
value64	value32
9223372036854775807	2
17156792991891826145	1
select * from t2;
value64	value32
9223372036854775807	4
17156792991891826145	3
select * from t1, t2 where t1.value64=17156792991891826145 and
t2.value64=17156792991891826145;
value64	value32	value64	value32
17156792991891826145	1	17156792991891826145	3
select * from t1, t2 where t1.value64=17156792991891826145 and
t2.value64=t1.value64;
value64	value32	value64	value32
17156792991891826145	1	17156792991891826145	3
select * from t1, t2 where t1.value64= 9223372036854775807 and
t2.value64=9223372036854775807;
value64	value32	value64	value32
9223372036854775807	2	9223372036854775807	4
select * from t1, t2 where t1.value64= 9223372036854775807 and
t2.value64=t1.value64;
value64	value32	value64	value32
9223372036854775807	2	9223372036854775807	4
drop table t1, t2;
131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147
create table t1 select 1 as 'a';
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` bigint(1) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1 select 9223372036854775809 as 'a';
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` bigint(19) unsigned NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1
select * from t1;
a
9223372036854775809
drop table t1;