how to use LOAD_FILE in mysql

最近需要往mysql里存一点小图片，想用mysql的LOAD_FILE来将文件写进去，但总是返回NULL，几经周折发现LOAD_FILE对权限要求比较严格。

参照官网（遇事不决问官网啊）。

LOAD_FILE(file_name) Reads the file and returns the file contents as a string. To use this function, the file must be located on the server host, you must specify the full path name to the file, and you must have the FILE privilege. The file must be readable by all and its size less than max_allowed_packet bytes. If the secure_file_priv system variable is set to a nonempty directory name, the file to be loaded must be located in that directory.

If the file does not exist or cannot be read because one of the preceding conditions is not satisfied, the function returns NULL.

The character_set_filesystem system variable controls interpretation of file names that are given as literal strings.

mysql> UPDATE t
            SET blob_col=LOAD_FILE('/tmp/picture')
            WHERE id=1;
mysql> SELECT LOAD_FILE('/tmp/picture');

要求如下：

必须绝对路径，不允许相对路径
用户必须有该文件的权限
文件大小必须小于max_allowed_packet
如果mysql服务器设置了secure_file_priv系统变量，该文件必须放到secure_file_priv变量指定的目录去

前2个简单，后2个需要看下mysql的变量。

mysql> show variables like 'max_allowed_packet';
+--------------------+---------+
| Variable_name      | Value   |
+--------------------+---------+
| max_allowed_packet | 4194304 |
+--------------------+---------+
1 row in set (0.00 sec)

mysql> show variables like 'secure_file_priv';
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
1 row in set (0.00 sec)

由于都是小文件，max_allowed_packet不会超；但是需要先将文件放到/var/lib/mysql-files/去，否则LOAD_FILE总是返回NULL。

这么严格的权限要求还是比较好理解的，毕竟这些文件是mysql服务器上，如果允许用户随便遍历，安全是个问题。

不过默认的secure_file_priv配置不太友好，权限有点高。AWS的RDS的这个目录是/tmp/，对管理员来说稍好一点，不过结果是一样的，我也没办法去AWS上去操作RDS服务器。

mysql> show variables like 'secure_file_priv';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv | /tmp/ |
+------------------+-------+
1 row in set (0.11 sec)

那么对于mysql client用户来说，如果想方便的上传文件到mysql服务器的表，有没有什么好办法呢？