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

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...

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

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.

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.

Here's what I'm using:

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//

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.

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.

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

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 typo

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

解决方案

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 ;

This can be simplified a bit to;

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 ;