mysql - 将 CAST 与 insert into ... select 结合使用

Question

我正在尝试使用 CAST(column AS DECIMAL(7,2)) 将一些错误数据转换为正确的数据，这在仅选择时有效，但一旦与 INSERT 语句结合使用，该语句失败:

/* setup */
drop table if exists dst;
create table dst (
  id int not null auto_increment
  , columnA decimal(7,2)
  , primary key (id)
);

drop table if exists src;
create table src (
  id int not null auto_increment
  , columnA varchar(255)
  , primary key (id)
);

insert into src (columnA) values ('');

/* test */

/* This works as I would like it to*/
select CAST(columnA AS decimal(7,2)) from src; /* returns 0.00 */

/* This fails */
insert into dst (columnA)
select CAST(columnA AS decimal(7,2)) from src; 
/*0 19  21:01:56    insert into dst (columnA)
 select cast(columnA as decimal(7,2)) from src  Error Code: 1366. Incorrect decimal value: '' for column '' at row -1   0.000 sec*/

Edit: this is a pared down reproduction example - the data I am working with is much more varied than just an empty string - it might also be a non-numeric string value or a numeric value that is outside of the bounds of decimal(7,2) - in which the CAST statement works the way I would like it to when only in a SELECT but fails when trying to use them as part of the INSERT.

我是不是做错了什么，或者是否有其他方法可以实现我的目标？

我在 WIN 7 x64 上使用 MYSQL 5.6.11

Answer 1

使用 CASE 显式设置空字符串的值或 IF :

• SELECT CASE WHEN columnA = '' THEN 0 ELSE CAST(columnA AS decimal(7,2)) END
FROM src;

• SELECT IF(columnA <> '', CAST(columnA AS decimal(7,2)), 0)
FROM src;

或者使用 2 SELECT与 UNION ALL :

SELECT CAST(columnA AS decimal(7,2))
FROM src 
WHERE columnA <> ''
 UNION ALL
SELECT 0
FROM src
WHERE columnA = '';