PHP-使用准备好的语句在mysql中插入二进制数据 [英] PHP- inserting binary data in mysql using prepared statements

问题描述

我必须使用php的mysql改进库向mysql中具有主键类型为VARBINARY的表中插入一行. 该字段的内容是计算得出的sha1哈希.

I have to insert a row using php's mysql improved library to a table in mysql that has its primary key of type VARBINARY. The contents of this field are a computed sha1 hash.

如果我以旧的方式运行查询，则查询效果会很好:

If I run the query the old way it works perfectly:

$mysqli->$query("INSERT INTO table (id, field1) VALUES (0x" . $id . ",'" . $field1 . "')");

但是当我尝试将其作为准备好的语句执行时，我不知道该怎么做.如果 我执行了等效的操作:

But when I try to execute it as a prepared statement, I can't figure out how to do it. If I perform the equivalent action:

if($stmt = $mysqli->prepare("INSERT INTO table (id, field1) VALUES (?, ?)")) {
    $stmt->bind_param('ss', "0x".$id, $field1);
    //execute statement
}

引发异常，表明该字段的内容太大.而且，如果我尝试将其作为BLOB字段插入:

It throws an exception saying that the contents were too large for this field. And If I try to insert it as a BLOB field:

if($stmt = $mysqli->prepare("INSERT INTO table (id, field1) VALUES (?, ?)")) {
    $stmt->bind_param('bs', $id, $field1);
    //execute statement
}

它没有错误，已插入行，但是标识符字段现在为空(不为null，为空).

It gives no error, the row is inserted, but the identifier field now is empty (not null, empty).

我知道我可以混合查询并输入字符串和其他字段中串联的id作为准备好的语句的绑定参数，但是我只是想知道什么是正确的插入方式，也许它将将来帮助别人.

I know I can mix the query and input the id concatenated in the string and the other fields as bind parameters of the prepared statement, but i'm asking just to know what is the correct way to insert this and perhaps it will help somebody in the future.

推荐答案

PHP的sha1函数返回一个十六进制数字的字符串表示形式.

PHP's sha1 function returns a string representation of a hex number.

这意味着，如果将其打印到屏幕上，它将显示一个十六进制数字.但是在内存中，它是一堆ASCII字符.

What that means is that if you print it to screen, it'll display a hex number. But in memory, it is an bunch of ASCII characters.

因此，请使用十六进制数字1A2F.作为内存中的ASCII，将是0x31413246，而不是0x1A2F

So, take the hex number 1A2F. As ASCII in memory that would be 0x31413246, instead of 0x1A2F

MySQL的普通接口将所有参数作为字符串发送.使用普通接口时，MySQL会将ASCII字符串转换为二进制值.

MySQL's normal interface sends all arguments as strings. When using the normal interface, MySQL will convert the ASCII string to a binary value.

新的prepare语句方法将所有内容都以二进制形式发送.因此，您的值"1A2F"现在将以0x31413246的形式发送并插入到该列中. -来源:dev.mysql.com-已准备声明

The new prepared statement method sends everything as binary. So your nice value of "1A2F" will now be sent as 0x31413246 and inserted into the column. - source: dev.mysql.com - Prepared statements

相反，使用以下命令将您的十六进制字符串打包为二进制字符串:

Instead, convert your Hex string by packing it into a binary string using:

$binId = pack("H*", $id); // this string is not ASCII, don't print it to the screen! That will be ugly.

，然后将$binId传递给MySQLi准备的语句，而不是$ id.

and then pass $binId to the MySQLi prepared statement instead of $id.

这篇关于PHP-使用准备好的语句在mysql中插入二进制数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！