我正在尝试使用 CAST(column AS DECIMAL(7,2))
将一些错误数据转换为正确的数据,这在 SELECT 时有效,但一旦与 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*/
编辑:这是一个精简的复制示例 - 我是数据 使用不仅仅是一个空字符串 - 它可能 也是非数字字符串值或外部的数值 十进制 (7,2( 的边界 -
CAST
语句在其中起作用 我希望它仅在SELECT
中但失败时的方式 尝试将它们用作INSERT
的一部分。
我做错了什么,还是有其他方法可以实现我正在寻找的目标?
我在 WIN 7 x64 上使用 MYSQL 5.6.11
使用 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
s 与 UNION ALL
一起使用:
SELECT CAST(columnA AS decimal(7,2))
FROM src
WHERE columnA <> ''
UNION ALL
SELECT 0
FROM src
WHERE columnA = '';
您可以尝试使用 REGEXP
来过滤掉源数据中的任何不规则之处:
INSERT INTO dst (columnA)
SELECT CAST(CASE
WHEN columnA REGEXP '^[0-9]{0,7}(.[0-9]{0,2})$' THEN columnA
ELSE 0
END AS decimal(7,2))
FROM src;
SQL 小提琴演示在这里