MySQL - 创建用户定义函数以进行自定义排序

Question

MySQL - 创建用户定义函数以进行自定义排序

mysql

7

我正在使用一组大型的历史数据（从平面文件数据库转换而来），其中一个字段格式为记录输入的年份的最后2位数，后跟4位数字增量...

例如，1998年创建的第三个记录将为“980003”，2004年创建的第11个记录将是“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年（因此任意“如果前两个字符小于96，则前置‘20’，否则前置‘19’”）。对于这一点我不太满意，但我认为这不是核心问题。

另外，情况更加复杂，1996年和1997年都是5位数，遵循上述相同的模式，但增量变成了3位数。再次提醒，我认为这可能会成为一个问题，但不是核心问题。

以下是custom_sort返回的示例结果：

我真的不知道我在这里做什么，也从未像这样使用MySQL来创建UDF - 希望能得到帮助。

非常感谢您提前给予的帮助。

/编辑打错字

/编辑 2 需要连接 "year" 值 - 仍然得到相同的结果

- momo

MySQL如何知道您正在将其转换为“UNSIGNED INT”，而不是例如“UNSIGNED TINYINT”？它是否知道，还是假设某些默认数字类型？ - biziclop

能否向数据库追加一个具有固定值的新列呢？不管怎样，对于不同长度的数字问题，您可以使用 LPAD 函数：http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_lpad - biziclop

@biziclop 不行 - 在用户界面中，用户可以访问所有列并对其进行编辑、排序等操作...管理员甚至可以添加列。我必须保持现有的结构。每个列在用户界面中都表示为可单击、拖动等的列。 - momo

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Joachim Isaksson · Accepted Answer

你的子字符串存在一些问题，而结尾处的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 ;