SQL 中的 Base64

本文介绍各类 SQL 数据库中的 Base64 编码/解码函数，包括 MySQL、SQL Server、PostgreSQL、Oracle 等。 你可以直接在 SQL 语句中完成数据的编码与解码。

概览

大多数现代 SQL 数据库都内置了 Base64 编码/解码函数。本文给出不同数据库的常见语法与函数， 并提供图像存储、二进制数据处理与数据传输等常见场景的示例。

MySQL 的 Base64 函数

基础编码/解码

-- Encode string to Base64
SELECT TO_BASE64('Hello, World!') AS encoded;
-- Result: SGVsbG8sIFdvcmxkIQ==

-- Decode Base64 to string
SELECT FROM_BASE64('SGVsbG8sIFdvcmxkIQ==') AS decoded;
-- Result: Hello, World!

-- Encode binary data
SELECT TO_BASE64(BINARY 'Hello') AS binary_encoded;
-- Result: SGVsbG8=

-- Decode to binary
SELECT FROM_BASE64('SGVsbG8=') AS binary_decoded;
-- Result: Hello

处理 BLOB 数据

-- Store image as Base64
INSERT INTO images (name, data_base64) 
VALUES ('logo.png', TO_BASE64(LOAD_FILE('/path/to/logo.png')));

-- Retrieve and decode image
SELECT name, FROM_BASE64(data_base64) AS image_data 
FROM images WHERE name = 'logo.png';

-- Update existing BLOB to Base64
UPDATE images 
SET data_base64 = TO_BASE64(image_blob) 
WHERE id = 1;

-- Convert Base64 back to BLOB
UPDATE images 
SET image_blob = FROM_BASE64(data_base64) 
WHERE id = 1;

URL 安全的 Base64

-- URL-safe Base64 encoding (manual implementation)
SELECT REPLACE(REPLACE(REPLACE(TO_BASE64('Hello World'), '+', '-'), '/', '_'), '=', '') 
AS url_safe_base64;
-- Result: SGVsbG8gV29ybGQ

-- URL-safe Base64 decoding (manual implementation)
SELECT FROM_BASE64(
    REPLACE(REPLACE(REPLACE('SGVsbG8gV29ybGQ', '-', '+'), '_', '/'), '', '=' || '')
) AS decoded;
-- Result: Hello World

-- Using in WHERE clause
SELECT * FROM users 
WHERE token = TO_BASE64(CONCAT(user_id, ':', timestamp));

SQL Server 的 Base64 函数

基础编码/解码

-- Encode string to Base64 (SQL Server 2016+)
SELECT CAST('Hello, World!' AS VARBINARY(MAX)) FOR XML PATH(''), BINARY BASE64;
-- Alternative method
SELECT CONVERT(VARCHAR(MAX), CAST('Hello, World!' AS VARBINARY(MAX)), 1);

-- Decode Base64 to string
SELECT CAST(CAST('SGVsbG8sIFdvcmxkIQ==' AS XML).value('.', 'VARBINARY(MAX)') AS VARCHAR(MAX));

-- Using FOR XML PATH for encoding
SELECT (SELECT 'Hello, World!' FOR XML PATH(''), BINARY BASE64) AS encoded;

-- Custom Base64 functions (SQL Server 2008+)
CREATE FUNCTION dbo.Base64Encode(@input VARBINARY(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
    RETURN CAST(@input AS VARCHAR(MAX)) FOR XML PATH(''), BINARY BASE64
END;

处理 VARBINARY 数据

-- Store image as Base64
INSERT INTO images (name, data_base64) 
SELECT 'logo.png', 
       CAST(image_data AS VARCHAR(MAX)) FOR XML PATH(''), BINARY BASE64
FROM (SELECT * FROM OPENROWSET(BULK 'C:\logo.png', SINGLE_BLOB) AS x) AS img;

-- Retrieve and decode image
SELECT name, 
       CAST(CAST(data_base64 AS XML).value('.', 'VARBINARY(MAX)') AS VARBINARY(MAX)) AS image_data
FROM images WHERE name = 'logo.png';

-- Update existing VARBINARY to Base64
UPDATE images 
SET data_base64 = CAST(image_data AS VARCHAR(MAX)) FOR XML PATH(''), BINARY BASE64
WHERE id = 1;

高级 Base64 操作

-- Create Base64 encoding function
CREATE FUNCTION dbo.Base64Encode(@input VARBINARY(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @result VARCHAR(MAX)
    SET @result = CAST(@input AS VARCHAR(MAX)) FOR XML PATH(''), BINARY BASE64
    RETURN @result
END;

-- Create Base64 decoding function
CREATE FUNCTION dbo.Base64Decode(@input VARCHAR(MAX))
RETURNS VARBINARY(MAX)
AS
BEGIN
    DECLARE @result VARBINARY(MAX)
    SET @result = CAST(CAST(@input AS XML).value('.', 'VARBINARY(MAX)') AS VARBINARY(MAX))
    RETURN @result
END;

-- Usage examples
SELECT dbo.Base64Encode(CAST('Hello' AS VARBINARY(MAX))) AS encoded;
SELECT CAST(dbo.Base64Decode('SGVsbG8=') AS VARCHAR(MAX)) AS decoded;

PostgreSQL 的 Base64 函数

基础编码/解码

-- Encode string to Base64
SELECT encode('Hello, World!'::bytea, 'base64') AS encoded;
-- Result: SGVsbG8sIFdvcmxkIQ==

-- Decode Base64 to string
SELECT convert_from(decode('SGVsbG8sIFdvcmxkIQ==', 'base64'), 'UTF8') AS decoded;
-- Result: Hello, World!

-- Encode binary data
SELECT encode('Hello'::bytea, 'base64') AS binary_encoded;
-- Result: SGVsbG8=

-- Decode to binary
SELECT decode('SGVsbG8=', 'base64') AS binary_decoded;
-- Result: \x48656c6c6f

处理 BYTEA 数据

-- Store image as Base64
INSERT INTO images (name, data_base64) 
VALUES ('logo.png', encode(lo_get(lo_import('/path/to/logo.png')), 'base64'));

-- Retrieve and decode image
SELECT name, decode(data_base64, 'base64') AS image_data 
FROM images WHERE name = 'logo.png';

-- Update existing BYTEA to Base64
UPDATE images 
SET data_base64 = encode(image_data, 'base64') 
WHERE id = 1;

-- Convert Base64 back to BYTEA
UPDATE images 
SET image_data = decode(data_base64, 'base64') 
WHERE id = 1;

URL 安全的 Base64

-- URL-safe Base64 encoding
SELECT replace(replace(replace(encode('Hello World'::bytea, 'base64'), '+', '-'), '/', '_'), '=', '') 
AS url_safe_base64;
-- Result: SGVsbG8gV29ybGQ

-- URL-safe Base64 decoding
SELECT convert_from(decode(
    replace(replace(replace('SGVsbG8gV29ybGQ', '-', '+'), '_', '/'), '', '=' || ''),
    'base64'
), 'UTF8') AS decoded;
-- Result: Hello World

-- Using in WHERE clause
SELECT * FROM users 
WHERE token = encode(concat(user_id, ':', timestamp)::bytea, 'base64');

Oracle 的 Base64 函数

基础编码/解码

-- Encode string to Base64
SELECT UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW('Hello, World!')) AS encoded FROM DUAL;
-- Result: SGVsbG8sIFdvcmxkIQ==

-- Decode Base64 to string
SELECT UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_DECODE('SGVsbG8sIFdvcmxkIQ==')) AS decoded FROM DUAL;
-- Result: Hello, World!

-- Encode binary data
SELECT UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW('Hello')) AS binary_encoded FROM DUAL;
-- Result: SGVsbG8=

-- Decode to binary
SELECT UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_DECODE('SGVsbG8=')) AS binary_decoded FROM DUAL;
-- Result: Hello

