mysql加密函數aes_encrypt()和aes_decrypt()使用教程

如果你需要對mysql某些欄位進行加解密的話,使用mysql的加解密函數可能比程式中處理更方便.
mysql-encrypt-funcs.png以aes_encrypt()和aes_decrypt()為例

特別需要注意的時mysql5.5及以下的版本僅支援aes-128-ecb模式，如果需要其它模式需要mysql5.6及以上版本才支援，可通過mysql全域變數如下方式指定：

mysql> SET block_encryption_mode = 'aes-256-cbc';
mysql> SET @key_str = SHA2('My secret passphrase',512);
mysql> SET @init_vector = RANDOM_BYTES(16);
mysql> SET @crypt_str = AES_ENCRYPT('text',@key_str,@init_vector);
mysql> SELECT AES_DECRYPT(@crypt_str,@key_str,@init_vector);
+-----------------------------------------------+
| AES_DECRYPT(@crypt_str,@key_str,@init_vector) |
+-----------------------------------------------+
| text                                          |
+-----------------------------------------------+

參考文檔如下：

https://dev.mysql.com/doc/refman/5.5/en/encryption-functions.html#function_aes-encrypt
http://dev.mysql.com/doc/refman/5.7/en/encryption-functions.html#function_aes-encrypt

關於加密的位元據在mysql中欄位存什麼類型(存blob還是varbinay類型)？
引用文檔一段話：

Many encryption and compression functions return strings for which the result might contain arbitrary byte values. If you want to store these results, use a column with a VARBINARY or BLOB binary string data type. This will avoid potential problems with trailing space removal or character set conversion that would change data values, such as may occur if you use a nonbinary string data type (CHAR, VARCHAR, TEXT).

盡量使用blob類型，原因如下：

There is no trailing-space removal for BLOB columns when values are stored or retrieved.
For indexes on BLOB columns, you must specify an index prefix length.
BLOB columns can not have DEFAULT values.

位元據如何使用sql插入？

不可直接拼接sql插入，否則會被當成字串處理，不可你可以將位元據轉換程十六進位或base64插入，相應的，取出來的時候你也需要轉換。但是通過mysql prepared statement方式可以插入stream data，如php pdo可以類似如下實現：

$db = new PDO('odbc:SAMPLE', 'db2inst1', 'ibmdb2');
$stmt = $db->prepare("insert into images (id, contenttype, imagedata) values (?, ?, ?)");
$id = get_new_id(); // some function to allocate a new ID
 
// assume that we are running as part of a file upload form
// You can find more information in the PHP documentation
 
$fp = fopen($_FILES['file']['tmp_name'], 'rb');
 
$stmt->bindParam(1, $id);
$stmt->bindParam(2, $_FILES['file']['type']);
$stmt->bindParam(3, $fp, PDO::PARAM_LOB);
 
$db->beginTransaction();
$stmt->execute();
$db->commit();