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

aes_encrypt()和aes_decrypt()在mysql中是進行加密了，我們今天一起來和各位看看關於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();