mysql - 第 1 行 : Column cannot be null 处的错误 1048 (23000)

Question

我有一个名为“crea”的数据库和一个名为“assets”的表，在这个表中我有 11 列名为 (name,description,assetType,local,temporary,data,id,create_time,access_time,asset_flags,CreatorID)

我还有一个目录文件夹，里面有很多.jp2格式的图片(xxxx.jp2)

我想做的是将这些图片批量插入到我的数据库的“assets”表中，所以我决定用 2 个 shell 脚本来完成，它们都在包含图片的目录中。

当我从终端启动 ./assetadd.sh 时，我从 MySQL 收到此错误:

ERROR 1048 (23000) at line 1: Column 'data' cannot be null

我检查了很多次，我确定“数据”列不是 NULL(类型:LONGBLOB Binaries Null:NO)，所以我真的不明白为什么会出现此错误。

帮助将不胜感激。谢谢

- 脚本 1:assetsadd.sh

#!/bin/bash

path=$(pwd)
find $path/ -type f \( -iname *.jp2 \) -exec ./insertjp2.sh {} \;

echo "finished!!"

- 脚本 2:insertjp2.sh

#!/bin/bash

user="crea"
password="crea"
database="crea"
dbhost="localhost"
creator="crea"

param=$@
basenam=${param##*/}
filenam=${basenam%.*}

MYSQL=`/usr/bin/mysql -u$user -p$password -D$database -e"INSERT INTO assets (name,description,assetType,local,temporary,data,id,create_time,access_time​,asset_flags,CreatorID) VALUES ('$filenam','$filenam',0,0,0,LOAD_FILE('$param'),'$filenam',UNIX_TIMESTAMP(),1325304546,0,'$creator' );"`

echo $param >> assetadd.log
echo $MYSQL

Answer 1

来自documentation for LOAD_FILE :

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.

至少看起来，您没有指定文件名的完整路径。