MySQL-为自定义排序创建用户定义的函数

Question

我正在处理大量旧数据（从平面文件db转换），其中字段的格式设置为输入记录的年份的最后2位，然后是4位的增量…

例如，1998年创建的第三条记录将为“ 980003”，而2004年创建的第十一条记录将为“ 040011”。

我无法更改这些值-
它们通过他们的公司存在，已经在州，客户等中注册。我知道将年份和其余年份分隔到单独的列中会很好，但这是不可能的。我什至不能真正做到“内部”，因为每一行都有大约300个可排序的字段，它们非常习惯于将此字段用作记录标识符。

所以我正在尝试实现MySQL
UDF（第一次）进行排序。该查询成功执行，它允许我“通过custom_sort（whatever）从表顺序中选择任何内容”，但是顺序不是我期望的。

这是我正在使用的：

DELIMITER //

CREATE FUNCTION custom_sort(id VARCHAR(8))
    RETURNS INT
    READS SQL DATA
    DETERMINISTIC
    BEGIN
        DECLARE year VARCHAR(2);
        DECLARE balance VARCHAR(6);
        DECLARE stringValue VARCHAR(8);
        SET year = SUBSTRING(0, 2,  id);
        SET balance = SUBSTRING(2, 6, id);
        IF(year <= 96) THEN
            SET stringValue = CONCAT('20', year, balance);
        ELSE
            SET stringValue = CONCAT('19', year, balance);
        END IF;
        RETURN CAST(stringValue as UNSIGNED);
    END//

记录只返回到96（因此，任意的“如果前2个字符少于96，则在前面加上‘20’，否则在前面加上‘19’）。我对此并不感到兴奋，但不相信这是核心问题是。

为了进一步努力，事实证明1996和1997都是5位数字，遵循上述相同的模式，但不是4位数字的增量，而是3位数字的增量。同样，我怀疑这将是一个问题，但不是核心问题。

我通过此custom_sort获得的回报示例：

001471
051047
080628
040285
110877
020867
090744
001537
051111
080692
040349
110941
020931
090808
001603
051175

我真的不知道我在这里做什么，并且从未将MySQL用于这样的UDF-任何帮助将不胜感激。

TYIA

/编辑错字

/ EDIT 2 concat需要增加“年”值-仍然得到相同的结果

Answer 1

您的子字符串有一些问题，并且强制转换为int使其在末尾而不是按年对具有更多数字的值进行排序。这应该更好地工作；

DELIMITER //

CREATE FUNCTION custom_sort(id VARCHAR(8))
    RETURNS VARCHAR(10)
    READS SQL DATA
    DETERMINISTIC
    BEGIN
        DECLARE year VARCHAR(2);
        DECLARE balance VARCHAR(6);
        DECLARE stringValue VARCHAR(10);
        SET year = SUBSTRING(id, 1, 2);
        SET balance = SUBSTRING(id, 3, 6);
        IF(year <= 96) THEN
            SET stringValue = CONCAT('20', year, balance);
        ELSE
            SET stringValue = CONCAT('19', year, balance);
        END IF;
        RETURN stringValue;
    END//

DELIMITER ;

这可以简化为；

DELIMITER //

CREATE FUNCTION custom_sort(id VARCHAR(8))
    RETURNS varchar(10)
    DETERMINISTIC
    BEGIN
        IF(SUBSTRING(id, 1, 2) <= '96') THEN
            RETURN CONCAT('20', id);
        ELSE
            RETURN CONCAT('19', id);
        END IF;
    END//

DELIMITER ;