生成包含 BLOB 图像数据的 MySQL UPDATE 语句

发布于 2024-08-28 10:43:57 字数 911 浏览 9 评论 0原文

我正在尝试编写一个 SQL 语句，该语句将生成一个 SQL 脚本，该脚本将使用从数据库中选择的 IMAGE 来更新 BLOB 字段。

这就是我所拥有的：

select concat( 'UPDATE `IMAGE` SET THUMBNAIL = ', 
               QUOTE( THUMBNAIL ), 
               ' WHERE ID = ', ID, ';' ) as UPDATE_STATEMENT
  from IMAGE;

在上面，THUMBNAIL 是一个包含原始图像数据的 BLOB 字段。当我运行生成的脚本时，出现以下错误：

ERROR at line 2: Unknown command '\\'.

我首先在没有 QUOTE() 函数的情况下尝试此操作，如下所示：

select concat( 'UPDATE `IMAGE` SET THUMBNAIL = \'', 
               THUMBNAIL, 
               '\' WHERE ID = ', ID, ';' ) as UPDATE_STATEMENT
  from IMAGE;

运行生成的脚本会产生此错误：

ERROR at line 2: Unknown command '\0'.

What is theproper function to apply to this BLOB field in the select，所以UPDATE 语句会起作用吗？

如果需要上下文，我希望仅针对某些图像 ID 将一台服务器上生成的缩略图迁移到另一台服务器。我会使用 mysqldump，但我不想破坏整个表。

非常感谢任何帮助！

原文

I'm trying to write an SQL statement that will generate an SQL script that will update a BLOB field with an IMAGE being selected from the database.

This is what I have:

select concat( 'UPDATE `IMAGE` SET THUMBNAIL = ', 
               QUOTE( THUMBNAIL ), 
               ' WHERE ID = ', ID, ';' ) as UPDATE_STATEMENT
  from IMAGE;

In the above, THUMBNAIL is a BLOB field containing raw image data. When I run the resulting script I get the following error:

ERROR at line 2: Unknown command '\\'.

I first tried this without the QUOTE() function, like so:

select concat( 'UPDATE `IMAGE` SET THUMBNAIL = \'', 
               THUMBNAIL, 
               '\' WHERE ID = ', ID, ';' ) as UPDATE_STATEMENT
  from IMAGE;

Running the resulting script produces this error:

ERROR at line 2: Unknown command '\0'.

What is the proper function to apply to this BLOB field in the select, so the UPDATE statements will work?

If context is required, I'm looking to migrate thumbnails generated on one server to another server for certain image IDs only. I would use mysqldump, but I don't want to clobber the entire table.

Any help is greatly appreciated!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

谈下烟灰 2024-09-04 10:43:58

您必须使用十六进制格式 blob。它看起来像这样：

select concat( 'UPDATE `IMAGE` SET THUMBNAIL = ', 
               0xC9CBBBCCCEB9C8CABCCCCEB9C9CBBB...., 
               ' WHERE ID = ', ID, ';' ) as UPDATE_STATEMENT
  from IMAGE;

或者您可能会发现使用 Openrowset 命令

You must work with hexadecimal format blob. It looks like this:

select concat( 'UPDATE `IMAGE` SET THUMBNAIL = ', 
               0xC9CBBBCCCEB9C8CABCCCCEB9C9CBBB...., 
               ' WHERE ID = ', ID, ';' ) as UPDATE_STATEMENT
  from IMAGE;

Or you might find it handy to use the Openrowset command

回复收藏 0 原文

友谊不毕业 2024-09-04 10:43:58

 <?php if (isset($_POST['sub'])) { 
     $tfname=$_FILES['f1']['tmp_name'];
     $name=$_POST['hi']; 
     $cont=file_get_contents($tfname);
     $cont=addslashes($cont); 
     mysql_connect("localhost" ,"root" ,"");
     mysql_select_db("k"); 
     $sqlstt="update test_image set
         id='2',name='$name',image='$cont' where id='2' ";
     if(mysql_query($sqlstt)) 
         echo "updated"; 
     else 
         echo "not";         
 } ?>

 <form action="" method="post" enctype="multipart/form-data">
     Name:-<input type="text" name="hi" /> 
     select image:-<input type="file" name="f1" required value="select" /> 
     <input type="submit" name="sub" value="update" /> 
 </form>

这很好用，检查一下

 <?php if (isset($_POST['sub'])) { 
     $tfname=$_FILES['f1']['tmp_name'];
     $name=$_POST['hi']; 
     $cont=file_get_contents($tfname);
     $cont=addslashes($cont); 
     mysql_connect("localhost" ,"root" ,"");
     mysql_select_db("k"); 
     $sqlstt="update test_image set
         id='2',name='$name',image='$cont' where id='2' ";
     if(mysql_query($sqlstt)) 
         echo "updated"; 
     else 
         echo "not";         
 } ?>

 <form action="" method="post" enctype="multipart/form-data">
     Name:-<input type="text" name="hi" /> 
     select image:-<input type="file" name="f1" required value="select" /> 
     <input type="submit" name="sub" value="update" /> 
 </form>

this is worked nice check it out

回复收藏 0 原文

~没有更多了~