MyBatis自動生成Java/C#的Bean（Entity）的等價MYSQL實現函式

rgqancy發表於2017-02-08
MyBatisJavaC#BeanMySql函式

這是單元測試程式碼：TESTTITLECASE.sql

SELECT TITLECASE('ABC_DEF_hgk') as BEANNAME FROM DUAL;
SELECT TITLECASE('www_mysql_com') as BEANNAME FROM DUAL;
SELECT FIRSTLETTERUPPERCASE('ABC') FROM DUAL;
SELECT FIRSTLETTERUPPERCASE('aBC') FROM DUAL;
SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('mysql', '.', 2), '.', -1);
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('www.mysql.com', '.', 1), '.', -1);
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('www.mysql.com', '.', 2), '.', -1);
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('www.mysql.com', '.', 3), '.', -1);

這是仿照MyBatis的功能實現：TITLECASE（這個是從Sublime裡的Edit的Case下借鑑過來的）

CREATE DEFINER=`root`@`%` FUNCTION `TITLECASE`(x VARCHAR(255)) RETURNS varchar(255) CHARSET utf8
    READS SQL DATA
BEGIN
    DECLARE i INT;
    DECLARE n INT;
    DECLARE y VARCHAR(255);
    DECLARE word VARCHAR(255);
    DECLARE wordWithCase VARCHAR(255);

    SET i = 1;
    SET n = LENGTH(x) - LENGTH(REPLACE(x, '_', '')) + 1;
    SET y = '';

    WHILE i < n + 1 DO
        SET word = SUBSTRING_INDEX(SUBSTRING_INDEX(x, '_', i), '_' , -1);
        SET wordWithCase = CONCAT(IF(i=1,LOWER(SUBSTRING(word, 1, 1)),UPPER(SUBSTRING(word, 1, 1))), LOWER(SUBSTRING(word FROM 2)));
        SET y = CONCAT(y,wordWithCase);
        SET i = i + 1;
    END WHILE;

    RETURN y;
END

這是一個副產品：單詞首字元大寫：FIRSTLETTERUPPERCASE.sql

CREATE DEFINER=`root`@`%` FUNCTION `FIRSTLETTERUPPERCASE`(x VARCHAR(255)) RETURNS varchar(255) CHARSET utf8
    READS SQL DATA
BEGIN
    return CONCAT(UPPER(SUBSTRING(x, 1, 1)), LOWER(SUBSTRING(x FROM 2)));
END

 

相關文章