Bug #18861 Add base64_encode, base64_decode functions
Submitted: 6 Apr 2006 18:00 Modified: 23 Sep 2013 16:49
Reporter: Roberto Spadim (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S4 (Feature request)
Version:ANY OS:Any (ANY)
Assigned to: CPU Architecture:Any
Tags: functions, qc
Triage: Needs Triage: D5 (Feature request)

[6 Apr 2006 18:00] Roberto Spadim
Description:
what about creating new functions like:
BASE64_ENCODE
BASE64_DECODE

How to repeat:
select base64_encode("STRING HERE")
select base64_decode("STRING HERE")
select base64_decode(base64_encode("STRING HERE")) -> should produce "STRING HERE"

Suggested fix:
create some functions
[6 Apr 2006 18:11] Roberto Spadim
AN VERY OLD IMPLEMENTATION OF BASE64 FOR MYSQL

Attachment: mysql-udf-base64.c (text/x-csrc), 6.94 KiB.

[13 Jun 2007 16:17] Roberto Spadim
any implementation of this function for mysql 5.1?
[13 Jun 2007 16:30] Miguel Solorzano
Thank you for the bug report feature request.
[28 Jan 2010 12:35] Guilhem Bichot
see also http://lists.mysql.com/internals/37666
[28 Jan 2010 13:13] Roberto Spadim
oh yes, base64 on sql query!!! any version prevision for this feature? tnks
[1 Jun 2010 23:05] Ivo Roper
An efficient set of base64 functions would be very appreciated. It seems like there's a lot of interest in this from a quick perusal of the Google search results.

Thank you kindly.
[18 Nov 2010 14:41] Daniel Smith
BASE64_DECODE and BASE64_ENCODE implementation

Attachment: base64.sql (application/octet-stream, text), 4.80 KiB.

[18 Nov 2010 14:43] Daniel Smith
I attached a SQL implementation of BASE64_* methods from Ian. You can use this SQL functions as a workaround.

BTW: I still hope that mysql once will support BASE64_* :-)
[18 Nov 2010 15:59] Roberto Spadim
it´s very slow :P
when mysql was 4.xxx it should include more fast this function
[18 Mar 2011 15:59] Roberto Spadim
any new idea? i'm using work around today... but a c function is better... it's easy... could anyone implement at mysql trunk?
[18 Mar 2011 16:10] Roberto Spadim
return long blob

i will use it as binary data
people from http/html/php/python/web2.0 use base64 to send files to server (base64 don't have special chars) that's the main use
using base64 at sql command line could help with reading some 'file' on mysql just with sql language (no more app side function)
another point. base64 string is bigger than real string, that's another point why base64_decode could be useful (less network traffic)

using longblob is nice for me (no text/latinX/utf-8/xxx conversion)
[24 Mar 2011 4:33] Roberto Spadim
we could implement it as a 'default' package plugin
anyone that want it could load plugin, this solve the problem who already have it implemented with SQL functions (to don´t conflict with function/tables/fields/views/others 'private' database names)

in future versions could put it in default mysql functions
[20 Jun 2011 8:16] jifei tan
Yes, there is not a native base64 encode/decode function now(My current installation is 5.1), and the stored function version is not so fast, so sometime we can translate the ENCRYPT binary block into hex string by HEX() and then store the result into VARCHAR/TEXT column, and translate the hexed text to binary block by UNHEX().

i.g.:

SET @encryptedText = HEX(AES_ENCRYPT('foo', 'bar'));
SET @var_foo = AES_DECRYPT(UNHEX(@encryptedText), 'bar');
SELECT @var_foo; -- foo

=====
Best regards.
[20 Jun 2011 15:59] Roberto Spadim
hum, it´s a workaround, but base64 is consume less space than hex
[2 Feb 2012 21:27] Roberto Spadim
anyone implemented this?
[3 Feb 2012 18:52] Daniel Smith
I don't think so. I am using the SQL from above.
[23 Sep 2013 16:49] Roberto Spadim
PLEASE CLOSE THIS BUG:

TO_BASE64  /  FROM_BASE64
This function was added in MySQL 5.6.1