MySQL 中的 base64 函数
来源:互联网 发布:淘宝店怎么加入淘宝客 编辑:程序博客网 时间:2024/06/11 22:41
MySQL
在5.6版本的时候增加了to_base64
和from_base64
函数,在此之前没有内部函数,只能使用User-Defined-Function。
本文分别介绍这两种方式下base64
函数的使用。
1. 5.6版本及之后的版本的base64
主要就是两个MySQL
内部函数to_base64和from_base64,使用也很简单,如下:
mysql> select version();+-----------+| version() |+-----------+| 5.7.11 |+-----------+1 row in set (0.00 sec)mysql> select to_base64('helloworld');+-------------------------+| to_base64('helloworld') |+-------------------------+| aGVsbG93b3JsZA== |+-------------------------+1 row in set (0.00 sec)mysql> select from_base64('aGVsbG93b3JsZA==');+---------------------------------+| from_base64('aGVsbG93b3JsZA==') |+---------------------------------+| helloworld |+---------------------------------+1 row in set (0.00 sec)mysql>
2. 5.6之前的版本
由于之前版本MySQL
没有与base64相关的函数,就有了很多用户自己实现了这两个函数base64_encode
、base64_decode
,下面是一个比较好的实现(http://www.cybercanibal.com/articulos-tecnicos/experiencia-tecnica/115-mysql-url-base64-encode-decode-function):
-- base64.sql - MySQL base64 encoding/decoding functions-- Copyright (C) 2006 Ian Gulliver-- -- This program is free software; you can redistribute it and/or modify-- it under the terms of version 2 of the GNU General Public License as-- published by the Free Software Foundation.-- -- This program is distributed in the hope that it will be useful,-- but WITHOUT ANY WARRANTY; without even the implied warranty of-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the-- GNU General Public License for more details.-- -- You should have received a copy of the GNU General Public License-- along with this program; if not, write to the Free Software-- Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USAdelimiter |DROP TABLE IF EXISTS input_pentaho_bod.base64_data |CREATE TABLE base64_data (c CHAR(1) BINARY, val TINYINT) |INSERT INTO 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 input_pentaho_bod.BASE64_DECODE |CREATE FUNCTION BASE64_DECODE (input BLOB) RETURNS BLOB CONTAINS SQL DETERMINISTIC SQL SECURITY INVOKERBEGIN 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 DEFAULT 3;each_input_char: WHILE in_count < 4 DO BEGIN DECLARE first_char CHAR(1); IF LENGTH(input) = 0 THEN RETURN ret; END IF; SET first_char = SUBSTRING(input,1,1); SET input = SUBSTRING(input,2); BEGIN DECLARE tempval TINYINT UNSIGNED; DECLARE error TINYINT DEFAULT 0; DECLARE base64_getval CURSOR FOR SELECT val FROM base64_data WHERE c = first_char; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET error = 1; OPEN base64_getval; FETCH base64_getval INTO tempval; CLOSE base64_getval; IF error THEN ITERATE each_input_char; END IF; SET accum_value = (accum_value << 6) + tempval; END; SET in_count = in_count + 1; IF first_char = '=' THEN SET done = 1; SET out_count = out_count - 1; END IF; END; END WHILE; -- We've now accumulated 24 bits; deaccumulate into bytes -- We have to work from the left, so use the third byte position and shift left WHILE out_count > 0 DO BEGIN SET ret = CONCAT(ret,CHAR((accum_value & 0xff0000) >> 16)); SET out_count = out_count - 1; SET accum_value = (accum_value << 8) & 0xffffff; END; END WHILE; END; END WHILE; RETURN ret;END |DROP FUNCTION IF EXISTS input_pentaho_bod.BASE64_ENCODE |CREATE FUNCTION BASE64_ENCODE (input BLOB) RETURNS BLOB CONTAINS SQL DETERMINISTIC SQL SECURITY INVOKERBEGIN 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 CHAR(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 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 ;
使用也非常简单:
mysql> select version();+------------+| version() |+------------+| 5.5.45-log |+------------+1 row in set (0.00 sec)mysql> select base64_encode('helloworld');+-----------------------------+| base64_encode('helloworld') |+-----------------------------+| aGVsbG93b3JsZA== |+-----------------------------+1 row in set (0.00 sec)mysql> select base64_decode('aGVsbG93b3JsZA==');+-----------------------------------+| base64_decode('aGVsbG93b3JsZA==') |+-----------------------------------+| helloworld |+-----------------------------------+1 row in set (0.00 sec)mysql>
3. 性能评估
UDF
的性能比native function
的性能差上90-100倍,其实还有一种方式[C版本的的UDF](http://bugs.mysql.com/file.php?id=3294&bug_id=18861),C版本的的UDF在性能上没有问题,但是也会存在部署和移植的问题,所幸的是在新版本的MySQL(5.6+)中已经有了这两个函数to_base64和from_base64。
ref:
http://stackoverflow.com/questions/358500/base64-encode-in-mysql
http://lists.mysql.com/internals/37665
http://www.cybercanibal.com/articulos-tecnicos/experiencia-tecnica/115-mysql-url-base64-encode-decode-function
http://dev.mysql.com/doc/refman/5.6/en/string-functions.html#function_to-base64
http://dev.mysql.com/doc/refman/5.6/en/string-functions.html#function_from-base64
- MySQL 中的 base64 函数
- mysql base64
- ASP网页中的Base64加密、解密函数代码
- mysql中的unix_timestamp函数
- mysql中的自定义函数
- MySql中的流程函数
- mysql中的函数
- MySQL中的字符串函数
- MySQL中的聚合函数
- MySQL中的流程函数
- mysql中的if函数
- Mysql中的基础函数
- Mysql中的基础函数
- mysql 中的内置函数
- mysql中的常用函数
- MySQL中的concat函数
- MySQL 中的常用函数
- MySQL中的常用函数
- Maven入门实战笔记10-持续集成
- maven wagon ftp ssh
- Java GUI图形界面开发工具
- xStream
- 最小生成树-Kruscal-POJ 1258 Agri-Net
- MySQL 中的 base64 函数
- xml to amp
- SourceTree 无法检测到文件状态改变的问题
- 安装SDL2.0
- 机器学习基础(十九)—— Trick
- 数据可视化之Grafana-Table Panel
- JAXB vs XStream
- Digester解析xml文件
- 《Python数据分析基础教程:Numpy学习指南》- 速记 - 第四章