MySQL加密CRUD操作技术咨询：列加密、数据类型及密钥配置

嘿，针对你提出的MySQL加密CRUD相关问题，我结合C/C++连接器的使用场景，给你逐一拆解说明，再附上可直接参考的代码示例：

1. 整行所有列加密存储的CRUD实现思路 #

整行加密的核心是把每行的所有字段都做加密处理，操作逻辑统一遵循「加密后写入，读取后解密」的原则：

• 插入（Create）：把每个字段的明文值转换成字节流，用密钥加密后，再插入到对应的列中（注意列类型要适配加密后的二进制或Base64编码值）。
• 查询（Read）：从数据库取出加密后的列值，用密钥解密成明文，再转换成对应的数据类型使用。
• 更新（Update）：先查询出目标行的加密值（或用加密后的条件匹配），对需要修改的字段重新加密后执行更新。
• 删除（Delete）：如果是基于字段条件删除，需要把查询条件的明文加密，用加密后的值作为WHERE条件匹配行，再执行删除。

注意：整行加密后，无法对单个字段做模糊查询（比如LIKE），因为加密后的值是无序的，只能做等值匹配。

2. 仅指定列加密存储的CRUD实现思路 #

这个是日常开发中更常用的场景（比如只加密手机号、身份证号这类敏感字段），逻辑和整行加密类似，但只针对指定字段处理：

• 插入：仅对敏感字段做加密，其他字段直接明文插入。
• 查询：取出数据后，只解密指定的敏感字段，其他字段直接使用。
• 更新：仅对要修改的敏感字段重新加密后更新，非敏感字段正常处理。
• 删除：如果条件涉及敏感字段，同样把明文条件加密后作为WHERE子句的匹配值；非敏感字段的条件直接用明文即可。

3. 加密列的类型选择：必须用BLOB吗？ #

答案是不需要强制用BLOB，具体选择取决于你加密后的存储方式：

• BLOB/TEXT类型：适合直接存储加密后的二进制字节流，节省存储空间，但无法直接用字符串类的SQL函数操作。
• VARCHAR类型：如果把加密后的二进制字节流做Base64编码（转成字符串），就可以存在VARCHAR里，好处是可以直接看存储的内容（虽然是乱码），但会增加约30%的存储空间。
• INT等数值类型：不适合存储加密数据，因为加密后的结果是字节流，无法直接映射为有效的数值类型，强行转换会导致数据损坏或解密失败。

建议优先选择BLOB存储二进制加密结果，对需要字符串兼容的场景用Base64+VARCHAR。

4. 加密密钥的存储位置（硬编码的注意事项） #

你计划把密钥硬编码在程序里是可行的，但要注意安全风险：一旦代码泄露（比如开源、编译后的二进制被逆向），密钥就会直接暴露。如果必须硬编码，建议：

• 不要用明文字符串直接写在代码里，比如可以把密钥拆分成多个片段，运行时再拼接；
• 用宏定义或者const数组存储，编译时尽量做代码混淆；
• 更安全的替代方案是用环境变量、加密的配置文件，或者专门的密钥管理服务，但如果你的场景要求硬编码，也可以按需求来。

C语言（MySQL Connector/C）示例代码 #

下面以AES-256-CBC对称加密为例，实现指定列加密的CRUD操作：

#include <mysql/mysql.h>
#include <openssl/evp.h>
#include <string.h>
#include <stdio.h>

// 硬编码密钥（注意：实际生产环境请谨慎处理）
const unsigned char AES_KEY[] = "your-32-byte-secret-key-here"; // AES-256需要32字节密钥
const unsigned char AES_IV[] = "your-16-byte-iv-here";          // CBC模式需要16字节IV

// 加密函数：明文 -> 加密后的二进制数据
int encrypt(unsigned char *plaintext, int plaintext_len, unsigned char *key,
            unsigned char *iv, unsigned char *ciphertext) {
    EVP_CIPHER_CTX *ctx;
    int len, ciphertext_len;

    if(!(ctx = EVP_CIPHER_CTX_new())) return -1;
    if(1 != EVP_EncryptInit_ex(ctx, EVP_aes_256_cbc(), NULL, key, iv)) return -1;
    if(1 != EVP_EncryptUpdate(ctx, ciphertext, &len, plaintext, plaintext_len)) return -1;
    ciphertext_len = len;
    if(1 != EVP_EncryptFinal_ex(ctx, ciphertext + len, &len)) return -1;
    ciphertext_len += len;

    EVP_CIPHER_CTX_free(ctx);
    return ciphertext_len;
}

