如何加密 MySQL 表中的特定列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4275882/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to encrypt a specific column in a MySQL table?
提问by BeeDog
I am experimenting with creating a simple message system (PHP) page that uses a MySQL table to store the entries. The rough outline of the columns I'll use in the table are:
我正在尝试创建一个简单的消息系统 (PHP) 页面，该页面使用 MySQL 表来存储条目。我将在表格中使用的列的粗略轮廓是：
msg_id(primary key, auto_increment)
msg_id（主键，auto_increment）
user_id(foreign key pointing to the user who created the message)
user_id（指向创建消息的用户的外键）
time(a DATETIME entry to provide msg timestamps)
时间（提供 msg 时间戳的 DATETIME 条目）
msg(a VARCHAR containing the msg)
msg（包含 msg 的 VARCHAR）
accessable(just an int(1), 0 means no one except the user himself can read the msg, and 1 means others can read it)
可访问（只是一个 int(1)，0 表示除了用户自己之外没有人可以读取 msg，1 表示其他人可以读取它）
What I'm wondering is, what's the best way to encrypt the msgfield so prying eyes can't read it (let's say, by opening the mysql CLI or phpMyAdmin and just read the value stored in a row)?
我想知道的是，加密msg字段以便窥探者无法读取它的最佳方法是什么（比方说，通过打开 mysql CLI 或 phpMyAdmin 并读取连续存储的值）？
If "accessable" is set to 0, then only the user him/herself should be able to read it (by accessing some PHP page), but if set to 1, everyone else should be able to read it as well. I don't know how to tackle this, so any help is very appreciated!
如果“accessable”设置为 0，那么只有用户他/她自己应该能够阅读它（通过访问某个 PHP 页面），但如果设置为 1，其他人也应该能够阅读它。我不知道如何解决这个问题，所以非常感谢任何帮助！
回答by rMX
Look here for list of possible encryption functions:
在此处查看可能的加密功能列表：
http://dev.mysql.com/doc/refman/5.1/en/encryption-functions.html
http://dev.mysql.com/doc/refman/5.1/en/encryption-functions.html
You can create trigger for update and check there field accessable. Something like that:
您可以为更新创建触发器并检查那里的字段accessable。类似的东西：
CREATE TRIGGER crypt_trg BEFORE UPDATE ON table FOR EACH ROW
BEGIN
IF new.accessable = 0 THEN
SET new.msg := ENCRYPT(new.msg, 'key');
ELSE
SET new.msg := DECRYPT(new.msg, 'key');
END IF;
END;
You also can update all existing records in you table with this query:
您还可以使用此查询更新表中的所有现有记录：
UPDATE table SET msg = IF(accessable = 0, ENCRYPT(msg, 'key'), DECRYPT(msg, 'key'));
So you can select records for you PHP code:
所以你可以为你的 PHP 代码选择记录：
SELECT msg_id, user_id, time, IF(accessable = 0, DECRYPT(msg, 'key'), msg) msg
FROM table
UPD.Also here was similar question:
更新。这里也有类似的问题：
回答by Core Xii
You can also encrypt the data prior to the query to insert it, so that MySQL doesn't even knowit's encrypted, and decrypt it on retrieval in the application. For that, you should store it in a varbinary or blob column though.
您还可以在查询之前对数据进行加密以将其插入，这样 MySQL 甚至不知道它已加密，并在应用程序中检索时对其进行解密。为此，您应该将其存储在 varbinary 或 blob 列中。
