ivはDBに保存しているが16進数文字列でファイルに記述してもよい
SET block_encryption_mode = 'aes-256-cbc'
SET @key_str = SHA2('XXXXXXX',512)
SET @init_vector = RANDOM_BYTES(16) ※INSERTの時だけ実行
INSERT INTO `table_name`(shop,token,iv,created) VALUES(HEX(AES_ENCRYPT('ABC', @key_str,@init_vector)),HEX(AES_ENCRYPT('DEF', @key_str,@init_vector)),HEX(@init_vector),'2024-05-01 11:12:13')
UPDATE `table_name` SET shop = HEX(AES_ENCRYPT(:shop, @key_str,@init_vector)),token = HEX(AES_ENCRYPT(:token, @key_str,@init_vector)),iv = HEX(@init_vector),modified = '2024-05-01 11:12:13' WHERE id = 5
SELECT convert(AES_DECRYPT(UNHEX(shop), @key_str,UNHEX(iv)) USING utf8) AS shop,convert(AES_DECRYPT(UNHEX(token), @key_str,UNHEX(iv)) USING utf8) AS token,created FROM `table_name` WHERE convert(AES_DECRYPT(UNHEX(shop), @key_str,UNHEX(iv)) USING utf8) = 'ABC'