mysql base64_MySQL中的base64编码

MySQL中的base64编码

我想从一个表中选择一个blob col，base64对其进行编码，然后将其插入到另一个表中。 有什么方法可以在不将数据往返数据库和通过我的应用程序往返的情况下进行此操作？

BCS asked 2020-07-27T05:31:08Z

8个解决方案

50 votes

我一直在寻找相同的东西，但我刚刚看到MySQL 5.6有两个支持此功能的新字符串函数：TO_BASE64和FROM_BASE64。

pau.moreno answered 2020-07-27T05:31:24Z

17 votes

当编码字符串中的字符串为32字节(空格)时，例如[BASECAT_CHAR(CONCAT(CHAR(15)，CHAR(32))))，[http://wi-fizzle.com/downloads/base64.sql]中的函数包含一些错误。 。 这是更正的功能

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 ;

WOLFF answered 2020-07-27T05:31:44Z

8 votes

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

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

Pini Cheyni answered 2020-07-27T05:32:04Z

5 votes

看起来好像没有，尽管有人要求，但有一个UDF。

编辑：或者是…这个。 啊。

ieure answered 2020-07-27T05:32:28Z

5 votes

对于那些感兴趣的人，这些是到目前为止唯一的选择：

1)使用以下功能：

[HTTP://我i-fizzle.com/downloads/巴塞64.SQL]

2)如果您已经拥有sys_eval UDF(Linux)，则可以执行以下操作：

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

已知第一种方法很慢。 第二个问题是编码实际上发生在MySQL的“外部”，它可能存在编码问题(除了您使用sys_ *函数添加的安全风险外)。

不幸的是，没有UDF编译版本(应该更快)，MySQL也没有本机支持(Posgresql支持！)。

MySQL开发团队似乎对实现它不感兴趣，因为该功能已经存在于其他语言中，这对我来说似乎很愚蠢。

lepe answered 2020-07-27T05:33:14Z

2 votes

另一个不需要支持表的自定义实现：

drop function if exists base64_encode;

create function base64_encode(_data blob)

returns text

begin

declare _alphabet char(64) default 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/';

declare _lim int unsigned default length(_data);

declare _i int unsigned default 0;

declare _chk3 char(6) default '';

declare _chk3int int default 0;

declare _enc text default '';

while _i < _lim do

set _chk3 = rpad(hex(binary substr(_data, _i + 1, 3)), 6, '0');

set _chk3int = conv(_chk3, 16, 10);

set _enc = concat(

_enc

, substr(_alphabet, ((_chk3int >> 18) & 63) + 1, 1)

, if (_lim-_i > 0, substr(_alphabet, ((_chk3int >> 12) & 63) + 1, 1), '=')

, if (_lim-_i > 1, substr(_alphabet, ((_chk3int >> 6) & 63) + 1, 1), '=')

, if (_lim-_i > 2, substr(_alphabet, ((_chk3int >> 0) & 63) + 1, 1), '=')

);

set _i = _i + 3;

end while;

return _enc;

end;

drop function if exists base64_decode;

create function base64_decode(_enc text)

returns blob

begin

declare _alphabet char(64) default 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/';

declare _lim int unsigned default 0;

declare _i int unsigned default 0;

declare _chr1byte tinyint default 0;

declare _chk4int int default 0;

declare _chk4int_bits tinyint default 0;

declare _dec blob default '';

declare _rem tinyint default 0;

set _enc = trim(_enc);

set _rem = if(right(_enc, 3) = '===', 3, if(right(_enc, 2) = '==', 2, if(right(_enc, 1) = '=', 1, 0)));

set _lim = length(_enc) - _rem;

while _i < _lim

do

set _chr1byte = locate(substr(_enc, _i + 1, 1), binary _alphabet) - 1;

if (_chr1byte > -1)

then

set _chk4int = (_chk4int << 6) | _chr1byte;

set _chk4int_bits = _chk4int_bits + 6;

if (_chk4int_bits = 24 or _i = _lim-1)

then

if (_i = _lim-1 and _chk4int_bits != 24)

then

set _chk4int = _chk4int << 0;

end if;

set _dec = concat(

_dec

, char((_chk4int >> (_chk4int_bits - 8)) & 0xff)

, if(_chk4int_bits > 8, char((_chk4int >> (_chk4int_bits - 16)) & 0xff), '\0')

, if(_chk4int_bits > 16, char((_chk4int >> (_chk4int_bits - 24)) & 0xff), '\0')

);

set _chk4int = 0;

set _chk4int_bits = 0;

end if;

end if;

set _i = _i + 1;

end while;

return substr(_dec, 1, length(_dec) - _rem);

end;

要旨

解码后应转换字符集：convert(base64_decode(base64_encode('ёлка')) using utf8)

Bars answered 2020-07-27T05:33:43Z

1 votes

如果您需要<5.6，请尝试使用此UDF，它似乎运行良好：

[HTTPS://GitHub.com/有-肯/MySQL-UDF-巴塞64]

Kevin McFadden answered 2020-07-27T05:34:07Z

-2 votes

创建表加密(用户名varchar(20)，密码varbinary(200))

插入加密值('raju'，aes_encrypt('kumar'，'key'))从用户名='raju'的加密中选择*，cast(aes_decrypt(password，'key')as char(40));

user9828512 answered 2020-07-27T05:34:31Z