MySQL 将 1 列 VARCHAR 分成 2 列 INT 类型。我有错误 1292 截断不正确的 DOUBLE 值

Question

我正在尝试将 VARCHAR 列 SampleID 中的信息分成 INT ID 和 Ampola 类型的两列。

我正在使用 CAST 和 SUBSTRING_INDEX 函数。

我尝试使用“CAST”和“SUBSTRING_INDEX”进行“更新”，但没有成功。

我想将 306465-14 转换为 ID = 306465 并将 Ampola = 14 或 306466 转换为 ID = 306466 和 Ampola = 1，但当 SampleID 无效时忽略。

下面是表转储和我尝试使用的查询。

CREATE TABLE `peak` (`SampleID` varchar(255) NOT NULL,`ID` int(11) DEFAULT NULL,`Ampola` int(11) DEFAULT NULL, PRIMARY KEY (`SampleID`));
INSERT INTO `peak` VALUES ('306465-14',NULL,NULL)
                            ,('306465-15',NULL,NULL)
                            ,('306466',NULL,NULL)
                            ,('TESTE',NULL,NULL)
                            ,('TESTE-02',NULL, NULL)
                            ,('off-0101-2020', NULL,NULL);

使用 SELECT 效果很好:

SELECT SampleID
    ,CASE 
        WHEN SIGN(SampleID) = 0 THEN NULL
        WHEN CAST(SUBSTRING_INDEX(SampleID, '-', 1) AS UNSIGNED INTEGER) = 0 THEN NULL
        WHEN INSTR(SampleID, '-') = 0 AND SIGN(SampleID) = 0 THEN NULL
        ELSE CAST(SUBSTRING_INDEX(SampleID, '-', 1) AS UNSIGNED INTEGER)
    END AS ID
    ,CASE 
        WHEN INSTR(SampleID, '-') = 0 AND SIGN(SampleID) = 1 THEN 1
        WHEN CAST(SUBSTRING_INDEX(SampleID, '-', 1) AS UNSIGNED INTEGER) = 0 THEN NULL
        ELSE CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(SampleID, '-', 2), '-', -1) AS UNSIGNED INTEGER)
    END AS Ampola
FROM Peak;

结果:

但是使用 UPDATE 查询

UPDATE Peak
SET ID = 
CASE 
    WHEN SIGN(SampleID) = 0 THEN NULL
    WHEN CAST(SUBSTRING_INDEX(SampleID, '-', 1) AS UNSIGNED INTEGER) = 0 THEN NULL
    WHEN INSTR(SampleID, '-') = 0 AND SIGN(SampleID) = 0 THEN NULL
    ELSE CAST(SUBSTRING_INDEX(SampleID, '-', 1) AS UNSIGNED INTEGER)
END, 
Ampola = 
CASE 
    WHEN INSTR(SampleID, '-') = 0 AND SIGN(SampleID) = 1 THEN 1
    WHEN CAST(SUBSTRING_INDEX(SampleID, '-', 1) AS UNSIGNED INTEGER) = 0 THEN NULL
    ELSE CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(SampleID, '-', 2), '-', -1) AS UNSIGNED INTEGER)
END

错误消息:

Error Code: 1292. Truncated incorrect DOUBLE value: '306465-14'

感谢@Strawberry 提供的简单提示。

Answer 1

考虑以下示例，它的优点是既最小又完整......

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(x VARCHAR(20) NOT NULL
,m INT NULL
,n INT NULL
);

INSERT INTO my_table VALUES
('306465-14',NULL,NULL),
('306465-15',NULL,NULL);

UPDATE my_table SET m = SUBSTRING_INDEX(x,'-',1), n = SUBSTRING_INDEX(x,'-',-1); 

SELECT * FROM my_table;
+-----------+--------+------+
| x         | m      | n    |
+-----------+--------+------+
| 306465-14 | 306465 |   14 |
| 306465-15 | 306465 |   15 |
+-----------+--------+------+