// 解密函数：加密后的二进制数据 -> 明文
int decrypt(unsigned char *ciphertext, int ciphertext_len, unsigned char *key,
            unsigned char *iv, unsigned char *plaintext) {
    EVP_CIPHER_CTX *ctx;
    int len, plaintext_len;

    if(!(ctx = EVP_CIPHER_CTX_new())) return -1;
    if(1 != EVP_DecryptInit_ex(ctx, EVP_aes_256_cbc(), NULL, key, iv)) return -1;
    if(1 != EVP_DecryptUpdate(ctx, plaintext, &len, ciphertext, ciphertext_len)) return -1;
    plaintext_len = len;
    if(1 != EVP_DecryptFinal_ex(ctx, plaintext + len, &len)) return -1;
    plaintext_len += len;

    EVP_CIPHER_CTX_free(ctx);
    return plaintext_len;
}

int main() {
    MYSQL *conn;
    MYSQL_RES *res;
    MYSQL_ROW row;
    char query[1024];
    unsigned char plaintext[] = "13800138000"; // 要加密的手机号
    unsigned char ciphertext[1024];
    unsigned char decrypted[1024];
    int cipher_len;

    // 初始化MySQL连接
    conn = mysql_init(NULL);
    if (!mysql_real_connect(conn, "localhost", "user", "password", "test_db", 0, NULL, 0)) {
        fprintf(stderr, "%s\n", mysql_error(conn));
        return 1;
    }

    // 1. 创建测试表：指定phone列为BLOB存储加密值
    snprintf(query, sizeof(query), "CREATE TABLE IF NOT EXISTS users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50), phone BLOB)");
    if (mysql_query(conn, query)) {
        fprintf(stderr, "%s\n", mysql_error(conn));
        return 1;
    }

    // 2. 插入数据：加密phone字段
    cipher_len = encrypt(plaintext, strlen((char*)plaintext), AES_KEY, AES_IV, ciphertext);
    if (cipher_len == -1) {
        fprintf(stderr, "Encryption failed\n");
        return 1;
    }
    // 用mysql_real_escape_string处理二进制数据，避免SQL注入
    unsigned char escaped_cipher[2048];
    mysql_real_escape_string(conn, (char*)escaped_cipher, (char*)ciphertext, cipher_len);
    snprintf(query, sizeof(query), "INSERT INTO users (name, phone) VALUES ('Alice', '%s')", escaped_cipher);
    if (mysql_query(conn, query)) {
        fprintf(stderr, "%s\n", mysql_error(conn));
        return 1;
    }
    printf("Data inserted successfully\n");

    // 3. 查询数据：解密phone字段
    snprintf(query, sizeof(query), "SELECT id, name, phone FROM users WHERE name = 'Alice'");
    if (mysql_query(conn, query)) {
        fprintf(stderr, "%s\n", mysql_error(conn));
        return 1;
    }
    res = mysql_store_result(conn);
    if ((row = mysql_fetch_row(res)) != NULL) {
        int phone_len = mysql_fetch_lengths(res)[2];
        int decrypted_len = decrypt((unsigned char*)row[2], phone_len, AES_KEY, AES_IV, decrypted);
        decrypted[decrypted_len] = '\0'; // 添加字符串结束符
        printf("ID: %s, Name: %s, Decrypted Phone: %s\n", row[0], row[1], decrypted);
    }
    mysql_free_result(res);

    // 4. 更新数据：重新加密phone字段
    unsigned char new_plaintext[] = "13900139000";
    cipher_len = encrypt(new_plaintext, strlen((char*)new_plaintext), AES_KEY, AES_IV, ciphertext);
    mysql_real_escape_string(conn, (char*)escaped_cipher, (char*)ciphertext, cipher_len);
    snprintf(query, sizeof(query), "UPDATE users SET phone = '%s' WHERE name = 'Alice'", escaped_cipher);
    if (mysql_query(conn, query)) {
        fprintf(stderr, "%s\n", mysql_error(conn));
        return 1;
    }
    printf("Data updated successfully\n");

    // 5. 删除数据
    snprintf(query, sizeof(query), "DELETE FROM users WHERE name = 'Alice'");
    if (mysql_query(conn, query)) {
        fprintf(stderr, "%s\n", mysql_error(conn));
        return 1;
    }
    printf("Data deleted successfully\n");

    // 关闭连接
    mysql_close(conn);
    return 0;
}

编译时需要链接MySQL和OpenSSL库：gcc mysql_crypto.c -o mysql_crypto $(mysql_config --cflags --libs) -lcrypto

C语言（MySQL Connector/C）示例代码 #

如果切换到C++连接器，逻辑类似，用面向对象的方式操作数据库：

#include <mysqlx/xdevapi.h>
#include <openssl/evp.h>
#include <string>
#include <vector>
#include <iostream>

using namespace mysqlx;
using namespace std;

// 硬编码密钥
const unsigned char AES_KEY[] = "your-32-byte-secret-key-here";
const unsigned char AES_IV[] = "your-16-byte-iv-here";