处理 BLOB 数据

-- Store image as Base64
INSERT INTO images (name, data_base64) 
VALUES ('logo.png', UTL_ENCODE.BASE64_ENCODE(image_blob));

-- Retrieve and decode image
SELECT name, UTL_ENCODE.BASE64_DECODE(data_base64) AS image_data 
FROM images WHERE name = 'logo.png';

-- Update existing BLOB to Base64
UPDATE images 
SET data_base64 = UTL_ENCODE.BASE64_ENCODE(image_blob) 
WHERE id = 1;

-- Convert Base64 back to BLOB
UPDATE images 
SET image_blob = UTL_ENCODE.BASE64_DECODE(data_base64) 
WHERE id = 1;

进阶操作

-- Create custom Base64 functions
CREATE OR REPLACE FUNCTION base64_encode(p_input VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
    RETURN UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW(p_input));
END;

CREATE OR REPLACE FUNCTION base64_decode(p_input VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
    RETURN UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_DECODE(p_input));
END;

-- Usage examples
SELECT base64_encode('Hello World') AS encoded FROM DUAL;
SELECT base64_decode('SGVsbG8gV29ybGQ=') AS decoded FROM DUAL;

其他数据库系统

SQLite 的 Base64

-- SQLite doesn't have built-in Base64 functions
-- You need to use extensions or custom implementations

-- Using base64 extension (if available)
SELECT base64('Hello, World!') AS encoded;
SELECT base64_decode('SGVsbG8sIFdvcmxkIQ==') AS decoded;

-- Manual Base64 implementation (simplified)
-- This is a basic example - full implementation would be more complex
CREATE TABLE base64_chars (
    val INTEGER PRIMARY KEY,
    char TEXT
);

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

MariaDB 的 Base64

-- MariaDB has the same Base64 functions as MySQL
-- TO_BASE64() and FROM_BASE64() functions

-- Encode string to Base64
SELECT TO_BASE64('Hello, World!') AS encoded;
-- Result: SGVsbG8sIFdvcmxkIQ==

-- Decode Base64 to string
SELECT FROM_BASE64('SGVsbG8sIFdvcmxkIQ==') AS decoded;
-- Result: Hello, World!

-- Working with BLOB data
INSERT INTO images (name, data_base64) 
VALUES ('logo.png', TO_BASE64(LOAD_FILE('/path/to/logo.png')));

-- Retrieve and decode
SELECT name, FROM_BASE64(data_base64) AS image_data 
FROM images WHERE name = 'logo.png';

DB2 的 Base64

-- DB2 Base64 functions
-- Encode string to Base64
SELECT BASE64('Hello, World!') AS encoded FROM SYSIBM.SYSDUMMY1;
-- Result: SGVsbG8sIFdvcmxkIQ==

-- Decode Base64 to string
SELECT BASE64_DECODE('SGVsbG8sIFdvcmxkIQ==') AS decoded FROM SYSIBM.SYSDUMMY1;
-- Result: Hello, World!

-- Working with BLOB data
INSERT INTO images (name, data_base64) 
VALUES ('logo.png', BASE64(image_blob));

-- Retrieve and decode
SELECT name, BASE64_DECODE(data_base64) AS image_data 
FROM images WHERE name = 'logo.png';

最佳实践