正在尝试从select语句创建临时表。但是在MySQL5.5的临时表中创建的列的数据类型不同于在5.6及更高版本中创建的数据类型。

select version();
set sql_mode='';

CREATE TEMPORARY TABLE test_dataset ( 
amountInINR double(20,2) NOT NULL DEFAULT '0.00',
quantity int(10) NOT NULL, 
useQuantity int(10) NOT NULL,
Code varchar(128) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, 
isPercentageINR int(1) NOT NULL, 
promoDiscountId bigint(20) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into test_dataset values(1,2,3,4,5,6);

select * from test_dataset;

CREATE TEMPORARY TABLE test_dataset_temp_0  engine =memory as select if(count(1)=0,0,amountInINR) amountInINR,quantity,useQuantity,Code,isPercentageINR,promoDiscountId  from test_dataset where amountInINR=0;
CREATE TEMPORARY TABLE test_dataset_temp_1  engine =memory as select amountInINR,quantity,useQuantity,Code,isPercentageINR,promoDiscountId  from test_dataset where amountInINR=0;
desc test_dataset;
desc test_dataset_temp_0;
desc test_dataset_temp_1;

输出：

version : 5.6.35-1+deb.sury.org~xenial+0.1  
desc test_dataset

Field           Type            Null    Key Default Extra
amountInINR     double(20,2)    NO      0.00     
quantity        int(10)         NO      NULL     
useQuantity     int(10)         NO      NULL     
Code            varchar(128)    NO      NULL     
isPercentageINR int(1)          NO      NULL     
promoDiscountId bigint(20)      NO      0    

6 rows (0.002 s) Edit, Export

desc test_dataset_temp_0

Field           Type            Null    Key Default Extra
amountInINR     double(20,2)    YES     NULL     
quantity        bigint(11)      YES     NULL     
useQuantity     bigint(11)      YES     NULL     
Code            varchar(128)    YES     NULL     
isPercentageINR bigint(11)      YES     NULL     
promoDiscountId bigint(20)      YES     NULL     

6 rows (0.002 s) Edit, Export

desc test_dataset_temp_1

Field           Type            Null    Key Default Extra
amountInINR     double(20,2)    NO      0.00     
quantity        int(10)         NO      NULL     
useQuantity     int(10)         NO      NULL     
Code            varchar(128)    NO      NULL     
isPercentageINR int(1)          NO      NULL     
promoDiscountId bigint(20)      NO      0    

version :  5.5.44-log

desc test_dataset

Field           Type            Null    Key Default Extra
amountInINR     double(20,2)    NO      0.00     
quantity        int(10)         NO      NULL     
useQuantity     int(10)         NO      NULL     
Code            varchar(128)    NO      NULL     
isPercentageINR int(1)          NO      NULL     
promoDiscountId bigint(20)      NO      0    

6 rows (0.001 s) Edit, Export

desc test_dataset_temp_0

Field           Type            Null    Key Default Extra
amountInINR     double(20,2)    NO      0.00     
quantity        int(10)         NO      NULL     
useQuantity     int(10)         NO      NULL     
Code            varchar(128)    NO      NULL     
isPercentageINR int(1)          NO      NULL     
promoDiscountId bigint(20)      NO      0    

6 rows (0.001 s) Edit, Export

desc test_dataset_temp_1

Field           Type            Null    Key Default Extra
amountInINR     double(20,2)    NO      0.00     
quantity        int(10)         NO      NULL     
useQuantity     int(10)         NO      NULL     
Code            varchar(128)    NO      NULL     
isPercentageINR int(1)          NO      NULL     
promoDiscountId bigint(20)      NO      0