MySQL中的base64编码

我想从一个表中选择一个blob col,base64对其进行编码并将其插入另一个表中.有没有办法做到这一点,没有从数据库和我的应用程序圆形绊倒数据？

我一直在寻找相同的东西,我刚刚看到MySQL 5.6有几个新的字符串函数支持这个功能:TO_BASE64和FROM_BASE64.

当编码字符串为32字节(空格)时,来自http://wi-fizzle.com/downloads/base64.sql的函数包含一些错误,例如BASE64_ENCODE(CONCAT(CHAR(15),CHAR(32))).这是纠正功能

DELIMITER $$

USE `YOUR DATABASE`$$

DROP TABLE IF EXISTS core_base64_data$$
CREATE TABLE core_base64_data (c CHAR(1) BINARY, val TINYINT)$$
INSERT INTO core_base64_data VALUES 
('A',0), ('B',1), ('C',2), ('D',3), ('E',4), ('F',5), ('G',6), ('H',7), ('I',8), ('J',9),
('K',10), ('L',11), ('M',12), ('N',13), ('O',14), ('P',15), ('Q',16), ('R',17), ('S',18), ('T',19),
('U',20), ('V',21), ('W',22), ('X',23), ('Y',24), ('Z',25), ('a',26), ('b',27), ('c',28), ('d',29),
('e',30), ('f',31), ('g',32), ('h',33), ('i',34), ('j',35), ('k',36), ('l',37), ('m',38), ('n',39),
('o',40), ('p',41), ('q',42), ('r',43), ('s',44), ('t',45), ('u',46), ('v',47), ('w',48), ('x',49),
('y',50), ('z',51), ('0',52), ('1',53), ('2',54), ('3',55), ('4',56), ('5',57), ('6',58), ('7',59),
('8',60), ('9',61), ('+',62), ('/',63), ('=',0) $$

DROP FUNCTION IF EXISTS `BASE64_ENCODE`$$

CREATE DEFINER=`YOUR DATABASE`@`%` FUNCTION `BASE64_ENCODE`(input BLOB) RETURNS BLOB
    DETERMINISTIC
    SQL SECURITY INVOKER
BEGIN
    DECLARE ret BLOB DEFAULT '';
    DECLARE done TINYINT DEFAULT 0;
    IF input IS NULL THEN
        RETURN NULL;
    END IF;
each_block:
    WHILE NOT done DO BEGIN
        DECLARE accum_value BIGINT UNSIGNED DEFAULT 0;
        DECLARE in_count TINYINT DEFAULT 0;
        DECLARE out_count TINYINT;
each_input_char:
        WHILE in_count < 3 DO BEGIN
            DECLARE first_char BLOB(1);

            IF LENGTH(input) = 0 THEN
                SET done = 1;
                SET accum_value = accum_value << (8 * (3 - in_count));
                LEAVE each_input_char;
            END IF;

            SET first_char = SUBSTRING(input,1,1);
            SET input = SUBSTRING(input,2);

            SET accum_value = (accum_value << 8) + ASCII(first_char);
            SET in_count = in_count + 1;
        END; END WHILE;

        -- We've now accumulated 24 bits; deaccumulate into base64 characters
        -- We have to work from the left, so use the third byte position and shift left
        CASE
            WHEN in_count = 3 THEN SET out_count = 4;
            WHEN in_count = 2 THEN SET out_count = 3;
            WHEN in_count = 1 THEN SET out_count = 2;
            ELSE RETURN ret;
        END CASE;

        WHILE out_count > 0 DO BEGIN
            BEGIN
                DECLARE out_char CHAR(1);
                DECLARE base64_getval CURSOR FOR SELECT c FROM core_base64_data WHERE val = (accum_value >> 18);
                OPEN base64_getval;
                FETCH base64_getval INTO out_char;
                CLOSE base64_getval;
                SET ret = CONCAT(ret,out_char);
                SET out_count = out_count - 1;
                SET accum_value = accum_value << 6 & 0xffffff;
            END;
        END; END WHILE;
        CASE
            WHEN in_count = 2 THEN SET ret = CONCAT(ret,'=');
            WHEN in_count = 1 THEN SET ret = CONCAT(ret,'==');
            ELSE BEGIN END;
        END CASE;

    END; END WHILE;
    RETURN ret;
END$$

DELIMITER ;

SELECT `id`,`name`, TO_BASE64(content) FROM `db`.`upload`

这会将blob值从内容列转换为base64字符串。然后，您可以随意使用此字符串，甚至可以将其插入到另一个表中

看起来没有,虽然它是被要求的,并且有一个UDF.

编辑:或者有...... 这个.啊.

对于那些感兴趣的人,这些是目前唯一的选择:

1)使用这些功能:

http://wi-fizzle.com/downloads/base64.sql

2)如果你已经有了sys_eval UDF(Linux),你可以这样做:

sys_eval(CONCAT("echo '",myField,"' | base64"));

已知第一种方法很慢.第二个问题是,编码实际上发生在"外部"MySQL,它可能有编码问题(除了你用sys_*函数添加的安全风险).

不幸的是,没有UDF编译版本(应该更快),也没有MySQL中的本机支持(Posgresql支持它!).

似乎MySQL开发团队对实现它并不感兴趣,因为这个函数已经存在于其他语言中,这对我来说似乎很愚蠢.