MySQL-为自定义排序创建用户定义的函数 [英] MySQL - creating a user-defined function for a custom sort

问题描述

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

I'm working with a large set of legacy data (converted from a flat-file db), where a field is formatted as the last 2 digits of the year the record was entered, followed by a 4 digit increment...

例如，在1998年创建的第三条记录将是"980003"，在2004年创建的第十一条记录将是"040011".

e.g., the third record created in 1998 would be "980003", and the eleventh record created in 2004 would be "040011".

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

i can not change these values - they exist through their company, are registered with the state, clients, etc. I know it'd be great to separate out the year and the rest of it into separate columns, but that's not possible. i can't even really do it "internally" since each row has about 300 fields that are all sortable, and they're very used to working with this field as a record identifier.

所以我正在尝试实现MySQL UDF(第一次)进行排序.该查询成功执行，它使我可以按custom_sort(任何)从表顺序中选择任何内容"，但是顺序不是我期望的.

so i'm trying to implement a MySQL UDF (for the first time) to sort. The query executes successfully, and it allows me to "select whatever from table order by custom_sort(whatever)", but the order is not what i'd expect.

这是我正在使用的:

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').我对此并不感到兴奋，但不要相信那是核心问题在哪里.

The records only go back to 96 (thus the arbitrary "if first 2 characters are less than 96, prepend '20' otherwise prepend '19'). I'm not thrilled with this bit, but don't believe that's where the core problem is.

要想在作品中另辟wrench径，原来1996和1997都是5位数字，遵循上述相同的模式，但不是4位数字的增量，而是3位数字的增量.再次，我怀疑这将是一个问题，但不是核心问题.

To throw another wrench in the works, it turns out that 1996 and 1997 are both 5 digits, following the same pattern described above but instead of a 4 digit increment, it's a 3 digit increment. Again, I suspect this will be a problem, but is not the core problem.

使用此custom_sort获得的退货示例:

An example of the returns I'm getting with this custom_sort:

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

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

I really have no idea what I'm doing here and have never used MySQL for a UDF like this - any help would be appreciated.

TYIA

/EDIT错字

/EDIT 2 concat需要增加年"值-仍然获得相同的结果

/EDIT 2 concat needed "year" value added - still getting same results

推荐答案

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

You have some problems with your substrings, and the cast to int at the end makes it sort values with more digits at the end, not by year. This should work better;

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 ;

这篇关于MySQL-为自定义排序创建用户定义的函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！