通过php在Mysql表上插入Varbinary数据

I have a small problem and i'm sure someone have the solution for it I have a binary data converted from a set of unsigned integers

我有一个小问题,我相信有人有解决方案我有一个无符号整数转换的二进制数据

$_BIN=pack('I*', 3563547,6587568,5468456,6458568,4568568);

and i want to insert it in a table that contain a varbinary column

我想将它插入包含varbinary列的表中

$strSQL = "INSERT INTO table_name (id, ba) VALUES($id, $_BIN)";

$ strSQL =“INSERT INTO table_name(id,ba)VALUES($ id,$ _BIN)”;

for some reason i get the following error

由于某种原因,我得到以下错误

Erreur SQL : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '_' at line 1 SQL string : INSERT INTO table_name (id, ba) VALUES(467, ('ùZ¢O ^,¦EòÇjÞ|²EÎóÇjÞ|²EÎóp>)

Erreur SQL:SQL语法中有错误;查看与MySQL服务器版本对应的手册,以便在第1行的“_”附近使用正确的语法SQL字符串:INSERT INTO table_name(id,ba)VALUES(467,('ùZ¢O ^,|EòÇjÞ|EEÎóÇjÞ| ²EÎóp>)

Hoping you guys can help me out

希望你们能帮助我

1 个解决方案

#1

This works for me:

这对我有用:

DROP TABLE IF EXISTS `binary`;
CREATE TABLE `binary`(
id INT NOT NULL AUTO_INCREMENT, 
ba VARBINARY( 100 ),
PRIMARY KEY(`id`)
) ENGINE=MYISAM;

and PHP code (using PDO):

和PHP代码(使用PDO):

<?php
$db = new PDO('mysql:dbname=test;host=localhost:4040','xxx','xxxx');

$_BIN=pack('I*', 3563547,6587568,5468456,6458568,4568568);

$rs = $db->prepare("INSERT INTO `binary`(`ba`) VALUES(?)");

$rs->execute(array($_BIN));

UPDATE

See below the code based on your suggested one with named parameters:

请参阅下面的代码,基于您建议的具有命名参数的代码:

<?php
$_SNB = 23;
$_USR = "toto";
$_BIN = pack('I*', 24325, 2556456, 547577, 675746, 535646, 4564575, 575474, 4735);

$db = new PDO('mysql:host=localhost:4040;dbname=test','xxxx','xxxx');
$db->exec('SET CHARACTER SET utf8');
$rs = $db->prepare("INSERT INTO `spw_license` (`serial_num`, `user`, `ba_struct`) 
                  VALUES(:serial_num, :username, :ba_struct)");

$rs->bindValue(':serial_num', $_SNB, PDO::PARAM_INT);
$rs->bindValue(':username', $_USR);
$rs->bindValue(':ba_struct', $_BIN, PDO::PARAM_LOB);

try {
    if(!$rs->execute()) {
        var_dump($db->errorInfo());
    }
} catch(Exception $e) {
    echo 'got Exception: ' . $e->getMessage() . PHP_EOL;
}

Here is how the table looks like after this is launched:

以下是启动此表后表格的外观:

mysql> select * from `spw_license`;
+------------+------+----------------------------------+
| serial_num | user | ba_struct                        |
+------------+------+----------------------------------+
|         23 | toto | ♣_  (☻' ∙ вO
 ^ _жE Є ⌂↕   |
+------------+------+----------------------------------+
1 row in set (0.00 sec)

#1