type_newdecimal.result

这是linux下运行的mysql软件包,可用于linux 下安装 php + mysql + apach 的网络配置

drop table t1;
create table t1 (sl decimal(65, 30));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `sl` decimal(65,30) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
create table t1 (
f1 decimal unsigned not null default 17.49, 
f2 decimal unsigned not null default 17.68, 
f3 decimal unsigned not null default 99.2, 
f4 decimal unsigned not null default 99.7, 
f5 decimal unsigned not null default 104.49, 
f6 decimal unsigned not null default 199.91, 
f7 decimal unsigned not null default 999.9, 
f8 decimal unsigned not null default 9999.99);
Warnings:
Note	1265	Data truncated for column 'f1' at row 1
Note	1265	Data truncated for column 'f2' at row 1
Note	1265	Data truncated for column 'f3' at row 1
Note	1265	Data truncated for column 'f4' at row 1
Note	1265	Data truncated for column 'f5' at row 1
Note	1265	Data truncated for column 'f6' at row 1
Note	1265	Data truncated for column 'f7' at row 1
Note	1265	Data truncated for column 'f8' at row 1
insert into t1 (f1) values (1);
select * from t1;
f1	f2	f3	f4	f5	f6	f7	f8
1	18	99	100	104	200	1000	10000
drop table t1;
create table t1 (
f0 decimal (30,30) zerofill not null DEFAULT 0,
f1 decimal (0,0) zerofill not null default 0);
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f0` decimal(30,30) unsigned zerofill NOT NULL default '0.000000000000000000000000000000',
  `f1` decimal(10,0) unsigned zerofill NOT NULL default '0000000000'
) ENGINE=MyISAM DEFAULT CHARSET=latin1
drop table t1;
drop procedure if exists wg2;
Warnings:
Note	1305	PROCEDURE wg2 does not exist
create procedure wg2()
begin
declare v int default 1;
declare tdec decimal(5) default 0;
while v <= 9 do set tdec =tdec * 10;
select v, tdec;
set v = v + 1;
end while;
end//
call wg2()//
v	tdec
1	0
v	tdec
2	0
v	tdec
3	0
v	tdec
4	0
v	tdec
5	0
v	tdec
6	0
v	tdec
7	0
v	tdec
8	0
v	tdec
9	0
drop procedure wg2;
select cast(@non_existing_user_var/2 as DECIMAL);
cast(@non_existing_user_var/2 as DECIMAL)
NULL
create table t (d decimal(0,10));
ERROR 42000: For float(M,D), double(M,D) or decimal(M,D), M must be >= D (column 'd').
CREATE TABLE t1 (
my_float   FLOAT,
my_double  DOUBLE,
my_varchar VARCHAR(50),
my_decimal DECIMAL(65,30)
);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `my_float` float default NULL,
  `my_double` double default NULL,
  `my_varchar` varchar(50) default NULL,
  `my_decimal` decimal(65,30) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
INSERT INTO t1 SET my_float = 1.175494345e-32,
my_double = 1.175494345e-32,
my_varchar = '1.175494345e-32';
INSERT INTO t1 SET my_float = 1.175494345e-31,
my_double = 1.175494345e-31,
my_varchar = '1.175494345e-31';
INSERT INTO t1 SET my_float = 1.175494345e-30,
my_double = 1.175494345e-30,
my_varchar = '1.175494345e-30';
INSERT INTO t1 SET my_float = 1.175494345e-29,
my_double = 1.175494345e-29,
my_varchar = '1.175494345e-29';
INSERT INTO t1 SET my_float = 1.175494345e-28,
my_double = 1.175494345e-28,
my_varchar = '1.175494345e-28';
INSERT INTO t1 SET my_float = 1.175494345e-27,
my_double = 1.175494345e-27,
my_varchar = '1.175494345e-27';
INSERT INTO t1 SET my_float = 1.175494345e-26,
my_double = 1.175494345e-26,
my_varchar = '1.175494345e-26';
INSERT INTO t1 SET my_float = 1.175494345e-25,
my_double = 1.175494345e-25,
my_varchar = '1.175494345e-25';
INSERT INTO t1 SET my_float = 1.175494345e-24,
my_double = 1.175494345e-24,
my_varchar = '1.175494345e-24';
INSERT INTO t1 SET my_float = 1.175494345e-23,
my_double = 1.175494345e-23,
my_varchar = '1.175494345e-23';
INSERT INTO t1 SET my_float = 1.175494345e-22,
my_double = 1.175494345e-22,
my_varchar = '1.175494345e-22';
INSERT INTO t1 SET my_float = 1.175494345e-21,
my_double = 1.175494345e-21,
my_varchar = '1.175494345e-21';
INSERT INTO t1 SET my_float = 1.175494345e-20,
my_double = 1.175494345e-20,
my_varchar = '1.175494345e-20';
INSERT INTO t1 SET my_float = 1.175494345e-19,
my_double = 1.175494345e-19,
my_varchar = '1.175494345e-19';
INSERT INTO t1 SET my_float = 1.175494345e-18,
my_double = 1.175494345e-18,
my_varchar = '1.175494345e-18';
INSERT INTO t1 SET my_float = 1.175494345e-17,
my_double = 1.175494345e-17,
my_varchar = '1.175494345e-17';
INSERT INTO t1 SET my_float = 1.175494345e-16,
my_double = 1.175494345e-16,
my_varchar = '1.175494345e-16';
INSERT INTO t1 SET my_float = 1.175494345e-15,
my_double = 1.175494345e-15,
my_varchar = '1.175494345e-15';
INSERT INTO t1 SET my_float = 1.175494345e-14,
my_double = 1.175494345e-14,
my_varchar = '1.175494345e-14';
INSERT INTO t1 SET my_float = 1.175494345e-13,
my_double = 1.175494345e-13,
my_varchar = '1.175494345e-13';
INSERT INTO t1 SET my_float = 1.175494345e-12,
my_double = 1.175494345e-12,
my_varchar = '1.175494345e-12';
INSERT INTO t1 SET my_float = 1.175494345e-11,
my_double = 1.175494345e-11,
my_varchar = '1.175494345e-11';
INSERT INTO t1 SET my_float = 1.175494345e-10,
my_double = 1.175494345e-10,
my_varchar = '1.175494345e-10';
INSERT INTO t1 SET my_float = 1.175494345e-9,
my_double = 1.175494345e-9,
my_varchar = '1.175494345e-9';
INSERT INTO t1 SET my_float = 1.175494345e-8,
my_double = 1.175494345e-8,
my_varchar = '1.175494345e-8';
INSERT INTO t1 SET my_float = 1.175494345e-7,
my_double = 1.175494345e-7,
my_varchar = '1.175494345e-7';
INSERT INTO t1 SET my_float = 1.175494345e-6,
my_double = 1.175494345e-6,
my_varchar = '1.175494345e-6';
INSERT INTO t1 SET my_float = 1.175494345e-5,
my_double = 1.175494345e-5,
my_varchar = '1.175494345e-5';
INSERT INTO t1 SET my_float = 1.175494345e-4,
my_double = 1.175494345e-4,
my_varchar = '1.175494345e-4';
INSERT INTO t1 SET my_float = 1.175494345e-3,
my_double = 1.175494345e-3,
my_varchar = '1.175494345e-3';
INSERT INTO t1 SET my_float = 1.175494345e-2,
my_double = 1.175494345e-2,
my_varchar = '1.175494345e-2';
INSERT INTO t1 SET my_float = 1.175494345e-1,
my_double = 1.175494345e-1,
my_varchar = '1.175494345e-1';
SELECT my_float, my_double, my_varchar FROM t1;
my_float	my_double	my_varchar
1.17549e-32	1.175494345e-32	1.175494345e-32
1.17549e-31	1.175494345e-31	1.175494345e-31
1.17549e-30	1.175494345e-30	1.175494345e-30
1.17549e-29	1.175494345e-29	1.175494345e-29
1.17549e-28	1.175494345e-28	1.175494345e-28
1.17549e-27	1.175494345e-27	1.175494345e-27
1.17549e-26	1.175494345e-26	1.175494345e-26
1.17549e-25	1.175494345e-25	1.175494345e-25
1.17549e-24	1.175494345e-24	1.175494345e-24
1.17549e-23	1.175494345e-23	1.175494345e-23
1.17549e-22	1.175494345e-22	1.175494345e-22
1.17549e-21	1.175494345e-21	1.175494345e-21
1.17549e-20	1.175494345e-20	1.175494345e-20
1.17549e-19	1.175494345e-19	1.175494345e-19
1.17549e-18	1.175494345e-18	1.175494345e-18
1.17549e-17	1.175494345e-17	1.175494345e-17
1.17549e-16	1.175494345e-16	1.175494345e-16
1.17549e-15	1.175494345e-15	1.175494345e-15
1.17549e-14	1.175494345e-14	1.175494345e-14
1.17549e-13	1.175494345e-13	1.175494345e-13
1.17549e-12	1.175494345e-12	1.175494345e-12
1.17549e-11	1.175494345e-11	1.175494345e-11
1.17549e-10	1.175494345e-10	1.175494345e-10
1.17549e-09	1.175494345e-09	1.175494345e-9
1.17549e-08	1.175494345e-08	1.175494345e-8
1.17549e-07	1.175494345e-07	1.175494345e-7
1.17549e-06	1.175494345e-06	1.175494345e-6
1.17549e-05	1.175494345e-05	1.175494345e-5
0.000117549	0.0001175494345	1.175494345e-4
0.00117549	0.001175494345	1.175494345e-3
0.0117549	0.01175494345	1.175494345e-2
0.117549	0.1175494345	1.175494345e-1
SELECT CAST(my_float   AS DECIMAL(65,30)), my_float FROM t1;
CAST(my_float   AS DECIMAL(65,30))	my_float
0.000000000000000000000000000000	1.17549e-32
0.000000000000000000000000000000	1.17549e-31
0.000000000000000000000000000001	1.17549e-30
0.000000000000000000000000000012	1.17549e-29
0.000000000000000000000000000118	1.17549e-28
0.000000000000000000000000001175	1.17549e-27
0.000000000000000000000000011755	1.17549e-26
0.000000000000000000000000117549	1.17549e-25
0.000000000000000000000001175494	1.17549e-24
0.000000000000000000000011754943	1.17549e-23
0.000000000000000000000117549438	1.17549e-22
0.000000000000000000001175494332	1.17549e-21
0.000000000000000000011754943324	1.17549e-20
0.000000000000000000117549434853	1.17549e-19
0.000000000000000001175494374380	1.17549e-18
0.000000000000000011754943743802	1.17549e-17
0.000000000000000117549432474939	1.17549e-16
0.000000000000001175494324749389	1.17549e-15
0.000000000000011754943671010360	1.17549e-14
0.000000000000117549429933840000	1.17549e-13
0.000000000001175494380653563000	1.17549e-12
0.000000000011754943372854760000	1.17549e-11
0.000000000117549428524377200000	1.17549e-10
0.000000001175494368510499000000	1.17549e-09
0.000000011754943685104990000000	1.17549e-08
0.000000117549433298336200000000	1.17549e-07
0.000001175494389826781000000000	1.17549e-06
0.000011754943443520460000000000	1.17549e-05
0.000117549432616215200000000000	0.000117549
0.001175494398921728000000000000	0.00117549
0.011754943057894710000000000000	0.0117549
0.117549434304237400000000000000	0.117549
SELECT CAST(my_double  AS DECIMAL(65,30)), my_double FROM t1;
CAST(my_double  AS DECIMAL(65,30))	my_double
0.000000000000000000000000000000	1.175494345e-32
0.000000000000000000000000000000	1.175494345e-31
0.000000000000000000000000000001	1.175494345e-30
0.000000000000000000000000000012	1.175494345e-29
0.000000000000000000000000000118	1.175494345e-28
0.000000000000000000000000001175	1.175494345e-27
0.000000000000000000000000011755	1.175494345e-26
0.000000000000000000000000117549	1.175494345e-25
0.000000000000000000000001175494	1.175494345e-24
0.000000000000000000000011754943	1.175494345e-23
0.000000000000000000000117549435	1.175494345e-22
0.000000000000000000001175494345	1.175494345e-21
0.000000000000000000011754943450	1.175494345e-20
0.000000000000000000117549434500	1.175494345e-19
0.000000000000000001175494345000	1.175494345e-18
0.000000000000000011754943450000	1.175494345e-17
0.000000000000000117549434500000	1.175494345e-16
0.000000000000001175494345000000	1.175494345e-15
0.000000000000011754943450000000	1.175494345e-14
0.000000000000117549434500000000	1.175494345e-13
0.000000000001175494345000000000	1.175494345e-12
0.000000000011754943450000000000	1.175494345e-11
0.000000000117549434500000000000	1.175494345e-10
0.000000001175494345000000000000	1.175494345e-09
0.000000011754943450000000000000	1.175494345e-08
0.000000117549434500000000000000	1.175494345e-07
0.000001175494345000000000000000	1.175494345e-06
0.000011754943450000000000000000	1.175494345e-05
0.000117549434500000000000000000	0.0001175494345
0.001175494345000000000000000000	0.001175494345
0.011754943450000000000000000000	0.01175494345
0.117549434500000000000000000000	0.1175494345
SELECT CAST(my_varchar AS DECIMAL(65,30)), my_varchar FROM t1;
CAST(my_varchar AS DECIMAL(65,30))	my_varchar
0.000000000000000000000000000000	1.175494345e-32
0.000000000000000000000000000000	1.175494345e-31
0.000000000000000000000000000001	1.175494345e-30
0.000000000000000000000000000012	1.175494345e-29
0.000000000000000000000000000118	1.175494345e-28
0.000000000000000000000000001175	1.175494345e-27
0.000000000000000000000000011755	1.175494345e-26
0.000000000000000000000000117549	1.175494345e-25
0.000000000000000000000001175494	1.175494345e-24
0.000000000000000000000011754943	1.175494345e-23
0.000000000000000000000117549435	1.175494345e-22
0.000000000000000000001175494345	1.175494345e-21
0.000000000000000000011754943450	1.175494345e-20
0.000000000000000000117549434500	1.175494345e-19
0.000000000000000001175494345000	1.175494345e-18
0.000000000000000011754943450000	1.175494345e-17
0.000000000000000117549434500000	1.175494345e-16
0.000000000000001175494345000000	1.175494345e-15
0.000000000000011754943450000000	1.175494345e-14
0.000000000000117549434500000000	1.175494345e-13
0.000000000001175494345000000000	1.175494345e-12
0.000000000011754943450000000000	1.175494345e-11
0.000000000117549434500000000000	1.175494345e-10
0.000000001175494345000000000000	1.175494345e-9
0.000000011754943450000000000000	1.175494345e-8
0.000000117549434500000000000000	1.175494345e-7
0.000001175494345000000000000000	1.175494345e-6
0.000011754943450000000000000000	1.175494345e-5
0.000117549434500000000000000000	1.175494345e-4
0.001175494345000000000000000000	1.175494345e-3
0.011754943450000000000000000000	1.175494345e-2
0.117549434500000000000000000000	1.175494345e-1
UPDATE t1 SET my_decimal = my_float;
SELECT my_decimal, my_float   FROM t1;
my_decimal	my_float
0.000000000000000000000000000000	1.17549e-32
0.000000000000000000000000000000	1.17549e-31
0.000000000000000000000000000001	1.17549e-30
0.000000000000000000000000000012	1.17549e-29
0.000000000000000000000000000118	1.17549e-28
0.000000000000000000000000001175	1.17549e-27
0.000000000000000000000000011755	1.17549e-26
0.000000000000000000000000117549	1.17549e-25
0.000000000000000000000001175494	1.17549e-24
0.000000000000000000000011754943	1.17549e-23
0.000000000000000000000117549438	1.17549e-22
0.000000000000000000001175494332	1.17549e-21
0.000000000000000000011754943324	1.17549e-20
0.000000000000000000117549434853	1.17549e-19
0.000000000000000001175494374380	1.17549e-18
0.000000000000000011754943743802	1.17549e-17
0.000000000000000117549432474939	1.17549e-16
0.000000000000001175494324749389	1.17549e-15
0.000000000000011754943671010360	1.17549e-14
0.000000000000117549429933840000	1.17549e-13
0.000000000001175494380653563000	1.17549e-12
0.000000000011754943372854760000	1.17549e-11
0.000000000117549428524377200000	1.17549e-10
0.000000001175494368510499000000	1.17549e-09
0.000000011754943685104990000000	1.17549e-08
0.000000117549433298336200000000	1.17549e-07
0.000001175494389826781000000000	1.17549e-06
0.000011754943443520460000000000	1.17549e-05
0.000117549432616215200000000000	0.000117549
0.001175494398921728000000000000	0.00117549
0.011754943057894710000000000000	0.0117549
0.117549434304237400000000000000	0.117549
UPDATE t1 SET my_decimal = my_double;
SELECT my_decimal, my_double  FROM t1;
my_decimal	my_double
0.000000000000000000000000000000	1.175494345e-32
0.000000000000000000000000000000	1.175494345e-31
0.000000000000000000000000000001	1.175494345e-30
0.000000000000000000000000000012	1.175494345e-29
0.000000000000000000000000000118	1.175494345e-28
0.000000000000000000000000001175	1.175494345e-27
0.000000000000000000000000011755	1.175494345e-26
0.000000000000000000000000117549	1.175494345e-25
0.000000000000000000000001175494	1.175494345e-24
0.000000000000000000000011754943	1.175494345e-23
0.000000000000000000000117549435	1.175494345e-22
0.000000000000000000001175494345	1.175494345e-21
0.000000000000000000011754943450	1.175494345e-20
0.000000000000000000117549434500	1.175494345e-19
0.000000000000000001175494345000	1.175494345e-18
0.000000000000000011754943450000	1.175494345e-17
0.000000000000000117549434500000	1.175494345e-16
0.000000000000001175494345000000	1.175494345e-15
0.000000000000011754943450000000	1.175494345e-14
0.000000000000117549434500000000	1.175494345e-13
0.000000000001175494345000000000	1.175494345e-12
0.000000000011754943450000000000	1.175494345e-11
0.000000000117549434500000000000	1.175494345e-10
0.000000001175494345000000000000	1.175494345e-09
0.000000011754943450000000000000	1.175494345e-08
0.000000117549434500000000000000	1.175494345e-07
0.000001175494345000000000000000	1.175494345e-06
0.000011754943450000000000000000	1.175494345e-05
0.000117549434500000000000000000	0.0001175494345
0.001175494345000000000000000000	0.001175494345
0.011754943450000000000000000000	0.01175494345
0.117549434500000000000000000000	0.1175494345
UPDATE t1 SET my_decimal = my_varchar;
Warnings:
Note	1265	Data truncated for column 'my_decimal' at row 1
Note	1265	Data truncated for column 'my_decimal' at row 2
Note	1265	Data truncated for column 'my_decimal' at row 3
Note	1265	Data truncated for column 'my_decimal' at row 4
Note	1265	Data truncated for column 'my_decimal' at row 5
Note	1265	Data truncated for column 'my_decimal' at row 6
Note	1265	Data truncated for column 'my_decimal' at row 7
Note	1265	Data truncated for column 'my_decimal' at row 8
Note	1265	Data truncated for column 'my_decimal' at row 9
Note	1265	Data truncated for column 'my_decimal' at row 10
Note	1265	Data truncated for column 'my_decimal' at row 11
SELECT my_decimal, my_varchar FROM t1;
my_decimal	my_varchar
0.000000000000000000000000000000	1.175494345e-32
0.000000000000000000000000000000	1.175494345e-31
0.000000000000000000000000000001	1.175494345e-30
0.000000000000000000000000000012	1.175494345e-29
0.000000000000000000000000000118	1.175494345e-28
0.000000000000000000000000001175	1.175494345e-27
0.000000000000000000000000011755	1.175494345e-26
0.000000000000000000000000117549	1.175494345e-25
0.000000000000000000000001175494	1.175494345e-24
0.000000000000000000000011754943	1.175494345e-23
0.000000000000000000000117549435	1.175494345e-22
0.000000000000000000001175494345	1.175494345e-21
0.000000000000000000011754943450	1.175494345e-20
0.000000000000000000117549434500	1.175494345e-19
0.000000000000000001175494345000	1.175494345e-18
0.000000000000000011754943450000	1.175494345e-17
0.000000000000000117549434500000	1.175494345e-16
0.000000000000001175494345000000	1.175494345e-15
0.000000000000011754943450000000	1.175494345e-14
0.000000000000117549434500000000	1.175494345e-13
0.000000000001175494345000000000	1.175494345e-12
0.000000000011754943450000000000	1.175494345e-11
0.000000000117549434500000000000	1.175494345e-10
0.000000001175494345000000000000	1.175494345e-9
0.000000011754943450000000000000	1.175494345e-8
0.000000117549434500000000000000	1.175494345e-7
0.000001175494345000000000000000	1.175494345e-6
0.000011754943450000000000000000	1.175494345e-5
0.000117549434500000000000000000	1.175494345e-4
0.001175494345000000000000000000	1.175494345e-3
0.011754943450000000000000000000	1.175494345e-2
0.117549434500000000000000000000	1.175494345e-1
DROP TABLE t1;
create table t1 (c1 decimal(64));
insert into t1 values(
89000000000000000000000000000000000000000000000000000000000000000000000000000000000000000);
Warnings:
Error	1292	Truncated incorrect DECIMAL value: ''
Warning	1264	Out of range value adjusted for column 'c1' at row 1
insert into t1 values(
99999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999 *
99999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999);
Warnings:
Error	1292	Truncated incorrect DECIMAL value: ''
Error	1292	Truncated incorrect DECIMAL value: ''
Error	1292	Truncated incorrect DECIMAL value: ''
Warning	1264	Out of range value adjusted for column 'c1' at row 1
insert into t1 values(1e100);
Warnings:
Warning	1264	Out of range value adjusted for column 'c1' at row 1
select * from t1;
c1
9999999999999999999999999999999999999999999999999999999999999999
9999999999999999999999999999999999999999999999999999999999999999
9999999999999999999999999999999999999999999999999999999999999999
drop table t1;