// 加密函数：返回加密后的二进制字符串
string encrypt(const string &plaintext) {
    EVP_CIPHER_CTX *ctx = EVP_CIPHER_CTX_new();
    if (!ctx) throw runtime_error("Failed to create cipher context");

    vector<unsigned char> ciphertext(plaintext.size() + EVP_CIPHER_block_size(EVP_aes_256_cbc()));
    int len, cipher_len;

    if (!EVP_EncryptInit_ex(ctx, EVP_aes_256_cbc(), nullptr, AES_KEY, AES_IV)) {
        EVP_CIPHER_CTX_free(ctx);
        throw runtime_error("Encryption init failed");
    }
    if (!EVP_EncryptUpdate(ctx, ciphertext.data(), &len, reinterpret_cast<const unsigned char*>(plaintext.c_str()), plaintext.size())) {
        EVP_CIPHER_CTX_free(ctx);
        throw runtime_error("Encryption update failed");
    }
    cipher_len = len;
    if (!EVP_EncryptFinal_ex(ctx, ciphertext.data() + len, &len)) {
        EVP_CIPHER_CTX_free(ctx);
        throw runtime_error("Encryption final failed");
    }
    cipher_len += len;

    EVP_CIPHER_CTX_free(ctx);
    return string(reinterpret_cast<char*>(ciphertext.data()), cipher_len);
}

// 解密函数：返回明文
string decrypt(const string &ciphertext) {
    EVP_CIPHER_CTX *ctx = EVP_CIPHER_CTX_new();
    if (!ctx) throw runtime_error("Failed to create cipher context");

    vector<unsigned char> plaintext(ciphertext.size());
    int len, plain_len;

    if (!EVP_DecryptInit_ex(ctx, EVP_aes_256_cbc(), nullptr, AES_KEY, AES_IV)) {
        EVP_CIPHER_CTX_free(ctx);
        throw runtime_error("Decryption init failed");
    }
    if (!EVP_DecryptUpdate(ctx, plaintext.data(), &len, reinterpret_cast<const unsigned char*>(ciphertext.c_str()), ciphertext.size())) {
        EVP_CIPHER_CTX_free(ctx);
        throw runtime_error("Decryption update failed");
    }
    plain_len = len;
    if (!EVP_DecryptFinal_ex(ctx, plaintext.data() + len, &len)) {
        EVP_CIPHER_CTX_free(ctx);
        throw runtime_error("Decryption final failed");
    }
    plain_len += len;

    EVP_CIPHER_CTX_free(ctx);
    return string(reinterpret_cast<char*>(plaintext.data()), plain_len);
}

int main() {
    try {
        // 连接MySQL服务器
        Session sess("localhost", 33060, "user", "password");
        cout << "Connected to MySQL server" << endl;

        // 选择数据库
        sess.sql("USE test_db").execute();

        // 创建测试表
        sess.sql("CREATE TABLE IF NOT EXISTS users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50), phone BLOB)").execute();

        // 插入数据
        string plain_phone = "13800138000";
        string encrypted_phone = encrypt(plain_phone);
        sess.sql("INSERT INTO users (name, phone) VALUES (?, ?)")
            .bind("Alice", encrypted_phone)
            .execute();
        cout << "Data inserted successfully" << endl;

        // 查询数据
        RowResult res = sess.sql("SELECT id, name, phone FROM users WHERE name = ?").bind("Alice").execute();
        Row row = res.fetchOne();
        if (row) {
            string cipher_phone = row[2].get<string>();
            string decrypted_phone = decrypt(cipher_phone);
            cout << "ID: " << row[0].get<int>() << ", Name: " << row[1].get<string>() << ", Decrypted Phone: " << decrypted_phone << endl;
        }

        // 更新数据
        string new_plain_phone = "13900139000";
        string new_encrypted_phone = encrypt(new_plain_phone);
        sess.sql("UPDATE users SET phone = ? WHERE name = ?")
            .bind(new_encrypted_phone, "Alice")
            .execute();
        cout << "Data updated successfully" << endl;

        // 删除数据
        sess.sql("DELETE FROM users WHERE name = ?").bind("Alice").execute();
        cout << "Data deleted successfully" << endl;

        sess.close();
    } catch (const mysqlx::Error &e) {
        cerr << "MySQL Error: " << e.what() << endl;
        return 1;
    } catch (const runtime_error &e) {
        cerr << "Crypto Error: " << e.what() << endl;
        return 1;
    }

    return 0;
}

编译时需要链接MySQL Connector/C++和OpenSSL库：g++ mysql_crypto.cpp -o mysql_crypto -lmysqlcppconn8 -lcrypto

内容的提问来源于stack exchange，提问作者Kumar Roshan Mehta